제약 조건 / PRIMARY KEY / FOREIGN KEY

PRIMARY KEY

1. 테이블당 한개만 만들 수 있음

2. 두개 이상의 컬럼을 조합해서 하나의 기본키를 가질 수 있다.

3. 중복값 안되고 NULL 안되고

 

테이블 생성하면서 같이 만들기

CREATE TABLE test1 (
    id VARCHAR2(30) PRIMARY KEY,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth  DATE
);

제약 조건을 줬으면 확인도 할 줄 알아야 한다.

SELECT * FROM user_constraints WHERE table_name = 'TEST1';
-- 존재 유무 확인 및 P키가 있는 제약 조건 이름 확인

SELECT * FROM user_cons_columns WHERE table_name = 'TEST1';

user_constraints의 결과

타입에 보면 P 이게 기본키의 약자이다.

보면 제약 조건의 이름을 확인 할 수 있다 이걸 기억하고 있다가 활용 할 수 있다.

user_cons_columns의 결과

 

 

SELECT u1.table_name, column_name, constraint_type, u1.constraint_name, search_condition
        FROM user_constraints u1
        JOIN user_cons_columns u2 ON u1.constraint_name = u2.constraint_name
        WHERE u1.table_name = UPPER('test1'); 
        --원래는 테이블 명은 대문자로 써야하는데 UPPER를 통해 소문자도 입력 가능

이렇게 한번에 확인 할 수도 있다.

 

제약 조건 및 컬럼 확인하기

SELECT u1.table_name, column_name, constraint_type, u1.constraint_name, search_condition
        FROM user_constraints u1
        JOIN user_cons_columns u2 ON u1.constraint_name = u2.constraint_name
        WHERE u1.table_name = UPPER('테이블명');

 

 

제약 조건에 이름을 안주면 구분하고 알아보기 힘들다.

이름을 주면서 생성 해보자.

 

CREATE TABLE test2 (
    id    VARCHAR2(30)  CONSTRAINT pk_test2_id  PRIMARY KEY,
    pwd   VARCHAR2(30)  NOT NULL,
    name  VARCHAR2(30)  NOT NULL,
    birth  DATE
);

pk_test2_id << 이게 P 제약조건의 이름이다.

 

테이블 레벨 방식의 제약 조건 부여

(컬럼 다 생성하고 마지막에 조건 부여하기)

CREATE TABLE test3 (
              id    VARCHAR2(30),
              pwd   VARCHAR2(30)  NOT NULL,
              name  VARCHAR2(30)  NOT NULL,
              birth  DATE,
              PRIMARY KEY( id )
        );

이름 부여 안했을때이다.

CREATE TABLE test4 (
              id    VARCHAR2(30),
              pwd   VARCHAR2(30)  NOT NULL,
              name  VARCHAR2(30)  NOT NULL,
              birth  DATE,
              CONSTRAINT pk_test4_id PRIMARY KEY( id )
        );

이름 부여를 해준 모습.

 

 

두개의 컬럼으로 기본키 부여

CREATE TABLE test5 (
    id     VARCHAR2(30),
    code   NUMBER, 
    pwd    VARCHAR2(30)  NOT NULL,
    name   VARCHAR2(30)  NOT NULL,
    birth  DATE,
    CONSTRAINT pk_test5_id PRIMARY KEY( id, code )
);

id와 code가 기본키로 설정된 모습.

 

 

 

이미 존재하는 테이블에 기본 키 부여하기

ALTER  TABLE test6  ADD CONSTRAINT pk_test6_id PRIMARY KEY ( id );

ALTER TABLE ADD를 이용한다.

이렇게 늦게 기본키를 부여할 경우 기존에 데이터가 기본키의 조건을 위반하면 안된다.

 

ex) id의 코드가 같은 데이터들이 여러개 존재한다던지

 

 

기본키 제약 조건 삭제해버리기

ALTER TABLE test6  DROP PRIMARY KEY;

ALTER TABLE DROP를 이용.

 

 

UNIQUE  유니크 제약 조건

* 하나의 테이블에 두개 이상 만들 수 있다.

* NULL을 허용하면 NULL 추가 가능

* 두개 이상의 컬럼으로 UNIQUE를 만들 수 있다.

 

테이블 생성과 동시에 조건을 줄 수 있지만 권장 하지 않는다.

테이블 레벨 방식으로 마지막에 조건을 주는게 좋다.

 

CREATE TABLE test1 (
                  id    VARCHAR2(50),
                  pwd   VARCHAR2(100) NOT NULL,
                  name  VARCHAR2(30) NOT NULL,
                  email VARCHAR2(50),
                  CONSTRAINT  pk_test1_id PRIMARY KEY(id),
                  CONSTRAINT  uq_test1_email UNIQUE(email)
        );

제약 조건을 2개 줬는데

id에 P 기본키를 줬고

email에는 유니크를 줘서 email은 중복으로 존재 못하게 설정 해논 모습이다.

 

단 UNIQUE이지만 NULL을 허용하면 NULL을 추가 가능하고 NULL은 중복 가능

 

 

존재하는 테이블에 UNIQUE 추가 하기

ALTER TABLE test5 ADD CONSTRAINT this_is_unique UNIQUE(id);

 

테이블에서 조건 삭제하기

ALTER TABLE test5 DROP CONSTRAINT this_is_unique;

this_is_unique << 조건 이름임.

 

 

 

NOT NULL은 MODIFY를 사용한다.

ALTER TABLE 테이블명 MODIFY 컬럼  NOT NULL;

ALTER TABLE 테이블명 MODIFY 컬럼 NULL;

ALTER TABLE test1 MODIFY pwd VARCHAR2(100);
--pwd는 컬럼명

 

DEFAULT

데이터의 기본값 설정 해주기.

 

아무런 데이터 입력이 없으면 DEFAULT의 값이 들어가는거임.

 

ALTER  TABLE   테이블명  MODIFY  컬럼명  DEFAULT  값;

ALTER TABLE 테이블명 MODIFY 컬럼 DEFAULT NULL;

CREATE TABLE test1 (
    num       NUMBER  PRIMARY KEY,
    name     VARCHAR2(30) NOT NULL,
    subject   VARCHAR2(500) NOT NULL,
    content  VARCHAR2(4000) NOT NULL,
    reg_date  DATE  DEFAULT  SYSDATE,
    hitCount  NUMBER  DEFAULT 0 NOT NULL
);

 

CHECK 

말 그대로 값을 체크해준다.

내가 정한 조건대로의 값인지 확인

 

CREATE  TABLE  test2
(
    num    NUMBER  PRIMARY  KEY,
    name  VARCHAR2(30)  NOT  NULL,
    com   NUMBER(3)  NOT  NULL,
    excel  NUMBER(3)  CHECK( excel  BETWEEN 0  AND 100 ),
    CONSTRAINT  ch_test2_com  CHECK ( com BETWEEN 0 AND 100 )
);

CREATE TABLE test3(
    num NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    gender VARCHAR2(50)  DEFAULT '여'  CHECK ( gender IN ('남', '여') ) , 
    sdate DATE NOT NULL,
    edate DATE NOT NULL
);

기존 테이블에 CHECK 추가하기

ALTER TABLE test3 ADD CHECK (sdate <=edate);

 

FOREIGN KEY (외래키)

PRIMARY KEY와 함께 중요한 제약 조건이다.

다른 테이블의 컬럼을 참조하거나 자기 자신 테이블 내부에서 서로 참조도 가능하다.

참조하는 컬럼과 참조당하는 컬럼은 타입과 크기는 같아야 하지만 컬럼명은 다를 수 있다

 

 

기존 테이블에 추가하기

ALTER TABLE score ADD CONSTRAINT fk_score FOREIGN KEY(hakbeon) REFERENCES injeok(hakbeon);

 

CREATE TABLE  std (
    hak  VARCHAR2(30)  PRIMARY  KEY,
    name  VARCHAR2(30) NOT NULL,
    email  VARCHAR2(50)  NOT NULL,
    tel      VARCHAR2(30)  NOT NULL
);

부모 테이블을 만들어준다.

CREATE  TABLE  std_score (
            num      NUMBER  PRIMARY KEY,
            hak       VARCHAR2(30)   NOT  NULL,
            gubun   NUMBER(1)  NOT  NULL,
            java      NUMBER(3)  NOT NULL,
            oracle   NUMBER(3)  NOT NULL,
            CONSTRAINT  fk_stdscore_hak FOREIGN  KEY( hak ) REFERENCES  std( hak )
    );

자식 테이블을 만들어준다

hak 이라는 이름을 가진 컬럼을 외래키로 지정하고 

외래키는 std 테이블의 hak을 참조한다.

    SELECT * FROM user_constraints WHERE table_name = 'STD';
    SELECT * FROM user_constraints WHERE table_name = 'STD_SCORE';

정보를 확인해보면 외래키는 R로 표시가 된다.

 

외래키가 있으면 부모 테이블은 삭제가 안된다.

자식 테이블 먼저 삭제를 해주고 삭제를 해야 한다.

 

하지만 강제로 삭제하는 방법이 있다.

DROP TABLE std CASCADE CONSTRAINTS PURGE;

CASCADE를 활용하면 외래키가 참조중임에도 강제로 삭제가 가능하다.

 

CREATE TABLE  std (
           hak  VARCHAR2(30)  PRIMARY  KEY,
           name  VARCHAR2(30) NOT NULL,
           email  VARCHAR2(50)  NOT NULL,
           tel      VARCHAR2(30)  NOT NULL
    );
    
-- 부모 테이블

CREATE  TABLE  std_score (
            num      NUMBER  PRIMARY KEY,
            shak       VARCHAR2(30) ,
            gubun   NUMBER(1)  NOT  NULL,
            java      NUMBER(3)  NOT NULL,
            oracle   NUMBER(3)  NOT NULL,
            FOREIGN  KEY( shak ) REFERENCES  std( hak )
    );
    
-- 자식 테이블

DROP TABLE   std_score PURGE;
DROP TABLE   std PURGE;

-- 자식 테이블 먼저 삭제

자기 자신을 참조(대분류, 중분류 등) 계층적 구조

CREATE  TABLE  test1 (
    num  NUMBER  PRIMARY  KEY,
    subject  VARCHAR2(300) NOT NULL,
    snum  NUMBER,
    FOREIGN  KEY ( snum ) REFERENCES test1 ( num )
);

INSERT INTO test1 (num, subject, snum) VALUES (1, '언어', null);
INSERT INTO test1 (num, subject, snum) VALUES (2, '디비', null);
INSERT INTO test1 (num, subject, snum) VALUES (3, '웹', null);
INSERT INTO test1 (num, subject, snum) VALUES (4, '자바', 1);
INSERT INTO test1 (num, subject, snum) VALUES (5, '오라클', 2);
INSERT INTO test1 (num, subject, snum) VALUES (6, 'MYSQL', 2);

4번 자바는 1번 언어에 속하니까 언어를 참조

마찬가지로 5번 오라클은 2번 디비에 속하고

6번 마이에스큐엘은 2번 디비에 속하니까 2번을 참조

 

 

예제)

CREATE TABLE  member1 (
     id  VARCHAR2(30)  PRIMARY KEY,
     pwd  VARCHAR2(100)  NOT NULL,
     name  VARCHAR2(30)  NOT NULL
);

 

CREATE TABLE  member2 (
     id  VARCHAR2(30),
     birth  DATE,
     tel  VARCHAR2(30),
     CONSTRAINT  pk_member2_id  PRIMARY KEY( id ),
     CONSTRAINT  fk_member2_id  FOREIGN KEY( id )  REFERENCES member1(id)
);

멤버1의 id를 참조 한다.

멤버1의 id = 멤버2의 id

즉 멤버2 테이블은 멤버1의 생일과 전화번호

 

CREATE TABLE guest (
    num  NUMBER  PRIMARY KEY,
    id    VARCHAR2(30) NOT NULL,
    content  VARCHAR2(4000) NOT NULL,
    reg_date DATE  DEFAULT  SYSDATE,
    FOREIGN KEY(id)  REFERENCES member1( id )
);

마찬가지 멤버1의 id를 참조 한다.

 

CREATE TABLE guestLike (
    num  NUMBER,
    id VARCHAR2(30),
    PRIMARY KEY(num, id),
    FOREIGN KEY(num) REFERENCES guest(num),
    FOREIGN KEY(id) REFERENCES member1(id)
);

CREATE TABLE note (
    num  NUMBER  PRIMARY KEY,
    sendId  VARCHAR2(30) NOT NULL,
    receiveId  VARCHAR2(30) NOT NULL,
    content  VARCHAR2(4000)  NOT NULL,
    FOREIGN KEY(sendId) REFERENCES member1(id),
    FOREIGN KEY(receiveId) REFERENCES member1(id)
);

send와 receive가 둘다 멤버1의 id를 참조하는 이유는 회원끼리만 주고 받을 수 있게 하기 위함이다.

 

 

참조키 외래키를 비활성화 하기

ALTER  TABLE  dept_test  DISABLE  CONSTRAINT fk_deptTest_managerId CASCADE;

-- dept_test = 테이블명
-- fk_deptTest_managerId = 제약조건 명

이렇게하면 외래키가 비활성화를 시킬 수 있다.

 

비활성화가 제대로 되었는지 확인해보기

SELECT * FROM user_constraints WHERE table_name = UPPER('dept_test');
-- satatus 항목 확인

참조키를 비활성화 하면 참조키 제약 조건 위반 데이터를 추가 할 수 있다.

 

작업이 끝났으면 다시 참조키를 활성화 시키기

ALTER  TABLE  dept_test  ENABLE  CONSTRAINT fk_deptTest_managerId ;

 

 

 

얽힌 부모 자식 테이블의 관계를 확인해보자.

 

부와 자 관계의 모든 테이블 출력

SELECT fk.owner, fk.constraint_name,
pk.table_name parent_table, fk.table_name child_table
        FROM all_constraints fk, all_constraints pk 
        WHERE fk.r_constraint_name = pk.constraint_name AND fk.constraint_type = 'R'
        ORDER BY fk.table_name;

 

『테이블명』을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)

SELECT fk.owner, fk.constraint_name , fk.table_name 
        FROM all_constraints fk, all_constraints pk 
        WHERE fk.r_constraint_name = pk.constraint_name 
                   AND fk.constraint_type = 'R' 
                   AND pk.table_name = UPPER('테이블명')
        ORDER BY fk.table_name;

테이블명에는 테이블명 입력

 

『테이블명』이 참조하고 있는 모든 테이블 목록 출력(부모 테이블 목록 출력)

SELECT table_name FROM user_constraints
        WHERE constraint_name IN (
              SELECT r_constraint_name 
              FROM user_constraints
              WHERE table_name = UPPER('테이블명') AND constraint_type = 'R'
          );

 

『테이블명』의 부모 테이블 목록 및 부모 컬럼 목록 출력

SELECT fk.constraint_name, fk.table_name child_table, fc.column_name child_column,
                    pk.table_name parent_table, pc.column_name parent_column
        FROM all_constraints fk, all_constraints pk, all_cons_columns fc, all_cons_columns pc
        WHERE fk.r_constraint_name = pk.constraint_name
                   AND fk.constraint_name = fc.constraint_name
                   AND pk.constraint_name = pc.constraint_name
                   AND fk.constraint_type = 'R'
                   AND pk.constraint_type = 'P'
                   AND fk.table_name = UPPER('테이블명');