데이터베이스
데이터베이스 - 제약 조건 (UK, NOT NULL, CHECK)
slcry
2022. 11. 8. 14:26
UK, NOT NULL, CHECK
제약 조건 설정
UK(Unique Key) 설정 - 중복된 값이 들어가지 않는다.
SQL> CREATE TABLE 테이블 (
2 .....
3 CONSTRAINT 제약_조건 UNIQUE (컬럼));
SQL> CREATE TABLE 테이블 (
2 컬럼 데이터_타입 CONSTRAINT 제약_조건 UNIQUE,
3 ......
NOT NULL 설정 -> 모든 숫자컬럼에는 NOT NULL을 쓰는게 일반적이다.
SQL> CREATE TABLE 테이블 (
2 컬럼 데이터_타입 CONSTRAINT 제약_조건 NOT NULL,
3 ......
CHECK 설정 -> WHERE절 대신으로 사용하는 느낌이다.
SQL> CREATE TABLE 테이블 (
2 .....
3 CONSTRAINT 제약_조건 CHECK (조건));
SQL> CREATE TABLE 테이블 (
2 컬럼 데이터_타입 CONSTRAINT 제약_조건 CHECK (조건),
3 ......
CHECK와 NOT NULL 제약 조건 검색
SQL> SELECT constraint_name, search_condition FROM user_constraints
2 WHERE table_name = '테이블';
- NOT NULL과 CHECK 제약 조건의 내용은 검색한다.
- 이외 제약 조건은 널 값이다.
예제 1. 다음 표를 확인하고 테이블을 생성한다.
SQL> CREATE TABLE emp4 (
2 eno VARCHAR2(4),
3 ename VARCHAR2(50) CONSTRAINT emp4_nu_ename NOT NULL,
4 gno VARCHAR2(13),
5 sex VARCHAR2(3),
6 CONSTRAINT emp4_eno_pk PRIMARY KEY (eno),
7 CONSTRAINT emp4_gno_uk UNIQUE (gno),
8 CONSTRAINT emp4_gno_ch CHECK (LENGTH(gno)=13),
9 CONSTRAINT emp4_sex_ch CHECK (sex IN ('여','남')));
테이블이 생성되었습니다.
SQL> SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
2 FROM user_constraints c, user_cons_columns s
3 WHERE c.constraint_name = s.constraint_name
4 AND c.table_name = 'EMP4';
TABLE_NAME CONSTRAINT_NAME C COLUMN_NAME
--------------- --------------------------- -- ----------------------
EMP4 EMP4_ENO_PK P ENO
EMP4 EMP4_GNO_UK U GNO
EMP4 EMP4_NU_ENAME C ENAME
EMP4 EMP4_GNO_CH C GNO
EMP4 EMP4_SEX_CH C SEX
SQL> SELECT constraint_name, search_condition FROM user_constraints
2 WHERE table_name = 'EMP4';
CONSTRAINT_NAME SEARCH_CONDITION
-------------------- -------------------
EMP4_ENO_PK
EMP4_GNO_UK
EMP_NU_ENAME "ENAME" IS NOT NULL
EMP4_GNO_CH LENGTH(gno)=13
EMP4_SEX_CH sex IN ('여','남')
예제 2. 제약 조건에 어긋나는 데이터를 입력하고 결과를 확인한다.
SQL> INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1001', '문시현', '0602183123456','남');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1002', NULL, '0605123123456','남');
VALUES ('1002',NULL,'0605123123456','남')
*
2행에 오류: ← ename에 NULL을 허용하지 않습니다.
ORA-01400: NULL을 ("ST"."EMP4"."ENAME") 안에 삽입할 수 없습니다
SQL> INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1003', '김민채', '0602183123456', '여');
INSERT INTO emp4 (eno,ename,gno,sex) ← gno 컬럼에 중복을 허용하지 않는다. 문시현의 gno와 동일합니다.
*
1행에 오류:
ORA-00001: 무결성 제약 조건(ST.EMP4_GNO_UK)에 위배됩니다
SQL> INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1004', '권아현', '060121312345', '여');
INSERT INTO emp4 (eno,ename,gno,sex) ← gno 컬럼은 반드시 13자리다.
*
1행에 오류:
ORA-02290: 체크 제약조건(ST.EMP4_GNO_CH)이 위배되었습니다
SQL> INSERT INTO emp4 (eno, ename, gno, sex)
2 VALUES ('1005', '권석현', '0601213123456', 'M');
INSERT INTO emp4 (eno,ename,gno,sex) ← sex 컬럼은 '여'와 '남'만을 허용한다.
*
1행에 오류:
ORA-02290: 체크 제약조건(ST.EMP4_SEX_CH)이 위배되었습니다
제약 조건 관리
제약 조건 추가/삭제
SQL> ALTER TABLE 테이블
2 ADD CONSTRAINT 제약_조건 제약_조건_타입;
SQL> ALTER TABLE 테이블
2 MODIFY 컬럼 CONSTRAINT 제약_조건 NOT NULL;
SQL> ALTER TABLE 테이블
2 DROP PRIMARY KEY | UNIQUE(컬럼) | CONSTRAINT 제약_조건 [CASCADE];
예제 1. 예제용 테이블에 입력된 데이터를 확인하고 제약 조건을 추가, 삭제한다.
SQL> CREATE TABLE class (
2 cno VARCHAR2(2),
3 cname VARCHAR2(50));
테이블이 생성되었습니다.
SQL> CREATE TABLE st (
2 sno VARCHAR2(2),
3 sname VARCHAR2(50),
4 cno VARCHAR2(2));
테이블이 생성되었습니다.
SQL> INSERT INTO class VALUES ('01','노랑새싹반');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO class VALUES ('02','연두잎새반');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO st VALUES ('01','문시현','01');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO st VALUES ('02','오시원','02');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO st VALUES ('03','양선호','03');
1 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> ALTER TABLE class
2 ADD CONSTRAINT class_cno_pk PRIMARY KEY (cno);
테이블이 변경되었습니다.
SQL> ALTER TABLE class
2 ADD CONSTRAINT class_cname_uk UNIQUE (cname);
테이블이 변경되었습니다.
SQL> ALTER TABLE st
2 ADD CONSTRAINT st_sno_pk PRIMARY KEY (sno);
테이블이 변경되었습니다.
SQL> ALTER TABLE st
2 ADD CONSTRAINT st_cno_fk FOREIGN KEY (cno) REFERENCES class (cno);
ADD CONSTRAINT st_cno_fk FOREIGN KEY (cno)
*
2행에 오류:
ORA-02298: 제약 (ST.ST_CNO_FK)을 사용 가능하게 할 수 없음 - 부모 키가 없습니다
SQL> SELECT sno, st.cno, class.cno
2 FROM class, st
3 WHERE class.cno(+) = st.cno;
SNO CNO CNO
-------- --- ---
01 01 01
02 02 02
03 03
SQL> UPDATE st SET cno = '01' WHERE sno = '03';
1 개의 행이 갱신되었습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> ALTER TABLE st 2 ADD CONSTRAINT st_cno_fk FOREIGN KEY (cno) REFERENCES class (cno);
테이블이 변경되었습니다.
SQL> ALTER TABLE class
2 MODIFY cname CONSTRAINT class_cname_nu NOT NULL;
테이블이 변경되었습니다.
SQL> SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
2 FROM user_constraints c, user_cons_columns s
3 WHERE c.constraint_name = s.constraint_name
4 AND c.table_name IN ('CLASS','ST');
TABLE_NAME CONSTRAINT_NAME C COLUMN_NAME
---------- ---------------------------- - --------------
CLASS CLASS_CNO_PK P CNO
CLASS CLASS_CNAME_UK U CNAME
CLASS CLASS_CNAME_NU C CNAME
ST ST_SNO_PK P SNO
ST ST_CNO_FK R CNO
SQL> ALTER TABLE class
2 DROP CONSTRAINT CLASS_CNO_PK; DROP CONSTRAINT CLASS_CNO_PK
*
2행에 오류:
ORA-02273: 고유/기본 키가 외부 키에 의해 참조되었습니다
SQL> ALTER TABLE class
2 DROP CONSTRAINT CLASS_CNO_PK CASCADE;
테이블이 변경되었습니다.
SQL> ALTER TABLE class
2 DROP CONSTRAINT CLASS_CNAME_NU;
테이블이 변경되었습니다.
SQL> ALTER TABLE class
2 DROP CONSTRAINT CLASS_CNAME_UK;
테이블이 변경되었습니다.
SQL> ALTER TABLE st
2 DROP CONSTRAINT ST_SNO_PK;
테이블이 변경되었습니다.