안녕하세요.
https://blog.naver.com/sysysy0302여니입니다 :)
<제약조건 CONSTRAINTS>
- 원하는 데이터값만 유지하기 위해서 (보관하기 위해서) 특정 컬럼마다 설정하는 제약
(데이터 무결성 보장을 목적으로)
- 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 있는지 없는지 자동으로 검사할 목적
- 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY
- 컬럼에 제약조건을 부여하는 방식 : 컬럼레벨방식 / 테이블레벨방식
--> 컬럼레벨방식 : 컬럼명 자료형 제약조건 => 제약조건을 부여하고자하는 컬럼 뒤에 곧바로 기술하는 방법
--> 테이블 레벨방식 : 모든 칼럼을 다 기술하고, 그 이후에 제약조건을 나열
1. NOT NULL 제약조건
해당 컬럼에 반드시 값이 존재해야만 할 경우 사용
=> 즉 NULL값이 절대 들어와서는 안되는 컬럼에 부여하는 제약조건
삽입/수정시 NULL값을 허용하지 않도록 제한하는 제약조건
-- 컬럼레벨 방식으로만 등록 가능.
ex)-- NOT NULL 제약조건을 설정한 테이블 만들기
CREATE TABLE MEM_NOTNULL(
MEM_NO NUMBER NOT NULL,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30)
);
-- 추가할 행에 직접 NOT NULL 제약조건 걸기
INSERT INTO MEM_NOTNULL
VALUES (1, 'user01', 'pass01', '소연', '여', '010-1111-1111', 'thdusthdus@naver.com');
SELECT * FROM MEM_NOTNULL
VALUES (2, 'user01', 'pass01', NULL, '여', '010-1111-1111', 'thdusthdus@naver.com');
-- DDL 계정에 MEM_NOTNULL 테이블에 NOTNULL 제약조건이 부여된 칼럼들에 NULL값이 들어갈수없어서
-- 오류가 발생함.
INSERT INTO MEM_NOTNULL
VALUES (1, 'user01', 'pass01', '소연',NULL,NULL,NULL);
-- NOT NULL 제약조건이 걸리지 않은 칼럼명에는 NULL값이 들어갈 수 있음
2. UNIQUE 제약조건
칼럼에 중복값을 제한하는 제약조건
삽입/수정시 기존에 추가, 수정이 되지 않게 제약
-- 칼럼/ 테이블 레벨방식 둘다 가능
ex)
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) NOT NULL,
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30)
UNIQUE(MEM_ID) -- 테이블 레벨 방식
);
INSERT INTO MEM_UNIQUE
VALUES(1,'user02','pass01','thdus','ksy','010-1111-1111','thdusthdus@naver.com');
/*
UNIQUE 제약조건에 위배되었으므로 INSERT실패
어느컬럼에 어느 문제가 있는지 구체적으로 알려주지 않음
DDL.SYS_C007062 : 제약조건의 이름으로만 제약조건 위배를 알려준다.(스크립트 출력창 확인)
제약조건 부여시 직접 제약조건명(☆)을 지정해주지 않으면 시스템에서 임의의 제약조건명을 부여해줌.
*/
-- GENDER칼럼에 '남', '여'라는 값만 들어가게 하고 싶음
3. CHECK 제약조건
컬럼에 기록될 수 있는 값에 대한 조건을 설정할 수 있다.
예 ) 성별 '남' 혹은 '여' 만 들어오게끔 하고 싶다.
[표현법]
CHECK (조건식) --; 조건식이 TRUE인 경우에만 성립
ex)
CREATE TABLE MEM_CHECK(
MEM_NO NUMBER NOT NULL,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE NOT NULL,
UNIQUE(MEM_ID) --테이블 레벨 방식
);
INSERT INTO MEM_CHECK
VALUES(1,'user01','pass01','김소연','여',null,null,SYSDATE);
INSERT INTO MEM_CHECK
VALUES(1,'user02','pass01','김소연','연',null,null,SYSDATE);
-- ; CHECK 제약조건을 걸어주었기 때문에 성별값에 제약조건에서걸어준 '남', '여' 아닌 값인 '연'이 들어와서 오류뜸
--INSERT INTO MEM_CHECK
--VALUES(1,'user02','pass01','김소연',NULL,null,null,SYSDATE);
-- 추가적으로 NULL값을 못 들어오게 하고 싶다면 NOT NULL 제약조건도 같이 걸어주면 됨.
* DEFAULT 설정
특정 칼럼에 들어올 값에 대한 기본값 설정 가능(제약조건은 아님)
EX) 회원가입일 컬럼에 회원정보가 삽입된 순간의 시간을 기록하고싶다
=> DEFAULT 설정으로 SYSDATE를 넣어주면됨
ex) -- 회원가입을 항상 SYSDATE 로 받고 싶은경우
CREATE TABLE MEM_CHECK(
MEM_NO NUMBER NOT NULL,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
UNIQUE(MEM_ID) --테이블 레벨 방식
);
INSERT INTO MEM_CHECK
VALUES(1,'user01','pass01','김소연','여',null,null,NULL);
INSERT INTO MEM_CHECK(MEM_NO,MEM_ID,MEM_PWD,MEM_NAME)
VALUES(1,'user01','pass01','김소연');
SELECT *FROM MEM_CHECK; -- 테이블 확인
==> 출력 : 1 user01 pass01 김소연 (null) (null) (null) 23/06/06
-- 지정이 안된 칼럼에는 기본적으로 NULL값이 들어간다
-- 만약 DEFAULT값이 부여되어 있다면 NULL값이 아닌 DEFAULT값으로 들어가게 된다.
4. PRIMARY KEY(기본키) 제약조건
테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 칼럼에 부여하는 제약조건
-> 각 행들을 구분할 수 있는 식별자의 역할
예) 사번, 부서아이디, 직급코드, 학생번호, 클래스 번호 ...
=> 식별자의 조건 : 중복X , 값이 없어도 안됨(NOT NULL + UNIQUE)
주의사항 : 한 테이블당 한개의 칼럼만 지정가능 -- ; UNIQUE는여러개가능
ex 1)
CREATE TABLE MEM_PRIMARYKEY1(
MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
UNIQUE(MEM_ID) --테이블 레벨 방식
);
INSERT INTO MEM_PRIMARYKEY1
1번--VALUES(1,'user01','pass01','김소연',null,null,null,DEFAULT);
2번--VALUES(NULL,'user01','pass01','김소연',null,null,null,DEFAULT);
-- 1번) 1이 중복이라 UNIQUE제약조건에 위반, 2번) NULL값이 오면 안되므로 NOT NULL제약조건 위반
ex 2)
CREATE TABLE MEM_PRIMARYKEY2(
MEM_NO NUMBER CONSTRAINT MEM_PK2 PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
UNIQUE(MEM_ID), --테이블 레벨 방식
CONSTRAINT MEM_PK2 PRIMARY KEY(MEM_NO) -- 테이블레벨방식
);
CREATE TABLE MEM_PRIMARYKEY2(
MEM_NO NUMBER ,
MEM_ID VARCHAR2(20) NOT NULL, --;NOT NULL 뒤에 PRIMARY KEY 추가시 에러남
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
CONSTRAINT MEM_PK2 PRIMARY KEY(MEM_NO, MEM_ID) -- 테이블레벨방식
);
-- PRIMARY KEY가 한테이블에 2개이상 사용될수 없다. 단, 두 컬럼을 하나로 묶어서 하나의 PRIMARY KEY로는 설정 가능.
-- 두가지 컬럼을 묶어서 PRIMARY KEY로 설정했을경우 => 복합키 ★의외로 많이 사용됨★
INSERT INTO MEM_PRIMARYKEY2
VALUES(1,'user01','pass01','김소연',null,null,null,DEFAULT);
INSERT INTO MEM_PRIMARYKEY2
VALUES(1,'user02','pass01','김소연',null,null,null,DEFAULT);
INSERT INTO MEM_PRIMARYKEY2
VALUES(2,'user02','pass01','김소연',null,null,null,DEFAULT);
INSERT INTO MEM_PRIMARYKEY2
VALUES(2,'user02','pass01','김소연',null,null,null,DEFAULT);
-- 복합키의 경우 두 컬럼의 값이 완전히 중복되어야지만 제약조건에 위배된다.
INSERT INTO MEM_PRIMARYKEY2
VALUES(NULL,'user02','pass01','김소연',null,null,null,DEFAULT);
-- 복합키일경우 한 컬럼에 값이 NULL이면 제약조건에 위배된다.
5. FOREIGN KEY(외래키)
해당 컬럼에 다른 테이블에 존재하는 값만 들어와야하는 칼럼에 부여하는 제약조건
=> "다른 테이블(==부모테이블)을 참조한다" 라고 표현
즉, 참조된 다른 테이블(==부모테이블)이 제공하고 있는 값만 들어올 수 있다.
예) KH계정에서
EMPLOYEE테이블(자식테이블) <-------- DEPARTMENT테이블(부모테이블)
DEPT_CODE ------ DEPT_ID
=> DEPT_CODE에는 DEPT_ID에 존재하는 값들만 들어올수 있다.
=> FOREIGN KEY제약조건으로 다른 테이블과 관계를 형성할 수 있다.(JOIN)
[표현법] ★그냥 암기
> 컬럼레벨 방식
컬럼명 자료형 CONSTRAINT 제약조건명 REFERENCES 참조할테이블명(참조할칼럼명)
> 테이블레벨방식
CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할칼럼명)
참조할테이블 == 부모테이블
생략가능한부분 : CONSTRAINT 제약조건명, 참조할 칼럼명(테이블, 칼럼레벨 모두 생략가능)
=> 참조할 칼럼명이 생략되는 경우 자동적으로 참조할 테이블의 PRIMARY KEY에 해당하는 컬럼이 참조컬럼으로 지정됨
주의사항 : 참조할 칼럼의 타입(부모테이블칼럼), 외래키로 지정할 칼럼타입(자식테이블칼럼)이 같아야한다.
ex 1)SELECT MEM_ID, GRADE_NAME
FROM MEM
JOIN MEM_GRADE ON GRADE_ID = GRADE_CODE;
-- 문제) 부모테이블에서 데이터값이 삭제된다면?
-- MEM_GRADE테이블에서 GRADE_CODE가 G3인 데이터만 지워보기?
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G3';
-- 자식테이블에서 현재 G3 값을 사용하고 있기 때문에 삭제할 수 없음.
-- 현재 외래키 제약조건 부여시 삭제에 대한 옵션을 별도로 부여하지 않은 상태
-- => 기본값으로 삭제 제한 옵션이 걸려있음
/*
* 자식테이블 생성시 외래키 제약조건을 부여했을때
부모테이블의 데이터가 삭제되었을 때 자식테이블에서는 어떻게 처리할지를 옵션으로 정해둘수있다.
* FOREIGN KEY 삭제 옵션
- ON DELETE SET NULL : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 NULL로 바꾸겠다.
- ON DELETE CASCADE : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 같이 삭제하겠다.
- ON DELETE RESTRICTED : 삭제를 제한하겠다(기본옵션)
*/
ex 2)
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2),
-- GRADE_ID CHAR(2) REFERENCES MEM_GRADE, --외래키 제약조건 (컬럼레벨)
GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
UNIQUE(MEM_ID),
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL--외래키 제약조건 (테이블레벨)
);
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';
-- 문제없이 삭제잘되고, G1을 참조하던 자식 테이블의 GRADE_ID 값에 G1대신 NULL값이 들어갔다.
ex 3)
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2),
-- GRADE_ID CHAR(2) REFERENCES MEM_GRADE, --외래키 제약조건 (컬럼레벨)
GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
UNIQUE(MEM_ID),
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE --외래키 제약조건 (테이블레벨)
);
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G2';
-- 문제없이 잘 삭제되었고, 자식테이블의 GRADE_ID 가 G2인 행까지 함께 삭제해버림
ex 4) -- 조인문제
-- 전체 회원의 회원번호, 아이디, 비밀번호, 이름, 등급명을 오라클전용구문, ANSI 구문으로 나눠서 작성
-- ANSI
SELECT MEM_NO 회원번호, MEM_ID 아이디, MEM_PWD 비밀번호, MEM_NAME 이름, GRADE_ID 등급명
FROM MEM
JOIN MEM_GRADE ON GRADE_ID = GRADE_CODE;
-- 오라클
SELECT MEM_NO 회원번호, MEM_ID 아이디, MEM_PWD 비밀번호, MEM_NAME 이름, GRADE_ID 등급명
FROM MEM, MEM_GRADE
WHERE GARADE_ID = GRADE_CODE;
/*
외래키 제약조건이 걸려있지 않더라도 JOIN은 가능함. 단, 두 컬럼의 동일한 의미의 데이터가
담겨있어야 정상적으로 조회 가능함.
*/
* 제약조건 부여시 제약조건명도 지정하는 표기법
> 컬럼레벨방식
CREATE TABLE 테이블명(
컬럼명 자료형 제약조건1 제약조건2,
컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
...
);
> 테이블레벨방식
CREATE TABLE 테이블명(
컬럼명 자료형,
...
[CONSTRAINT 제약조건] 제약조건 (칼럼명)
);
=> 두방식 모두 CONSTRAINT 제약조건이름은 생략가능했었음(시스템이 랜덤한 이름을 부여해줌)
ex)
CREATE TABLE MEM_CON_NM(
MEM_NO NUMBER NOT NULL,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) CONSTRAINT MEM_NAME_NN NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID) --테이블 레벨 방식
);
INSERT INTO MEM_CON_NM
VALUES(1,'user01','pass01','김소연',NULL,NULL,NULL);
INSERT INTO MEM_CON_NM
VALUES(2,'user01','pass02','김소연2',NULL,NULL,NULL);
--어떤 컬럼에 어떤 종류의 제약조건인지 한번에 확인가능(오류메세지확인)
INSERT INTO MEM_CON_NM
VALUES(1,'user02','pass02',NULL,NULL,NULL,NULL);