[SQL] 제약조건 (CONSTRAINTS)

제약조건: CONSTRAINTS

- 종류: NOT NULL, UNIQUE, CHECK,PRIMARY KEY, FOREIGN KEY
- 원하는 데이터값만을 유지, 보관하기 위해 특정 '컬럼'마다 설정하는 제약
- 목적: 데이터의 무결성 보장 (권한 없는자가 데이터 위변조)
- 제약조건이 부여된 컬럼에 들어올 데이터가 문제가 있는지, 없는지 자동으로 검사
-부여방식: 1) COLUMN LEVEL 2) TABLE LEVEL

CREATE TABLE 테이블명 (
	컬럼명1 데이터타입 DEFAULT [DEFAULT값] [컬럼 레벨 제약조건],
	컬럼명2 데이터타입 DEFAULT [DEFAULT값] [컬럼 레벨 제약조건],
	컬럼명3 데이터타입 DEFAULT [DEFAULT값] [컬럼 레벨 제약조건],

    [테이블 레벨 제약조건]
 )

 

 

1. NOT NULL 제약조건:

해당 컬럼에 반드시 값이 존재해야만 하는 경우 사용 (절대 NULL값이 들어오면 안되는 경우)
삽입, 수정시 NULL값 허용하지 않도록 제약
단, NOT NULL은 COLUMN LEVEL만 가능

--NOT NULL 제약조건만 설정한 테이블 만들기(컬럼레벨 : 컬럼명 자료형 제약조건 )

CREATE TABLE MEM_NOTNULL (
	MEM_NO NUMBER NOT NULL ,
	MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) ,
    PHONE VARCHAR(15),
    EMAIL VARCHAR(30)
);

INSERT INTO 
		MEM_NOTNULL
		VALUES(1,'user01','pass01','홍길동', '남','010-1234-5687','hgd@gg.com');


INSERT INTO
		MEM_NOTNULL
		VALUES (2, NULL, NULL, NULL, NULL, NULL, NULL);
                -- NOT NULL제약조건 위배로 인한 오류발생 cannot insert NULL into
                -- not null제약조건이 부여되어 있는 컬럼에는 반드시 값이 존재해야함

2. UNIQUE제약조건

컬럼에 중복값을 제한하는 제약조건
데이터 조작시(데이터 삽입, 수정시) 기존 해당 컬럼값 중 중복값이 존재할 경우 데이터 조작아 불가능하도록 제약
컬럼레벨, 테이블레벨 둘다 적용가능

CREATE TABLE MEM_UNIQUE (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, ---컬럼레벨
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20),
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);



CREATE TABLE MEM_UNIQUE (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR(20) NOT NULL,
    MEM_PWD VARCHAR(20) NOT NULL,
    MEM_NAME VARCHAR(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(3),

    UNIQUE(MEM_ID) -- 테이블 레벨 방식
);
	INSERT
	  INTO
MEM_UNIQUE
	VALUES (1, 'user01', 'pwd01,'kim', '여, NULL, NULL);


	INSERT
	  INTO
MEM_UNIQUE
	VALUES
            (2, 'user01', 'pwd03','lee', '여', NULL, NULL);
--ORA-00001:unique constraint (DDL.SYS_C007027---제약조건명, 지정가능!) violated ==>insert실패
--unique 제약조건 위배되었으므로 insert실패

 

3. CHECK 제약조건

컬럼에 기록 가능한 값에 대한 조건 설정

CREATE TABLE MEM_CHECK (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(30) NOT NULL,
    MEM_NAME VARCHAR(30) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')) NOT NULL,
    PHONE VARCHAR2(30),
    EMAMIL VARCHAR2(30),
    MEM_DATE DATE NOT NULL
);


INSERT INTO MEM_CHECK VALUES (1, 'user01', 'pass01', '홍길동', '남', NULL, NULL, SYSDATE);

-- 회원가입일을 항상 SYSDATE로 받고싶다 :초기값 지정가능

4. PRIMARY KEY (기본키), PK

- 테이블에서 각 행의 정보를 유일하게 식별가능한 컬럼에 부여하는 제약조건
- 각 행들을 구분가능한 식별자 역할
- 사번, 학번, 주문번호, 예약번호, 회원번호 등
- 중복되지 않고(UNIQUE)  값이 항상 존재해야하는(NOT NULL) 컬럼에 PRIMARY KEY 제약조건 부여
   (UNIQUE + NOT NULL)
- 한 테이블당 한 개의 컬럼만 설정가능
- 숫자값 컬럼으로 지정하는 것이 가장 편함!  

CREATE TABLE MEM_PRIMARY_KEY(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(20)
);


INSERT INTO MEM_PRIMARY_KEY VALUES(1, 'user01', 'pass01', '홍길동', '남', NULL, NULL);

INSERT INTO MEM_PRIMARY_KEY VALUES(NULL, 'user02','pass02','홍길동', NULL, NULL, NULL);
--ORA-01400: cannot insert NULL into ("DDL"."MEM_PRIMARY_KEY"."MEM_NO")
--PK컬럼에 NULL 값을 넣을 수 없음

INSERT INTO MEM_PRIMARY_KEY VALUES(1, 'user02','pass02','홍길동', NULL, NULL, NULL);
--ORA-00001: unique constraint (DDL.MEM_PK) violated



CREATE TABLE MEM_PRIMARY_KEY2(
	MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
	MEM_ID VARCHAR2(20) PRIMARY KEY, 
   --ORA-02260: table can have only one primary key, 02260. 00000 - "table can have only one primary key"
	MEM_PWD VARCHAR2(20) NOT NULL, 
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(20)
);

             
 <복합 PRIMARY KEY: Composite Key>

   : 다중의 컬럼을 묶어서 하나의 PK로 사용

- 복합KEY (Composite Key)  한 테이블당 PK는 여러개일 수 없으나,    
  PK가 반드시 한개의 컬럼일 필요는 없다! 
 여러 컬럼을 하나로 묶어서 PRIMARY KEY로 설정이 가능함  ==> 
 컬럼을 묶어서 TABLE레벨에서 PRIMARKY KEY로 설정
- 복합KEY에서의 UNIQUE 조건
  : 복합KEY를 이루는 각각의 컬럼이 모두 중복되는 것이 아니라, 둘 중 하나가 다를 시 다른 것으로 간주

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER,
    MEM_ID VARCHAR(20),
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),

    PRIMARY KEY(MEM_NO, MEM_ID) --- 두개가 한 세트이기 때문에 둘중에 하나만 달라도 데이터 삽입이 가능함
);

 

INSERT INTO MEM_PRIMARYKEY2
VALUES (2, 'user01', 'pass02', 'hong', NULL, NULL, NULL); 
-- 두개가 한 세트이기 때문에 둘중에 하나만 달라도 데이터 삽입이 가능함
    
    
INSERT INTO MEM_PRIMARYKEY2
VALUES (NULL, 'user02', 'pass03', 'hong', NULL, NULL, NULL);   
-- 기본키로 설정되어있는 컬럼들에는 NULL값이 들어갈 수 없음

    

5. FOREIGN KEY (외래키) 제약조건

- 해당 컬럼에는 다른 테이블에 존재하는 값만 들어와야할 때 부여하는 제약조건
=> 다른 테이블(부모테이블)을 참조하는 값만 들어올 수 있음
==> 제약조건으로 다른 테이블들간의 관계형성 가능
- 데이터타입은 부모테이블 데이터타입과 동일해야함

 1) 컬럼레벨 방식 

컬럼형 자료형 REFERENCES 참조테이블명(참조할컬럼명)

CREATE TABLE MEM (
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE),  --- 자료형은 부모테이블의  타입과 동일하게
    GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

2) 테이블 레벨 방식

FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할 컬럼명)

CREATE TABLE MEM (
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) ,  --- 자료형은 부모테이블의  타입과 동일하게
    GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

 

==>참조할 컬럼명은 두 방식 모두 생략 가능함
생략시 기본적으로 참조할 테이블의 PRIMARY KEY가 참조할 컬럼명으로 설정됨

INSERT INTO MEM
VALUES (4, 'user04', 'pass04', '김똥똥',  NULL, NULL, NULL, NULL);
    
INSERT INTO MEM
VALUES (5, 'user05', 'pass05', '김땅땅',  'G4', NULL, NULL, NULL); 
-- ORA-02291: integrity constraint (DDL.SYS_C007136) violated - parent key not found
--PARENT KEY 에 존재하는 값만 FOREIGN KEY의 값으로 넣을 수 있음

    
==> 부모테이블에서 자식테이블이  FOREIGN KEY로 참조하고 있는 데이터가 삭제된다면?

- 부모테이블로부터 자식테이블이 참조하는 내용을 삭제하려고 하면
   --> CHILD RECORD FOUND 에러 발생
- 외래키 제약조건 부여시 삭제 옵션을 부여하여야만 참조하고있는 부모 테이블 삭제가 가능
- 자식테이블에서 사용하고 있는 값이 있을 경우 삭제가 안되는 '삭제제한옵션'이 걸려있음

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';  
--  ORA-02292: integrity constraint (DDL.SYS_C007136) violated - child record found
  -- 부모테이블로부터 자식테이블이 참조하는 내용을 삭제하려고 하면 CHILD RECORD FOUND 에러 발생
  -- 외래키 제약조건 부여시 삭제 옵션을 부여하여야만 참조하고있는 부모 테이블 삭제가 가능하다
 -- 자식테이블에서 사용하고 있는 값이 있을 경우 삭제가 안되는 '삭제제한옵션'이 걸려있음
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G3'; ---G3은 자식테이블에서 사용하고있지 않음 --> 삭제가능

SELECT * FROM MEM_GRADE; ---DELETE 시 전체 행이 날라감

3) FOREIGN KEY 삭제옵션

자식 테이블 생성시 외래키 제약조건을 부여하면
부모테이블의 데이터 삭제시 자식테이블에서는 해당 데이터를 어떻게 처리할지 옵션으로 정할 수 있음


1) ON DELETE RESTRICTED: 부모데이터 삭제 불가 (CHILD RECORD FOUND)
    삭제옵션 별도 미지정시 기본적으로 ON DELETE RESTRICTED (삭제제한) 으로 설정 (DEFAULT)
2) ON DELETE SET NULL: 부모데이터 삭제시 해당데이터를 사용하는 자식 데이터를 NULL로 변경
3) ON DELETE CASCADE: 부모데이터 삭제시 해당 데이터를 사용하는 자식의 데이터도 삭제

 

CREATE TABLE MEM2 ( 
        MEM_NO NUMBER PRIMARY KEY,
        MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR2(20) NOT NULL,
        GRADE_ID CHAR(2),
        GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(30),
        FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL
);

 

 CREATE TABLE MEM ( 
        MEM_NO NUMBER PRIMARY KEY,
        MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR2(20) NOT NULL,
        GRADE_ID CHAR(2),
        GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(30),
        FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE
);
 
INSERT INTO MEM
VALUES (1, 'user01', 'pass01', '홍',  'G1', NULL, NULL, NULL);
    
INSERT INTO MEM
VALUES (2, 'user02', 'pass02', '김',  'G2', NULL, NULL, NULL);
    
    
INSERT INTO MEM
VALUES (3, 'user03', 'pass03', '황',  'G1', NULL, NULL, NULL);

INSERT INTO MEM 
VALUES(4, 'user04', 'pass04', 'ee', NULL, NULL, NULL, NULL);

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1'; ---- 부모테이블 삭제되면서 자녀테이블도 같이 삭제됨

 

6. 제약조건명 설정하기 

제약조건 부여시,
해당 제약조건에대한 별칭을 같이 지정하는 표현식

   1) 컬럼레벨 방식

CREATE TABLE 테이블명 (
컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
컬럼명 자료형
...

);

 2) 테이블레벨 방식

CREATE TABLE 테이블명 (
컬럼명 자료형 ,
컬럼명 자료형,
CONSTRAINT 제약조건명 제약조건(컬럼명)
);

CREATE TABLE MEM_CON_NN (
MEM_NO NUMBER NOT NULL,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR (20) CONSTRAINT MEM_NAME_NN NOT NULL, -- 컬럼레벨로 제약조건명 지정
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
CONSTRAINT MEM_ID_UNIQUE UNIQUE (MEM_ID) ---테이블레벨로 제약조건명 지정
);




INSERT INTO MEM_CON_NN VALUES (1, 'user01', 'pass01', '홍길동', NULL, NULL, NULL);
INSERT INTO MEM_CON_NN VALUES (2, 'user02', 'pass02', '김길동', '가', NULL, NULL);

 

 

7. 컬럼에 주석달기 ( 컬럼에 대한 설명 )

COMMENT ON COLUMN 테이블명.컬럼 IS '주석내용';

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.MEMBER_DATE IS '회원가입일';

 

 

<기타> 

DATA DICTIONARY
다양한 객체의 정보를 저장하고있는 시스템 테이블
-USER_TABLES: 현재 이 계정이 가지고 있는 테이블의 전반적인 구조를 확인할 수 있는 DATA DICTIONARY