[4일차] 오라클 데이터베이스/p360 ~ 416 / 제약조건 ,사용자, 롤, 권한관리

 

 

 

--제약조건 

create table table_notnull(
    login_id varchar2(20) not null,
    login_pwd varchar2(20) not null,
    tel  varchar2(20)
);

desc table_notnull;

-- ORA-01400: NULL을 ("SCOTT"."TABLE_NOTNULL"."LOGIN_PWD") 안에 삽입할 수 없습니다
insert into table_notnull(login_id, login_pwd, tel) values ('TEST_ID_01', null, '010-1234-5678');

insert into table_notnull(login_id, login_pwd) values ('TEST_ID_01','1234');

select * from table_notnull;



--열의 제약 조건을 not null 로 지정했기 때문에 null 값으로 수정하는것도 불가능 안된다.
--ORA-01407: NULL로 ("SCOTT"."TABLE_NOTNULL"."LOGIN_PWD")을 업데이트할 수 없습니다
update table_notnull set login_pwd = null where login_id = 'TEST_ID_01';


select owner, constraint_name, constraint_type, table_name from user_constraints;


create table table_notnull2(
    login_id varchar2(20) constraint tblnn2_lgnid_nn not null,
    login_pwd varchar2(20) constraint tblnn2_lgnpw_nn not null,
    tel varchar2(20)
);


select owner, constraint_name, constraint_type, table_name from user_constraints;


--ORA-02296: (SCOTT.) 사용으로 설정 불가 - 널 값이 발견되었습니다.
alter table table_notnull modify(tel not null);


update table_notnull set tel = '010-1234-5678' where login_id = 'TEST_ID_01';

select * from table_notnull;

 

--열의 제약 조건을 not null 로 지정했기 때문에 null 값으로 수정하는것도 불가능 안된다.
--ORA-01407: NULL로 ("SCOTT"."TABLE_NOTNULL"."LOGIN_PWD")을 업데이트할 수 없습니다
update table_notnull set login_pwd = null where login_id = 'TEST_ID_01';


select owner, constraint_name, constraint_type, table_name from user_constraints;


create table table_notnull2(
    login_id varchar2(20) constraint tblnn2_lgnid_nn not null,
    login_pwd varchar2(20) constraint tblnn2_lgnpw_nn not null,
    tel varchar2(20)
);


select owner, constraint_name, constraint_type, table_name from user_constraints;


--ORA-02296: (SCOTT.) 사용으로 설정 불가 - 널 값이 발견되었습니다.
alter table table_notnull modify(tel not null);


update table_notnull set tel = '010-1234-5678' where login_id = 'TEST_ID_01';

select * from table_notnull;

alter table table_notnull modify(tel not null);

select owner, constraint_name, constraint_type, table_name from user_constraints;

alter table table_notnull2 modify (tel constraint tblnn_tel_nn not null);

desc table_notnull2;

 

 

alter table table_notnull2 
rename constraint tblnn_tel_nn to tblnn2_tel_nn;

select owner, constraint_name, constraint_type, table_name from user_constraints;

alter table table_notnull2 drop constraint tblnn2_tel_nn;

desc table_notnull2;


--unique 중복되지 않은 값 지정
create table table_unique(
    login_id      varchar2(20) unique,
    login_pwd  varchar2(20) not null,
    tel             varchar2(20)
);

desc table_unique;

select owner, constraint_name, constraint_type, table_name 
from user_constraints
where table_name = 'TABLE_UNIQUE';

insert into table_unique(login_id, login_pwd, tel) values ('TEST_ID_01','PWD01','010-1234-5678');

select * from table_unique;


--unique 제약 조건은 열 값의 중복은 허용하지 않지만 null 값은 저장 가능 
--ORA-00001: 무결성 제약 조건(SCOTT.SYS_C0010115)에 위배됩니다
insert into table_unique(login_id, login_pwd, tel) values ('TEST_ID_01','PWD01','010-1234-5678');


insert into table_unique(login_id, login_pwd, tel) values ('TEST_ID_02','PWD01','010-1234-5678');


insert into table_unique(login_id, login_pwd, tel) values ('NULL','PWD01','010-1234-5678');


--update table_unique set login_id = 'TEST_ID_01' where login_id is null;

create table table_unique2(
    login_id   varchar2(20) constraint tblunq2_lgnid_unq unique,
    login_pwd  varchar2(20) constraint tblunq2_lgnpw_nn not null,
    tel   varchar2(20)
);

select owner, constraint_name, constraint_type, table_name 
from user_constraints
where table_name like 'TABLE_UNIQUE%';



--ORA-02299: 제약 (SCOTT.SYS_C0010118)을 사용 가능하게 할 수 없음 - 중복 키가 있습니다  : tel 에 중복된 열이 있어서 그렇다.
--alter table table_unique modify (tel unique);


update table_unique set tel = null;

select * from table_unique;

alter table table_unique modify(tel unique);

alter table table_unique2 modify(tel constraint tblunq_tel_unq unique);


select owner, constraint_name, constraint_type, table_name 
from user_constraints
where table_name like 'TABLE_UNIQUE%';


alter table table_unique2 rename constraint tblunq_tel_unq to tblunq2_tel_unq;

alter table table_unique2 drop constraint tblunq2_tel_unq;

create table table_pk(
    login_id   varchar2(20) primary key,
    login_pwd  varchar2(20) not null,
    tel             varchar2(20)
);


desc table_pk;

select owner, constraint_name, constraint_type, table_name 
from user_constraints
where table_name like 'TABLE_PK%';

select index_name, table_owner, table_name 
from user_indexes
where table_name like 'TABLE_PK%';


create table table_pk2(
  login_id  varchar2(20) constraint tblpk2_lgnid_pk primary key,
  login_pwd  varchar2(20) constraint tblpk2_lgnpw_nn not null,
  tel    varchar2(20)
);

desc table_pk2;


-- primary key 제약조건을 지정한 열 확인 중복값을 입력했을 때
insert into table_pk(login_id, login_pwd, tel) values ('TEST_ID_01', 'PWD01', '010-1234-5678');

select * from table_pk;


--ORA-00001: 무결성 제약 조건(SCOTT.SYS_C0010126)에 위배됩니다  primary key 제약 조건 때문에 중복을 허용 안함 
insert into table_pk(login_id, login_pwd, tel) values ('TEST_ID_01', 'PWD02', '010-1234-5678');


insert into table_pk(login_id, login_pwd, tel) values ('NULL', 'PWD02', '010-1234-5678');

--ORA-01400: NULL을 ("SCOTT"."TABLE_PK"."LOGIN_ID") 안에 삽입할 수 없습니다
insert into table_pk(login_pwd, tel) values ('PWD02', '010-1234-5678');


--FOREIGN KEY

select owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name 
from user_constraints
where table_name in ('EMP','DEPT');

-- ORA-02291: 무결성 제약조건(SCOTT.FK_DEPTNO)이 위배되었습니다- 부모 키가 없습니다 
-- FOREIGN KEY 참조하는 열에 존재하지 않는 데이터 입력 dept 테이블의 deptno 열에 50이 존재하지 않기 때문에 오류남
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (9999,'이석재','CLERK','7788',to_date('2017/04/30','YYYY/MM/DD'), 1200, null, 50);


--CONSTRAINT [제약조건 명] FOREIGN KEY([컬럼명])
--REFERENCES [참조할 테이블 이름]([참조할 컬럼])
--[ON DELETE CASCADE | ON DELETE SET NULL]

create table dept_fk(
    deptno number(2) constraint deptfk_deptno_pk primary key,
    dname varchar2(14),
    loc   varchar2(13)
);

desc dept_fk;

create table emp_fk(
    empno   number(4) constraint empfk_empno_pk primary key,
    ename   varchar2(10),
    job        varchar2(9),
    mgr       number(4),
    hiredate date,
    sal        number(7,2),
    comm   number(7,2),
    deptno   number(2) constraint empfk_deptno_fk references dept_fk(deptno)
);

desc emp_fk;

--deptno 데이터가 아직 dept_fk 테이블에 없으므로 제약조건에 위배 
--ORA-02291: 무결성 제약조건(SCOTT.EMPFK_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다
insert into emp_fk values (9999, 'TEST_NAME', 'TEST_JOB', null, to_date('2001/01/01','YYYY/MM/DD'), 3000, NULL, 10);

insert into dept_fk values (10, 'TEST_DNAME', 'TEST_LOC');

select * from dept_fk;


insert into emp_fk values (9999, 'TEST_NAME', 'TEST_JOB', null, to_date('2001/01/01','YYYY/MM/DD'), 3000, NULL, 10);

select * from emp_fk;


--deptno 값을 참조하는 데이터가 있기 때문
--ORA-02292: 무결성 제약조건(SCOTT.EMPFK_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
delete from dept_fk where deptno = 10;

-- 열 데이터를 삭제할 때 데이터를 참조하고 있는 데이터도 함께 삭제
-- constraint [제약조건 이름] references 참조 테이블(참조할 열) on delete cascade
-- 열 데이터를 삭제할 때 데이터를 참조한느 데이터를 null 로 수정
-- constraint [제약조건 이름] references 참조 테이블(참조할 열) on delete set null

--check 제약
create table table_check(
    login_id varchar2(20) constraint tblck_loginid_pk primary key,
    login_pwd varchar2(20) constraint tblck_loginpw_ck check (length(login_pwd) > 3),
    tel varchar2(20)
);

desc table_check;

--ORA-02290: 체크 제약조건(SCOTT.TBLCK_LOGINPW_CK)이 위배되었습니다
--비밀번호 에 해당되는 컬럼 조건은 열 길이가 3글자 이상만 저장되도록 조건이 되어 있는데 
--테이블 입력한 조건은 비밀번호가 3글자로 지정했기 때문에 체크 제약조건에 위배된다 라는 메세지가 나옴
insert into table_check values ('TEST_ID','123','010-1234-5678');


insert into table_check values ('TEST_ID','1234','010-1234-5678');

select * from table_check;


select owner, constraint_name, constraint_type, table_name
from user_constraints
where table_name like 'TABLE_CHECK';

--default 기본값을 정함
create table table_default(
    login_id  varchar2(20) constraint tblck2_loginid_pk primary key,
    login_pwd varchar2(20) default '1234',
    tel  varchar2(20)
);

desc table_default;

insert into table_default values ('TEST_ID',NULL, '010-1234-5678');

insert into table_default (login_id, tel) values ('TEST_ID2','010-1234-5678');

select * from table_default;


--유저 생성 방법 : CREATE USER [유저 이름] IDENTIFIED BY [비밀번호];
--(생성된 유저 확인 방법 : SELECT USERNAME FROM DBA_USERS; ) 
-- 비밀번호 변경 : ALTER USER [유저이름] IDENTIFIED BY [변경할 비밀번호];
-- 유저 삭제 방법 : DROP USER [유저이름];
-- 권한 부여 방법 : GRANT [권한] TO [대상유저];
-- 권한 회수 방법 : REVOKE [권한] FROM [대상유저];



--1. 유저생성(비밀번호생성)
--CREATE USER[유저 이름]IDENRIFIED BY[비밀번호]
--CREATE USER dba_user IDENTIFIED BY pass;
--CREATE USER web_user IDENTIFIED BY pass;
--CREATE USER user002 IDENTIFIED BY pass3;
--생성 유저 확인
--유저가 바꿔줄수 있는거는 소문자로 그외에 원래 있는거나 명령어는 대문자
--SELECT USERNAME FROM DBA_USERS;
 
--2. 비밀번호 변경
--ALTER USER [유저이름] IDENTIFIED BY [변경할 비밀번호];
--ALTER USER web_user IDENTIFIED BY pass;
 
--3. 유저 삭제
--DROP USER[유저이름]
--DROP USER dab_user;
--DROP USER user002;
 
--4. 권한 부여
--GRANT[권한]TO[대상 유저]
--GRANT CONNECT,RESOURCE,DBA TO web_user;
--GRANT CREATE TABLE, CREATE VIEW TO web_user;
--GRANT CONNECT,DBA TO dba_user;
--GRANT [시스템 권한] to [사용자이름/롤/public] [with admin option]
--5. 권한 회수
--REVOKE[권한]FROM[대상유저]
--REVOKE DBA FROM web_user;