Oracle SQL DDL 02 (Data Definition Language 02)

DDL에서 무결성 이란?

간단하게 COLUMN에 지정하는 성질이다. 라고 볼 수 있다. 종류는 다음과 같다.

P	Primary Key	PK	기본키
U	Unique Key	UK	고유키
F	Foreign Key	FK	외래키
	CHECK
	NOT NULL

NOT NULL

NULL을 허용하지 않는 성질.

CREATE TABLE test_tb(
COL_01 VARCHAR2(10) NOT NULL, -- NOT NULL 성질 설정.
COL_02 VARCHAR2(10)
); -- COL_01 COLUMN은 null로 지정이 불가능 하게되며, 값이 필요하게된다.

INSERT INTO test_tb(COL_01, COL_02)
VALUES('AAA', 'aaa');
SELECT * FROM tb_test;

INSERT INTO test_tb(COL_01, COL_02)
VALUES('AAA', '');
SELECT * FROM tb_test;

INSERT INTO test_tb(COL_01, COL_02)
VALUES('', ''); --cannot insert NULL into ("HR"."TB_TEST"."COL_01") 오류 발생

CHECK

데이터의 값을 허용하는 범위지정 또는 특정 값 지정 하는 성질.

NULL 을 허용한다.

DROP TABLE test_tb
CASCADE CONSTRAINTS; -- 실행후 진행

CREATE TABLE test_tb (
COL_01 VARCHAR2(10), 
COL_01 VARCHAR2(10),
CONSTRAINT chck_test_01 CHECK (COL_01 IN('사과', '배', '바나나')), -- 특정 값 지정
CONSTRAINT chck_test_02 CHECK (COL_02 > 0 AND COL_01 <= 100) -- 범위 지정
);

INSERT INTO test_tb (COL_01, COL_02)
VALUES ('사과', 0 ); -- check constraint (HR.CHK_TEST_02) violated 오류 발생

INSERT INTO test_tb (COL_01, COL_02)
VALUES ('귤', 10 ); -- check constraint (HR.CHK_TEST_01) violated 오류 발생

INSERT INTO test_tb (COL_01, COL_02)
VALUES ('', '' );
SELECT * FROM test_01; -- 특정값, 범위값 이외에는 null만을 허용함.

Unique key (UK) : 고유키

유일한 값을 갖는 성질.

NULL을 허용한다.

DROP TABLE test_tb
CASCADE CONSTRAINTS; -- 실행후 진행

CREATE TABLE test_tb(
UKEY_1 VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE, -- UK 성질 설정.
COL_1 VARCHAR2(10),
COL_2 VARCHAR2(10)
);

INSERT INTO test_tb (UKEY_1,COL_1,COL_2)
VALUES('AAA', 'aaa', '111'); -- 2번 실행시. unique constraint (HR.UK_TEST_01) violated 오류발생.

INSERT INTO test_tb (UKEY_1,COL_1,COL_2)
VALUES('', 'aaa', '111'); -- NULL값 허용.

SELECT * FROM test_tb; -- 확인

Primary Key (PK) : 기본키

유일하게 식별 할 수 있는 정의된 키

한테이블에서 최대 32개 COLUMN에 지정 할 수 있다.

PK = NOT NULL + UK

DROP TABLE test_tb
CASCADE CONSTRAINTS; -- 실행후 진행

CREATE TABLE test_tb(
KEY_01 VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, -- PK 성질 설정.
KEY_02 VARCHAR2(10), 
KEY_03 VARCHAR2(10)
);

INSERT INTO test_tb(KEY_01, KEY_02, KEY_3)
VALUES ('AAA', 'aaa', '111');
-- 두번실행시 ORA-00001: unique constraint (HR.PK_TEST_01) violated 오류 발생

두개의 PK 사용 예

DROP TABLE test_tb
CASCADE CONSTRAINTS; -- 실행후 진행

CREATE TABLE test_tb(
KEY_01 VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, 
KEY_02 VARCHAR2(10) CONSTRAINT PK_TEST_02 PRIMARY KEY, 
KEY_03 VARCHAR2(10)
); -- 오류 실행불가.

CREATE TABLE test_tb(
KEY_01 VARCHAR2(10) ,
KEY_02 VARCHAR2(20),
KEY_03 VARCHAR2(20),
CONSTRAINT PK_TEST_01 PRIMARY KEY(KEY_01, KEY_02)
); -- 실행가능.

PK가 두가지 이상일 경우 쌍으로 취급하기 때문에 각각의 PK 가 되지않고 한번에 성질을 설정해야한다.

Foreign Key (FK) : 외래키

테이블간 연결하기 위한 무결성 성질이다. 즉, 제약조건이다.

다른테이블에 값이 PK 이거나 UK 이면 사용이 가능하다.

NULL을 허용한다.

CREATE TABLE test_par(
pkey_01 VARCHAR2(10),
pkey_02 VARCHAR2(10),
col_01 VARCHAR2(10),
col_02 VARCHAR2(10),
CONSTRAINT pk_test_01 PRIMARY KEY (pkey_01, pkey_02)
);

CREATE TABLE test_chd(
col_01 VARCHAR2(10),
fkey_01 VARCHAR2(10),
fkey_02 VARCHAR2(10),
CONSTRAINT fk_test_01 FOREIGN KEY ( fkey_01,fkey_02) 
REFERENCES test_par(pkey_01, pkey_02) -- Reffrences 로 키가 연결되므로 꼭 작성하여야한다.
);

INSERT INTO test_par (pkey_01, pkey_02, col_01, col_02)
VALUES ('AAA', 'aaa', '111', '222');

INSERT INTO test_par (pkey_01, pkey_02, col_01, col_02)
VALUES ('BBB', 'bbb', '111', '222');

INSERT INTO test_chd (col_01, fkey_01, fkey_02)
VALUES ('가나다', 'AAA', 'aaa');

INSERT INTO test_chd (col_01, fkey_01, fkey_02)
VALUES ('가나다', 'BBB', 'bbb');

INSERT INTO test_chd (col_01, fkey_01, fkey_02)
VALUES ('가나다', 'BBB', 'aaa');
-- integrity constraint (HR.FK_TEST_01) violated - parent key not found 오류 발생.

INSERT INTO test_chd (col_01, fkey_01, fkey_02)
VALUES ('가나다', '', ''); -- NULL 값을 허용하므로 실행가능..

SELECT *
FROM test_chd; -- null 값이 허용됨을 확인 할 수 있다.

SELECT *
FROM test_par; -- null 값이 허용되지 않을 확인 할 수 있다.

CREATE TABLE emp
AS SELECT * FROM employees;

TABLE COPY 실행시 복사된 emp TABLE 제약조건이 없다는 것을 확인 할 수 있다.

즉, 무결성은 복사되지않는다.

TABLE 수정 : 제약조건 추가

ALTER TABLE test_chd
ADD CONSTRAINT pk_test_01
PRIMARY KEY (col_01); -- col_01에 pk_test_01 성질 설정 추가.

TABLE 수정 : 제약조건 삭제

ALTER TABLE test_chd
DROP CONSTRAINT pk_test_01; -- pk_test_01 성질 삭제