데이터베이스 - 제약 조건(PK, FK)

PK(Primary Key, 주키, 주식별자) 

  - PK는 테이블의 모든 데이터를 유일하게 식별해주는 컬럼이다.

  - EX) student테이블의 학생을 구별할때 SNO같은 나머지요소의 결정인자.

        student

         sno ---> (sname, sex, major, avr, syear) 

        emp

        eno ----> (ename, job, dno, ....)

        score

        sno + cno ----> (result)

- COURSE : CNO, DEPT : DNO, SCORE : SNO + CNO, 

 

FK(Foreign Key, 외부키, 외부식별자)

  - FK는 테이블 간 관계(Relationship)를 의미한다.

 

PK(Primary Key), FK(Foreign Key) 설정과 조회

 - Primary Key 설정

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

 

SQL> CREATE TABLE 테이블 (
2 컬럼 데이터_타입 CONSTRAINT 제약_조건 PRIMARY KEY,
3 ......

 

Foreign Key 설정

SQL> CREATE TABLE 테이블 (
 2 .....  
 3 CONSTRAINT 제약_조건 FOREIGN KEY (컬럼) 
 4 REFERENCES 참조할_테이블 (참조할_컬럼) [ON DELETE CASCADE]);

 

SQL> CREATE TABLE 테이블 (
 2 컬럼명 데이터_타입 CONSTRAINT 제약_조건 FOREIGN KEY 
 3 REFERENCES 참조할_테이블 (참조할_컬럼) 
 4 [ON DELETE CASCADE],
 5 .....

 

제약 조건 조회

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 (검색_대상_테이블_목록)
 5 ORDER BY c.table_name;

 

SQL> SELECT p.table_name 상위테이블, p.constraint_name 상위제약조건, 
 2 c.table_name 하위테이블, c.constraint_name 참조제약조건 
 3 FROM user_constraints p, user_constraints c
 4 WHERE c.r_constraint_name=p.constraint_name
 5 AND p.table_name in (검색_대상_테이블_목록)
 6 ORDER BY p.table_name;

 

예제 1. PK와 FK가 추가된 dept와 emp 테이블을 생성한다. 

SQL> DROP TABLE emp;
테이블이 삭제되었습니다. 

SQL> DROP TABLE dept;
테이블이 삭제되었습니다. 

SQL> PURGE RECYCLEBIN;
휴지통이 지워졌습니다. 

SQL> CREATE TABLE dept (
 2 dno VARCHAR2(2),
 3 dname VARCHAR2(10),
 4 loc VARCHAR2(6),
 5 CONSTRAINT dept_dno_pk PRIMARY KEY(dno)
 6 ); 
 
테이블이 생성되었습니다. 

SQL> CREATE TABLE emp (
 2 eno VARCHAR2(4), 
 3 ename VARCHAR2(10),
 4 sex VARCHAR2(2),
 5 job VARCHAR2(8),
 6 mgr VARCHAR2(4),
 7 hdate DATE,
 8 sal NUMBER,
 9 comm NUMBER, 
10 dno VARCHAR2(2),
11 CONSTRAINT emp_eno_pk PRIMARY KEY (eno),
12 CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr) REFERENCES emp (eno),
13 CONSTRAINT emp_dno_fk FOREIGN KEY (dno) REFERENCES dept (dno)
14 ); 

테이블이 생성되었습니다.

 

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 ('DEPT', 'EMP')
 5 ORDER BY c.table_name;

TABLE_NAME      CONSTRAINT_NAME      C COLUMN_NAME
--------------- -------------------- - -----------
DEPT            DEPT_DNO_PK          P DNO
EMP             EMP_ENO_PK           P ENO
EMP             EMP_DNO_FK           R DNO
EMP             EMP_MGR_FK           R MGR


SQL> SELECT p.table_name 상위테이블, p.constraint_name 상위제약조건,
 2 c.table_name 하위테이블, c.constraint_name 참조제약조건
 3 FROM user_constraints p, user_constraints c 
 4 WHERE c.r_constraint_name=p.constraint_name
 5 AND p.table_name in ('DEPT','EMP')
 6 ORDER BY p.table_name;

상위테이블 상위제약조건     하위테이블 참조제약조건
---------- ---------------- ---------- ------------
DEPT       DEPT_DNO_PK      EMP        EMP_DNO_FK
EMP        EMP_ENO_PK       EMP        EMP_MGR_FK

 

예제 2. Dept 테이블과 emp 테이블에 각각 데이터를 입력하고 제약조건이 입력 데이터를 통제하는지 확인한다. 

SQL> INSERT INTO dept (dno, dname, loc)
 2 VALUES ('10','개발','서울');
1 개의 행이 만들어졌습니다. 

SQL> INSERT INTO emp (eno, ename, dno)
 2 VALUES ('2000','문시현','10');
1 개의 행이 만들어졌습니다. 

SQL> COMMIT;
커밋이 완료되었습니다.

 

SQL> INSERT INTO dept (dno, dname, loc)
 2 VALUES ('10','총무','부산');  ← dno 컬럼이 중복된다.
INSERT INTO dept (dno, dname, loc) *
1행에 오류:
ORA-00001: 무결성 제약 조건(ST.DEPT_PK_DNO)에 위배됩니다

SQL> INSERT INTO emp (eno, ename, dno)
 2 VALUES ('2001','손하늘','20');  ← 20번 부서는 존재하지 않는다.
INSERT INTO emp (eno,ename, dno) *
1행에 오류:
ORA-02291: 무결성 제약조건(ST.EMP_FK_DNO)이 위배되었습니다- 부모 키가 없습니다

 

실습

C:\Users\st09\sql\board.sql

 

DROP TABLE BOARD;
PURGE RECYCLEBIN;

CREATE TABLE BOARD (
 NO NUMBER,
 NAME VARCHAR2(50),
 SUB VARCHAR2(100),
 CONTRAINT BOARD_NO_PK PRIMARY KEY(NO)
);

CREATE TABLE B_CONTENT (
 NO NUMBER,
 CONTENT VARCHAR2(40000),
 CONTRAINT B_CONTENT_NO_PK PRIMARY KEY(NO),
 CONTRAINT B_CONTENT_NO_FK FOREIGN KEY(NO) REFERENCES BOARD(NO)
);

 

SQL> DESC board; 
이름                  널?      유형
--------------------- -------- ---------------
NO                    NOT NULL NUMBER
NAME                           VARCHAR2(50)
SUB                            VARCHAR2(200)

SQL> DESC b_content;
이름                  널?      유형
--------------------- -------- ---------------
NO                    NOT NULL NUMBER
CONTENT                        VARCHAR2(4000)

 

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 ('BOARD','B_CONTENT')
 5 ORDER BY c.table_name;
 
TABLE_NAME CONSTRAINT_NAME      C COLUMN_NAME
---------- -------------------- - --------------
BOARD      BOARD_NO_PK          P NO
B_CONTENT  B_CONTENT_NO_PK      P NO
B_CONTENT  B_CONTENT_NO_FK      R NO


SQL> SELECT p.table_name 상위테이블, p.constraint_name 상위제약조건, 
 2 c.table_name 하위테이블, c.constraint_name 참조제약조건 
 3 FROM user_constraints p, user_constraints c 
 4 WHERE c.r_constraint_name=p.constraint_name
 5 AND p.table_name in ('BOARD','B_CONTENT'); 

상위테이블 상위제약조건 하위테이블 참조제약조건
---------- ------------ ---------- --------------
BOARD      BOARD_NO_PK  B_CONTENT  B_CONTENT_NO_FK

 

예제 4. 제약 조건에 이름을 지정하지 않고 테이블을 생성한다.

SQL> CREATE TABLE test (
 2 no NUMBER,
 3 PRIMARY KEY (no));
테이블이 생성되었습니다. 

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 = 'TEST';

TABLE_NAME CONSTRAINT_NAME              C COLUMN_NAME
---------- ---------------------------- - --------------
TEST       SYS_C007131                  P NO

 

실습

1. 다음 구조를 갖는 테이블을 생성한다. 각 테이블에는 필요한 PK와 FK를 지정한다.
 - 데이터 타입이나 길이는 임의로 각자 결정하고 테이블 생성 순서에 주의한다.
 factory 테이블(공장)
 : fno(공장번호), fname(공장이름), loc(지역)
 goods 테이블(제품)
 : gno(제품번호), gname(제품명), pri(표준단가), fac_no(생산공장)
 prod 테이블(출고 상품)
 : s_num(일련번호), gno(제품번호), pri (출고단가), pdate(생산일자)

DROP TABLE PROD;
DROP TABLE GOODS;
DROP TABLE FACTORY;

PURGE RECYCLEBIN;

CREATE TABLE FACTORY (
 FNO VARCHAR2(20),
 FNAME VARCHAR2(50),
 LOC VARCHAR2(100),
 CONSTRAINT FACTORY_NO_PK PRIMARY KEY(FNO)
);

CREATE TABLE GOODS (
 GNO VARCHAR2(20),
 GNAME VARCHAR2(20),
 PRI NUMBER,
 FAC_NO VARCHAR2(50),
 CONSTRAINT GOODS_GNO_PK PRIMARY KEY(GNO),
 CONSTRAINT GOODS_GNAME_FK FOREIGN KEY(GNAME) REFERENCES GOODS(GNO),
 CONSTRAINT GOODS_FAC_NO_FK FOREIGN KEY(FAC_NO) REFERENCES FACTORY(FNO)
);

CREATE TABLE PROD (
 S_NUM VARCHAR2(20),
 GNO VARCHAR2(20),
 PRI NUMBER, 
 PDATE VARCHAR2(50),
 CONSTRAINT PROD_S_NUM_PK PRIMARY KEY(S_NUM),
 CONSTRAINT PROD_GNO_FK FOREIGN KEY(GNO) REFERENCES GOODS(GNO)
);

 

2. 테이블 교안보고 생성 p96참조

DROP TABLE SCORE;
DROP TABLE STUDENT;
DROP TABLE COURSE;
DROP TABLE PROFESSOR;

PURGE RECYCLEBIN;

CREATE TABLE STUDENT (
 SNO VARCHAR2(8),
 SNAME VARCHAR2(10),
 SEX VARCHAR2(4),
 SYEAR NUMBER(1),
 MAJOR VARCHAR2(10),
 AVR NUMBER(4,2),
 CONSTRAINT STUDENT_SNO_PK PRIMARY KEY(SNO)
);

CREATE TABLE PROFESSOR (
 PNO VARCHAR2(8),
 PNAME VARCHAR2(10),
 SECTION VARCHAR2(10),
 ORDERS VARCHAR2(10),
 HIREDATE DATE,
 CONSTRAINT PROFESSOR_PNO_PK PRIMARY KEY(PNO)
);

CREATE TABLE COURSE (
 CNO VARCHAR2(8),
 CNAME VARCHAR2(14),
 ST_NUM NUMBER,
 PNO VARCHAR2(8),
 CONSTRAINT COURSE_CNO_PK PRIMARY KEY(CNO),
 CONSTRAINT COURSE_PNO_FK FOREIGN KEY(PNO) REFERENCES PROFESSOR(PNO)
);

CREATE TABLE SCORE (
 SNO VARCHAR2(8),
 CNO VARCHAR2(8),
 RESULT NUMBER,
 CONSTRAINT SCORE_SNOCNO_PK PRIMARY KEY(SNO, CNO),
 CONSTRAINT SCORE_SNO_FK FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),
 CONSTRAINT SCORE_CNO_FK FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
);