DB(SQL)

[SQL] CONSTRAINTS(제약조건)

이나피스 2022. 3. 27. 16:38
반응형

CONSTRAINTS (제약조건)

원하는 데이터값만 유지하기 위해 특정 컬럼마다 설정하는 제약
제약조건이 부여된 컬럼에 들어올 데이터에 문제 여부를 자동으로 검사하는 목적(데이터 무결성)

종류 제약조건 제약조건 부여 방식
NOT NULL 데이터 삽입/수정 시 NULL값을 허용하지 않게 제한 컬럼레벨방식
UNIQUE 컬럼에 중복값을 제한 컬럼레벨방식, 테이블레벨방식
CHECK  컬럼에 기록될 수 있는 값을 제한  
PRIMARY KEY
(기본키)		 테이블에서 각 행들의 정보를 유일하게 식별할수 있도록 제한  
FOREIGN KEY
(외래키)		 두 테이블을 연결할 때 사용되는 키  

 

제약조건 부여방식

//컬럼레벨방식
CREATE TABLE 테이블명(
컬럼명 자료형 제약조건1 제약조건2,
컬럼명 자료형 CONSTRAINT 조약조건명 제약조건,
컬럼명 자료형,
...
);

//테이블레벨방식
CREATE TABLE 테이블명(
컬럼명 자료형,
컬럼명 자료형,
...,
컬럼명 자료형,
CONSTRAINT 제약조건명 제약조건(컬럼명)
);
CREATE TABLE MEM_CON_NM(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEM_NAME_NN NOT NULL, --컬럼레벨방식
    GENDER CHAR(3),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID) --테이블레벨방식
);

INSERT INTO MEM_CON_NM VALUES(10,'test01','test01','김테스트',NULL,NULL,NULL);
INSERT INTO MEM_CON_NM VALUES(12,'test01','test02','김테스트2',NULL,NULL,NULL);
--오류발생!! unique constraint (DDL.MEM_ID_UQ)
INSERT INTO MEM_CON_NM VALUES(15,'test51','test012','김연습','나',NULL,NULL); 
--성별에 '나'로 오타냄
--(DDL.MEM_ID_UQ)제약조건이 오류문구에 발생하니까 오류 찾기가 쉬움

SELECT *
FROM MEM_CON_NM;

 

NOT NULL

NULL값이 절대 들어와서는 안되는 컬럼에 부여하는 제약조건

해당 컬럼에 반드시 값이 존재해야만 할 경우 사용
데이터 삽입/수정 시 NULL값을 허용하지 않게 제한

컬럼레벨방식으로 작성

--테이블 생성
CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

--데이터 추가
INSERT INTO MEM_NOTNULL 
       VALUES(1,'user01','password01','김일번','남','010-1234-1243','kim1@gmail.com');
INSERT INTO MEM_NOTNULL 
       VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL); --오류발생!! cannot insert NULL into
INSERT INTO MEM_NOTNULL 
       VALUES(2,'user02','password02','박이번',NULL,NULL,NULL);
INSERT INTO MEM_NOTNULL 
       VALUES(3,'user03','password03','유삼번','여',NULL,'yu3@gmail.com');

--테이블 확인
SELECT *
FROM MEM_NOTNULL;

 

UNIQUE

컬럼에 중복값을 제한하는 제약조건
삽입/수정 시 기존에 해당 컬럼값 중에 중복값이 있을 경우 추가 또는 수정이 되지 않게 제약
컬럼레벨방식, 테이블레벨방식으로 작성

--컬럼레벨 방식
--테이블 생성
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --한개의 컬럼에 여러가지 제약조건을 걸수있다
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

--데이터 입력
INSERT INTO MEM_UNIQUE VALUES(1,'user01','password01','장기하',NULL,NULL,NULL);
INSERT INTO MEM_UNIQUE VALUES(2,'user01','password11','장기상',NULL,NULL,NULL); 
--오류발생!! unique constraint


--테이블레벨링 방식
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    UNIQUE(MEM_NAME) --테이블레벨링
);

--데이터 입력
INSERT INTO MEM_UNIQUE VALUES(1,'user1','pw01','김당산',NULL,NULL,NULL);
INSERT INTO MEM_UNIQUE VALUES(1,'user2','pw02','김당산',NULL,NULL,NULL); 
--오류발생!! unique constraint

 

CHECK

컬럼에 기록될 수 있는 값에 대한 조건을 설정하는 제약조건
EX) 성별 '남' OR '여'만 들어올수 있게 하고 싶다

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE NOT NULL --회원가입일
);

INSERT INTO MEM_CHECK 
       VALUES(1,'user11','password','김열심','남','010-3333-7777','kimhus@nate.com',SYSDATE);
INSERT INTO MEM_CHECK 
       VALUES(2,'user22','password','김최고','여','010-1111-6666','kimbest@nate.com',SYSDATE);
INSERT INTO MEM_CHECK 
       VALUES(3,'user33','password','김노력','나','010-6666-5555','kimtry@nate.com',SYSDATE); 
       --오류발생!!

DEFAULT 설정

특정 컬럼에 들어올 값에 대한 기본값 설정 가능

DEFAULT 설정 후 제약조건을 작성한다

EX) 회원가입일 컬럼에 회원정보가 삽입된 순간의 시간을 기록하고 싶다 -> SYSDATE

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL --DEFAULT 먼저 설정 후 제약조건 작성
);

INSERT INTO MEM_CHECK 
       VALUES(2,'user22','password','김최고','여','010-1111-6666','kimbest@nate.com',SYSDATE);
INSERT INTO MEM_CHECK 
       VALUES(2,'user22','password','김최고','여','010-1111-6666','kimbest@nate.com',DEFAULT);
INSERT INTO MEM_CHECK 
       (MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GENDER)VALUES(2,'user22','password','김최고','여');
--컬럼 형식을 지정해주면 지정이 안된 컬럼에 대하여 기본 NULL값 입력
--DEFAULT 설정 시 DEFAULT값 입력

 

PRIMARY KEY (기본키)

테이블에서 각 행들의 정보를 유일하게 식별할수 있는 컬럼에 부여하는 제약조건
각 행들을 구분할 수 있는 식별자의 역할이다
식별자의 조건 : 중복값 X, NULL값 X (NOT NULL + UNIQUE)

한 테이블 당 한개의 컬럼에만 지정 가능하다

EX) 사번, 부서아이디, 직급코드, 회원번호, 학번 등

CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO MEM_PRIMARYKEY1(MEM_NO,MEM_ID,MEM_PW,MEM_NAME) 
       VALUES (1,'test1','test2','김테스트');
INSERT INTO MEM_PRIMARYKEY1(MEM_NO,MEM_ID,MEM_PW,MEM_NAME)
       VALUES (1,'test2','test3','김밥'); --오류발생!! UNIQUE
INSERT INTO MEM_PRIMARYKEY1(MEM_NO,MEM_ID,MEM_PW,MEM_NAME)
       VALUES (NULL,'test2','test3','김밥'); --오류발생!! NOT NULL

 

FOREIGN KEY (외래키)

해당 컬럼에 다른 테이블에 존재하는 값만 들어와야 하는 컬럼에 부여하는 제약조건
외래키 값을 제공하는 부모테이블과 외래키가 포함된 자식테이블이 존재한다

EX) COMPANY계정에서 EMPLOYEE테이블 <--- DEPARTMENT테이블 관계인 경우

자식 부모
DEPT_CODE DEPT_ID

-> DEPT_CODE에는 DEPT_ID에 존재하는 값들만 들어올 수 있다.
=  FOREIGN KEY 제약조건(연결고리 역할)으로 다른 테이블과 관계를 형성 할 수 있다 (JOIN)

참조컬럼타입과 외래키 지정컬럼타입이 같을 경우 참조컬럼명 생략가능
자동으로 참조테이블의 PRIMARY KEY에 해당되는 컬럼이 참조컬럼으로 잡힘

컬럼명 자료형 REFERENCES 참조테이블명(참조컬럼명(생략가능))

FOREIGN KEY 삭제 옵션

자식테이블 생성 시(외래키 제약조건을 부여 시) 부모테이블의 데이터 삭제가 되었을 때

자식테이블 데이터 처리를 옵션으로 지정가능하다

ON DELETE SET NULL : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 NULL로 바꾸겠다.
ON DELETE CASCADE : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 같이 삭제하겠다.
ON DELETE RESTRICTED : 삭제 제한(기본옵션)

외래키 예제) 

① 부모테이블과 자식테이블 생성

--부모테이블 만들기
--회원등급에 대한 데이터(등급코드,등급명)를 보관하는 테이블
CREATE TABLE MEM_GRADE(
    GRADE_CODE CHAR(2) PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

INSERT INTO MEM_GRADE VALUES('G1','일반회원');
INSERT INTO MEM_GRADE VALUES('G2','우수회원');
INSERT INTO MEM_GRADE VALUES('G3','특별회원');

--자식테이블
--회원정보를 담는 테이블
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW 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(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(1,'user1','pwd1','김패스','G1');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(2,'user2','pwd2','김쓰루패스','G2');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(3,'user3','pwd3','김롱패스','G3');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(4,'user4','pwd3','김백패스','G4'); 
--오류발생!! parent key not found 참조하고 있는 컬럼에 없는 데이터를 넣어서
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(5,'user5','pwd3','김하이패스',NULL); --NULL값은 잘 들어감

좌) 부모테이블 / 우) 자식테이블

② 부모테이블(MEM_GRADE)에서 데이터값이 삭제된다면?

--MEM_GRADE 테이블로부터 GRADE_CODE가 'G1'인 데이터만 지워보자
DELETE FROM MEM_GRADE WHERE GRADE_CODE='G1'; --오류발생!!
--child record found : 자식테이블중에 G1값을 참조해서 사용하고 있기 때문에 삭제할수 없다
--현재 외래키 제약조건 부여시 삭제에 대한 옵션을 따로 부여하지 않았다.
--> 기본적으로 삭제 제한 옵션이 걸려있다

③ 위의 자식테이블 삭제 후 삭제옵션 재설정하자

--1) ON DELETE SET NULL
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL, --외래키 지정
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(1,'user1','pwd1','김패스','G1');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(2,'user2','pwd2','김쓰루패스','G2');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(3,'user3','pwd3','김롱패스','G3');

SELECT * FROM MEM;

--부모테이블의 GRADE_CODE 중 'G1'인 데이터 삭제
DELETE FROM MEM_GRADE WHERE GRADE_CODE='G1'; --삭제되면서 자식테이블의 'G1'값이 NULL로 변경됨
--2)ON DELETE CASCADE
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE, --외래키 지정
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(1,'user1','pwd1','김패스','G1');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(2,'user2','pwd2','김쓰루패스','G2');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(3,'user3','pwd3','김롱패스','G3');

SELECT * FROM MEM;
--부모테이블(MEM_GRADE)에서 GRADE_CODE 'G1' 삭제 -- ON DELETE CASCADE
DELETE FROM MEM_GRADE WHERE GRADE_CODE='G1'; 
--삭제되면서 자식테이블의 'G1'값을 가진 튜플이 삭제됨

SELECT * FROM MEM_GRADE;

좌) 부모테이블 / 우) 자식테이블(해당 튜플 내용 전체가 삭제됨)

 

반응형