[SQL] DDL : 제약조건- PRIMARY KEY, FOREIGN KEY, 제약조건 명 부여

PRIMARY KEY

o  테이블에서 한 행의 정보를 구분하기 위한 고유 식별자 역할

o  NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 한 테이블 당 하나만 설정 가능

o  컬럼 레벨과 테이블 레벨 둘 다 지정 가능

CREATE TABLE TBL_USER_PK(     USER_NO NUMBER PRIMARY KEY, -- 컬럼 레벨 PRIMARY KEY 설정     USER_ID VARCHAR2(20) UNIQUE,     USER_PWD VARCHAR2(20) NOT NULL -- PRIMARY KEY(USER_NO) -- 테이블 레벨 PRIMARY KEY 설정 );   INSERT INTO TBL_USER_PK VALUES(1,'USER11','USER11'); INSERT INTO TBL_USER_PK VALUES(NULL,'USER11','USER11'); -- NULL로 인해 불가 INSERT INTO TBL_USER_PK VALUES(1,'USER22','USER11'); -- 중복 값(1)으로 인해 불가 SELECT * FROM TBL_USER_PK;

 

복합키

o  PRIMARY KEY는 두개 이상의 컬럼을 묶어서 하나의 PRIMARY KEY로 사용이 가능한데 이를 복합키라고 한다.

o  복합키 설정은 테이블 레벨에서 가능하다.

CREATE TABLE TBL_ORDER(     PRODUCT_NO NUMBER,     USER_ID VARCHAR2(20),     ORDER_DATE DATE,     COUNT NUMBER,     ORDER_NO NUMBER,     PRIMARY KEY(PRODUCT_NO,USER_ID,ORDER_DATE) );   INSERT INTO TBL_ORDER VALUES(1,'ADMIN','21/03/10',10,1); INSERT INTO TBL_ORDER VALUES(2,NULL,'21/03/11',10,1); --NULL입력으로 인한 에러 발생 INSERT INTO TBL_ORDER VALUES(2,'ADMIN','21/03/11',10,1); INSERT INTO TBL_ORDER VALUES(2,'USER1','21/03/11',10,1); SELECT * FROM TBL_ORDER;

-  복합키에 사용되는 컬럼 또한 마찬가지로 AND조건으로 연결되기에 데이터 입력 시 지정된 컬럼 모두가 중복이 없어야 제약 조건이 성립한다.

-  그러나 NOT NULL은 테이블 단위 적용 불가하다. 따라서 NULL에 대해서는 컬럼별 적용이 되어 복합키 조건에 사용되는 컬럼 중 NULL이 하나라도 있으면 제약 조건이 성립되어 데이터 입력이 불가능하다.

  

FOREIGN KEY

o  참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 것

o  참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성되는데 참조되는 값은 제공되는 값 외에 NULL을 사용 가능하며 참조할 테이블의 참조할 컬럼 명을 생략할 경우 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 됨

o  카테고리, 부서명, 학과명 등 테이블 간 연간관계 시 사용하며 부모(참조되는 테이블), 자식(참조하는 테이블)의 관계가 성립한다.

o  부모테이블의 참조되는 컬럼에는 UNIQUE제약조건이 설정이 되어있어야 한다.

CREATE TABLE MEMBER(     USER_ID VARCHAR2(20) NOT NULL UNIQUE,     USER_PWD VARCHAR2(20)    );   CREATE TABLE BOARD(   BOARD_NO NUMBER,   BOARD_TITLE VARCHAR2(20) NOT NULL,   BOARD_CONTENT VARCHAR2(20), BOARD_WRITER VARCHAR2(20) REFERENCES MEMBER(USER_ID), --BOARD_WRITER컬럼은 MEMBER의 UESR_ID 컬럼을 참조한다. BOARD_DATE DATE -- FOREIGN KEY(USER_ID) REFERENCES MEMBER(USER_ID) -- FOREIGN KEY 테이블레벨 생성 );   INSERT INTO MEMBER VALUES('USER01','USER01');   INSERT INTO BOARD VALUES(1,'내용',NULL,'USER01',SYSDATE); INSERT INTO BOARD VALUES(1,'내용',NULL,'USER02',SYSDATE); -- 에러, 참조하고 있는 컬럼에 있는 값만 입력할 수 있다.   SELECT * FROM MEMBER; SELECT * FROM BOARD;

o  만약 부모테이블의 참조되고 있는 컬럼을 지울 경우 자식 테이블에서 참조하고 있는 값을 먼저 지워야 지울 수 있다. o  또한 FOREIGN KEY제약 조건으로 연결되어 있으면 참조하는 컬럼 값이 없어도 부모 테이블을 지울 수 없다.

 

o  이는 FOREIGN KEY 설정 시 옵션 설정을 통해 바로 지워지게 할 수도 있다.

1.  ON DELETE SET NULL : 참조하는 값을 NULL로 변경 후 참조 테이블을 제거한다. NOT NULL 제약 조건이 걸려있으면 적용할 수 없다.

2.  ON DELETE CASCADE : 부모 테이블 제거 시 자식 테이블에서 연결된 데이터도 같이 제거된다.

DELETE FROM MEMBER WHERE USER_ID='USER01';  -- USER01로 참조되고 있는 값이 있기에 지울 수 없다.   DELETE FROM BOARD WHERE BOARD_WRITER='USER01'; -- 자식테이블에서 참조하고 있는 값을 지우면 DELETE FROM MEMBER WHERE USER_ID='USER01';  -- 참조 값을 지울 수 있다.   DROP TABLE MEMBER; -- 참조하는 값이 없어도 부모 테이블은 지울 수 없다.     CREATE TABLE MEMBER2(     USER_ID VARCHAR2(20) UNIQUE,     USER_PWD VARCHAR(20) );   CREATE TABLE SHOP_BUY(     BUY_NO NUMBER PRIMARY KEY,     USER_ID VARCHAR2(20),     PRODUCT_NAME VARCHAR2(50),     REG_DATE DATE,     FOREIGN KEY(USER_ID) REFERENCES MEMBER2(USER_ID) ON DELETE SET NULL -- FOREIGN KEY(USER_ID) REFERENCES MEMBER2(USER_ID) ON DELETE CASCADE ); INSERT INTO MEMBER2 VALUES('USER01','USER01');   INSERT INTO SHOP_BUY VALUES(1,'USER01','바지',SYSDATE); DELETE FROM MEMBER2 WHERE USER_ID='USER01'; -- ON DELETE SET NULL -> DELETE명령어 사용 시 USER01이 NULL로 변경된다. -- ON DELETE CASCADE -> DELETE 명령어 사용 시 연관된 데이터(ROW)가 같이 제거된다. SELECT * FROM SHOP_BUY; SELECT * FROM MEMBER2;   - 옵션에 따른 DELETE 후 변화 - TABLE SHOP_BUY  ON DELETE SET NULL  ON DELETE CASCADE

 

제약조건 명 부여하기

o  제약 조건 이름을 보다 쉽게 알아보기 위해 설정할 때 원하는 이름으로 부여할 수 있다.

o  (CONSTRAINT 제약조건명 제약조건) 형식으로 사용한다.

CREATE TABLE CONS_NAME(     USER_ID VARCHAR2(20) CONSTRAINT CONS_NAME_PK PRIMARY KEY,  -- CONSTRAINT 제약 조건명     USER_NAME VARCHAR2(20),     CONSTRAINT USER_NAME_UQ UNIQUE(USER_NAME) );   SELECT* FROM USER_CONS_COLUMNS WHERE TABLE_NAME='CONS_NAME';