제약조건 특징, 종류, 검색, PK와 FK의 특징, 무결성 통제, 명령어 개념까지

 

제약 조건의 이해(기본 특징)

1. 테이블 단위 정의: 규칙은 표(테이블) 전체에 미리 정해준다. 표를 만들 때 "이 칸에는 이런 자료만 들어 올 수 있어" 라고 설정하는 것  2. 삭제방지(종속성); 두 표가 서로 연결되어 있을때, 한쪽을 마음대로 지우면 연결된 다른 쪽 자료가 미아가 된다. 이걸 막기 위해 연결된 자료가 있으면 삭제를 못 하게 막아준다 3. 매번 적용: 자료를 새로 넣을때(INSERT), 고칠 때(UPDATE), 지울 때(DELETE) 마다 컴퓨터가 규칙을 잘 지켰는지 매번 검사 한다 4. 켰다 끄기 가능: 상호아에 따라 이 규칙을 잠시 멈춰두거나(비활성화), 다시 작동하게(활성화) 할 수 있다 5. 이름 필수: 이 규칙들은 데이터 베이스 안에서 하나의 독립된 물건처럼 관리 된다. 그래서 나중에 규칙을 착거나 고치기 위해 반드시 각각의 이름이 붙어 있어야 한다

 

오라클 제약조건 5가지 (종류)

제약조건 이름	핵심 기능 (내용)
PRIMARY KEY (기본키)	비어있으면 안 되고, 겹쳐서도 안 됨. 자료 전체에서 딱 하나만 존재해야 하는 핵심 식별값
FOREIGN KEY (외래키)	다른 표와 연결. 다른 표에 이미 들어있는 값만 쓸 수 있게 연결하여 서로 어긋나지 않게 함
UNIQUE KEY (고유키)	겹치면 안 됨. 하지만 비어있는 것(NULL)은 허용 (중복만 금지)
NOT NULL	비어있으면 안 됨. 반드시 어떤 값이라도 입력해야 함
CHECK	정해진 규칙 확인. "숫자는 10보다 커야 함" 처럼 내가 직접 정한 조건에 맞는지 검사함

 

PK, FK

PK - Primary Key, 주키, 식별자 - 테이블마다 한 개만 정의 할 수 있음 - 테이블 내에 모든 행을 유일하도록 식별해주는 컬럼 - 모든 컬럼은 PK 컬럼에 함수적 종속 관계를 갖는다  FK - Foreing Key, 외부키, 외부식별자 - 테이블 간 관계를 의미함  - 항상 부모-자식 관계 - 자식 테이블의 참조 컬럼에 지정함 - 두 컬럼에 데이터 타입이 일치해야함  -  PK나 UK만 참조 가능

 

PK, FK 설정 

SQL> CREATE TABLE 테이블 ( 2   ..... 3   CONSTRAINT 제약_조건 PRIMARY KEY (컬럼)); SQL> CREATE TABLE 테이블 ( 2   ..... 3   CONSTRAINT 제약_조건 FOREIGN KEY (컬럼) 4   REFERENCES 참조할_테이블 (참조할_컬럼) [ON DELETE CASCADE]); * FOREIGN KEY는 테이블을 참조하는 것이므로 REFERENCES 참조할_테이블을 적어야 함

 

제약 조건 검색

명령어 SQL> SELECT c.table_name, c.constraint_name, c.constraint_type, 2          c.status, s.column_name 3  FROM user_constraints c, user_cons_columns s 4  WHERE c.constraint_name = s.constraint_name 5  AND c.table_name in (검색_대상_테이블_목록) 6  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 등)이 있고, 그 규칙이 어떤 컬럼에 걸려 있는지 보여준다 SELECT c.table_name, c.constraint_name, c.constraint_type, -- 테이블이름, 규칙이름, 규칙종류를 보여줘        c.status, s.column_name                            -- 규칙이 작동중인지, 어떤 컬럼에 걸렸는지 보여줘 * status: 규칙의 활성화 여부 colunm_name: 규칙이 적용된 컬럼 이름 * c.status에서 c: constraints의 약자 s.conlumn에서 c: columns의 약자  FROM user_constraints c, user_cons_columns s              -- 규칙 정보 주머니(c)와 컬럼 정보 주머니(s)에서 가져올게 * user_constraints: 규칙의 이름과 종류는 알지만 정작 어느 칸에 걸려 있는지 모름 user_cons_colmns: 규칙의 이름과 어느 칸에 걸려 있는지 알지만, 이게 PK인지 FK 인지 모름. 그래서 규칙 이름 이라는 공통된 단서를 가지고 두 정보를 합쳐서 우리가 원하는 전체 정보를 볼 수 있다 ---> 셀프 조인 WHERE c.constraint_name = s.constraint_name               -- 두 주머니에서 이름이 똑같은 것끼리 짝을 지어줘 * s 주머니에서 칸 이름을 가져오고, c 주머니에서 규칙 종류를 가져와서 규칙이름이 똑같은 것끼리 옆으로 딱 붙여주는 것이다 AND c.table_name in ('EMP', 'DEPT')                       -- EMP랑 DEPT 테이블 것만 골라서 보여줘 * 검색하고 싶은 테이블 목록을 적는다 ---> 안쓰면 모든 테이블의 제약 조건이 다 나오게 된다 ORDER BY c.table_name;                                    -- 테이블 이름 순서대로 깔끔하게 정리해줘 * 한 테이블에 제약 조건이 여러 개 있을 수 있으므로 반드시 정렬함 2. 테이블 간의 연결 관계 검색(상하 관계 확인) 어떤 테이블이 상위(부모)이고, 어떤 테이블이 그걸 따라가는 하위(자식)인지 보여준다 SELECT p.table_name 상위테이블, p.constraint_name 상위제약조건, -- 주인 테이블과 그 규칙 이름을 보여줘        c.table_name 하위테이블, c.constraint_name 참조제약조건  -- 따라가는 테이블과 그 규칙 이름을 보여줘 FROM user_constraints p, user_constraints c                  -- 규칙 주머니 두 개(p, c)를 준비해 WHERE c.r_constraint_name=p.constraint_name         -- 자식이 가리키는 이름과 주인의 이름이 똑같은 것만 골라 AND p.table_name in ('EMP', 'DEPT')                       -- 그중 주인 이름이 EMP랑 DEPT 테이블이 포함된 것만 보여줘 ORDER BY p.table_name;                                       -- 주인 테이블 이름 순서대로 보여줘

 

명령어에 나온 단어들의 정확한 개념

단어 (명칭)	뜻 (개념)	역할 (설명)
p (parent)	상위(부모) 주머니	기준이 되는 주인 테이블의 규칙 정보를 담고 있는 별명
c (child)	하위(자식) 주머니	주인을 따라가는 테이블의 규칙 정보를 담고 있는 별명
user_constraints	규칙 장부	모든 테이블의 규칙(PK,FK등) 이 적혀 있음
table_name	테이블 이름	정보를 담은 상자의 이름 (예: DEPT, EMP)
constraint_name	규칙 이름	각 테이블이 가진 규칙에 붙은 이름 (예: PK, FK 이름)
r_constraint_name	참조 규칙 이름	자식이 어떤 주인의 규칙 이름을 가리키고 있는지 적어둔 칸

1. 상위테이블(주인) 개념: 정보의 기준이 되는 원본 테이블 역할: 다른 테이블에서 빌려 쓸 정보를 미리 가지고 있는 쪽임. 여기서는 부서(dept) 정보가 있어야 사원(emp)이 어느 부서인지 정할 수 있으므로 부서가 주인이 된다  2. 상위제약조건(주인규칙) 개념: 주인 테이블에서 가장 중요한 칸에 컬린 "기본키(Primary Key)" 규칙 역할: "이 칸은 절대 중복되면 안되고, 비어 있어도 안된다"는 규칙. 예시의 DEPT_DNO_PK는 부서 번호가 겹치기 않게 관리하는 주인만의 고유 번호표  3. 하위테이블(자식) 개념: 주인 테이블의 정보를 참조해서 따라가는 테이블 역할: 독자적으로 존재하기보다 주인 테이블에 있는 정보를 연결해서 사용하는 쪽임. 사원(emp)은 반드시 존재하는 부서에 소속되어야 하므로 자식이 됨 4. 참조제약조건(연결규칙) 개념: 자식 테이블에서 주인 테이블을 바라보게 만드는 "외래키(Foreign Key)" 규칙  역할: 이 칸에 적힐 내용은 반드시 주인 테이블의 번호표에 있는 것이어야 한다는 연결 고리. EMP_DNO_FK는 사원 테이블의 부서 번호가 실제 테이블(DEPT)에 있는 번호인지 확인하는 감시관 역할

 

제약조건에 의한 무결성 통제 

SQL> INSERT INTO dept (dno, dname, loc) VALUES ('10','개발','서울'); ---> 부서 테이블(부모) SQL> INSERT INTO emp (eno,ename, dno) VALUES ('2000','문시현','10'); ---> 사원 테이블(자식) SQL> COMMIT; SQL> INSERT INTO dept (dno, dname, loc) VALUES ('10','총무','부산'); * 부서 번호(dno)는 기본키(PK) 라서 이미 있는 10번을 또 만들 수 없다 SQL> DELETE FROM dept WHERE dno = '10'; * 10번 부서에 소속된 사원(문시현)이 이미 있어서 연결된 자식이 있는 주인은 지울 수 없다  ← ORA-00001: 무결성 제약 조건.... 에러 발생 SQL> INSERT INTO emp (eno,ename, dno) VALUES ('2001','손하늘','20'); * 부서 테이블(dept)에 20번 부서가 아직 만들어지지 않아서 없는 부서에 사원을 넣을 수 없다 ← ORA-02291: 무결성 제약 조건.... 에러 발생 ← ORA-02291: 무결성 제약 조건.... 에러 발생 SQL> UPDATE emp SET dno = '20' WHERE ename = '문시현'; * 부서 테이블에 '20'번 부서가 존재하지 않으므로, 사원의 소속 부서를 없는 번호로 바꿀 수 없다 ← ORA-02291: 무결성 제약 조건.... 에러 발생

핵심 요약  1. 부서 테이블(부모): 번호가 중복되면 안 되고(PK), 사원이 연결되어 있으면 지울 수 없다 2. 사원 테이블(자식): 반드시 부서 테이블에 실제로 있는 번호만 사용해야 한다(FK) * 지울때는 자식부터 , 생성은 부모부터 생성함 (DEPT 만들기 전에 EMP는 만들 수 없음)