SQL-제약 조건 이해와 설정-UK, NOT NULL, CHECK

* UK(Unique Key) 설정

SQL> CREATE TABLE 테이블 (
2 .....
3 CONSTRAINT 제약_조건 UNIQUE (컬럼)

);

 

* NOT NULL 설정
SQL> CREATE TABLE 테이블 (
2 컬럼 데이터_타입 CONSTRAINT 제약_조건 NOT NULL,
3 ......

 

NOT NULL은 항상 컬럼 뒷쪽에 써줘야 한다.

 

* CHECK 설정

SQL> CREATE TABLE 테이블 (
2 .....
3 CONSTRAINT 제약_조건 CHECK (조건)

);

 

* CHECK와 NOT NULL 제약 조건 검색
SQL> SELECT constraint_name, search_condition FROM user_constraints
2 WHERE table_name = '테이블';

 

[예제 1] 다음 표를 확인하고 테이블을 생성.

CREATE TABLE emp4 (
eno VARCHAR2(4),
ename VARCHAR2(50) CONSTRAINT emp4_nu_ename NOT NULL, // not null은 따로 써줄수 없기때문에 여기에 써줌.
gno VARCHAR2(13),
sex VARCHAR2(3),
CONSTRAINT emp4_eno_pk PRIMARY KEY (eno),
CONSTRAINT emp4_gno_uk UNIQUE (gno),
CONSTRAINT emp4_gno_ch CHECK (LENGTH(gno)=13), // LENGTH는 반드시 13이어야 함.
CONSTRAINT emp4_sex_ch CHECK (sex IN ('여','남'))
);

 

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
FROM user_constraints c, user_cons_columns s
WHERE c.constraint_name = s.constraint_name
AND c.table_name = 'EMP4';

 

SELECT constraint_name, search_condition

FROM user_constraints
WHERE table_name = 'EMP4';

 

예제 2. 제약 조건에 어긋나는 데이터를 입력하고 결과를 확인.

INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1001', '문시현', '0602183123456','남');

 

INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1002', NULL, '0605123123456','남');

 

INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1003', '김민채', '0602183123456', '여'); // gno가 문시현과 똑같아서 오류남

 

INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1004', '권아현', '060121312345', '여');

 

INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1005', '권석현', '0601213123456', 'M');

 

[실습]

1. 다음 표를 보고 테이블을 생성하는 스크립트를 작성하세요
- 테이블명과 컬럼명은 한글을 허용합니다.
- 스크립트 생성 시에 테이블 생성 순서에 주의하세요.

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name 
FROM user_constraints c, user_cons_columns s 
WHERE c.constraint_name = s.constraint_name 
AND c.table_name IN ('PRODUCT', 'SLIP', 'DETAILSLIP')

ORDER BY c.table_name;

 

SELECT constraint_name, search_condition

FROM user_constraints 
WHERE table_name IN ('PRODUCT', 'SLIP', 'DETAILSLIP');

 

2. 다음 테이블에 대한 표를 보고 테이블 생성을 위한 스크립트를 생성한다.

 

 

* 제약 조건 관리

 

ALTER TABLE 테이블
ADD CONSTRAINT 제약_조건 제약_조건_타입

 

ALTER TABLE 테이블
MODIFY 컬럼 CONSTRAINT 제약_조건 NOT NULL;

 

ALTER TABLE 테이블
DROP PRIMARY KEY | UNIQUE(컬럼) | CONSTRAINT 제약_조건 [CASCADE]

-> cascade : 자신을 참조하는 제약조건까지 삭제. 버전에 따라 되는경우도 있고 안되는 경우도 있음. 

 

[예제 1] 예제용 테이블에 입력된 데이터를 확인하고 제약 조건을 추가, 삭제한다.

CREATE TABLE class (
cno VARCHAR2(2),
cname VARCHAR2(50)

);

 

CREATE TABLE st (
sno VARCHAR2(2),
sname VARCHAR2(50),
cno VARCHAR2(2)

);

 

INSERT INTO class VALUES ('01','노랑새싹반');

INSERT INTO class VALUES ('02','연두잎새반');
INSERT INTO st VALUES ('01','문시현','01');

INSERT INTO st VALUES ('02','오시원','02');

INSERT INTO st VALUES ('03','양선호','03'); // 없는 class지만 제약조건이 없어서 실행 됨.
COMMIT;

ALTER TABLE class
ADD CONSTRAINT class_cno_pk PRIMARY KEY (cno);

ALTER TABLE class
ADD CONSTRAINT class_cname_uk UNIQUE (cname);

 

ALTER TABLE st
ADD CONSTRAINT st_sno_pk PRIMARY KEY (sno);

ALTER TABLE st
ADD CONSTRAINT st_cno_fk FOREIGN KEY (cno) REFERENCES class (cno); // 양선호때문에 안 됨.

 

SELECT sno, st.cno, class.cno
FROM class, st

WHERE class.cno(+) = st.cno; // 어떤 데이터 때문에 제약조건이 수정이 안되는지 알 수 있음.

 

UPDATE st SET cno = '01' WHERE sno = '03'; // sno가 03인 컬럼의 cno를 01로 수정

COMMIT;

 

ALTER TABLE st
ADD CONSTRAINT st_cno_fk FOREIGN KEY (cno) REFERENCES class (cno); // 다시 문제없이 제약조건이 추가됨을 확인

 

ALTER TABLE class
MODIFY cname CONSTRAINT class_cname_nu NOT NULL;

 

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
FROM user_constraints c, user_cons_columns s
WHERE c.constraint_name = s.constraint_name
AND c.table_name IN ('CLASS','ST');

 

ALTER TABLE class
DROP CONSTRAINT CLASS_CNO_PK; // 참조 되고 있어서 삭제 안 됨.

 

ALTER TABLE class
DROP CONSTRAINT CLASS_CNO_PK CASCADE;

 

ALTER TABLE class
DROP CONSTRAINT CLASS_CNAME_NU;

 

ALTER TABLE class
DROP CONSTRAINT CLASS_CNAME_UK;

 

ALTER TABLE st
DROP CONSTRAINT ST_SNO_PK;

 

[실습]

다음 표를 보고 테이블을 생성하는 스크립트를 작성한다.(제약조건은 반드시 ALTER문으로)

select c.table_name, c.constraint_name, c.constraint_type, s.column_name 
from user_constraints c, user_cons_columns s 
where c.constraint_name = s.constraint_name 
and c.table_name in ('PRODUCT', 'SLIP', 'DETAILSLIP') 
order by c.table_name;

 

select p.table_name 상위테이블, p.constraint_name 상위제약조건,
c.table_name 하위테이블, c.constraint_name 참조제약조건, s.column_name 컬럼이름
from user_constraints p, user_constraints c, user_cons_columns s
where c.r_constraint_name = p.constraint_name and s.constraint_name = p.constraint_name
and p.table_name in ('PRODUCT', 'SLIP', 'DETAILSLIP')  
order by p.table_name;

 

2. 다음 테이블에 대한 표를 보고 테이블을 생성을 위한 스크립트를 생성한다.(제약 조건은 반드시 ALTER 문으로 설정)

select c.table_name, c.constraint_name, c.constraint_type, s.column_name 
from user_constraints c, user_cons_columns s 
where c.constraint_name = s.constraint_name 
and c.table_name in ('DEPT', 'EMP') 
order by c.table_name;

select p.table_name 상위테이블, p.constraint_name 상위제약조건, 
c.table_name 하위테이블, c.constraint_name 참조제약조건, s.column_name 컬럼이름 
from user_constraints p, user_constraints c, user_cons_columns s 
where c.r_constraint_name = p.constraint_name and s.constraint_name = p.constraint_name 
and p.table_name in ('DEPT', 'EMP')   
order by p.table_name;