무결성(CONSTRAINT) : COLUMN을 지정하는 성질(설정)
종류
Primary Key : 기본키. 중복을 허용하지 않는다. NULL을 허용하지 않는다. 예시) ID, 주민번호
Unique Key : 고유키. 중복을 허용하지 않는다. NULL을 허용한다. 예시) e-mail
Foreign Key : 외래키. JOIN(테이블과 테이블의 연결)이 목적. NULL을 허용한다.
외래키로 지정된 컬럼은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.
CHECK : 범위를 지정. 지정된 값 외에 사용할 수 없다. 중복을 허용한다. NULL을 허용한다.
NOT NULL : 중복을 허용한다. NULL을 허용하지 않는다.
NOT NULL : 데이터 삽입 시 NULL을 허용하지 않는다.
형식
CREATE TABLE 테이블명(
컬럼명 데이터형식(크기) NOT NULL,
);
참고
행 삽입 시 NOT NULL 지정 컬럼에 데이터를 삽입하지 않으면 경우 에러가 발생한다.
예시
-- 테이블 생성
CREATE TABLE TB_TEST08(
COL1 VARCHAR2(10) NOT NULL,
COL2 VARCHAR2(20)
);
-- 데이터 삽입
INSERT INTO TB_TEST08(COL1, COL2)
VALUES ('AAA','111');
INSERT INTO TB_TEST08(COL1)
VALUES ('BBB');
INSERT INTO TB_TEST08(COL2)
VALUES ('222'); -- 오류발생 COL1 NOT NULL
CASCADE CONSTRAINTS : 무결성 제약조건까지 모두 삭제
형식
DROP TABLE 테이블명
CASCADE CONSTRAINTS;
참조
예시처럼 똑같이 진행 시 이미 테이블이 생성되어 있어서 에러가 발생할 것입니다.
테이블 생성 시마다 아래 예시를 실행해주어야 에러가 발생하지 않습니다.
예시
DROP TABLE TB_TEST08
CASCADE CONSTRAINTS;
Primary Key(기본키) : 중복금지 + NOT NULL
형식
1. CREATE TABLE 테이블명(
컬럼명 데이터형식(크기) PRIMARY KEY
);
2. CREATE TABLE 테이블명(
컬럼명 데이터형식(크기) CONSTRAINT Key ID PRIMARY KEY
);
3. CREATE TABLE 테이블명(
컬럼명1 데이터형식(크기),
컬럼명2 데이터형식(크기)
CONSTRAINT Key ID PRIMARY KEY(컬럼명1, 컬럼명2)
);
참고
1 형식으로 만들 수 있지만, Key ID 지정하지 않았을 경우 지정한 PRIMARY KEY만 삭제를 하지 못하게 된다.
지정한 PRIMARY KEY 삭제 시 Key ID 필요!
3 형식으로 생성 시 2개 이상의 컬럼을 PRIMARY KEY로 지정할 수 있다.
행 삽입 시 PRIMARY KEY로 지정 컬럼에 데이터를 삽입하지 않으면 경우 에러가 발생한다.
행 삽입 시 PRIMARY KEY로 지정 컬럼에 데이터를 중복 삽입할 경우 에러가 발생한다.
예시
CREATE TABLE TB_TEST08(
-- PK_COL VARCHAR2(10) PRIMARY KEY -- 가능하지만
PK_COL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, -- PK_TEST_01 : Primary Key ID 지정
COL1 VARCHAR2(20),
COL2 VARCHAR2(20)
);
INSERT INTO TB_TEST08(PK_COL, COL1, COL2)
VALUES('AAA','aaa','111');
INSERT INTO TB_TEST08(PK_COL)
-- VALUES('AAA'); -- 중복 허용X , 에러발생.
VALUES('BBB');
-- INSERT INTO TB_TEST08(PK_COL, COL1, COL2)
-- VALUES('','aaa','111'); -- '' = NULL , 에러발생
-- 에러 only one primary key
/*
CREATE TABLE TB_TEST08(
PK_COL1 VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
PK_COL2 VARCHAR2(10) CONSTRAINT PK_TEST_02 PRIMARY KEY,
COL1 VARCHAR2(20),
COL2 VARCHAR2(20)
);
*/
-- 프라이머리키 2개 이상 생성 방법
CREATE TABLE TB_TEST08(
PK_COL VARCHAR2(10),
COL1 VARCHAR2(20),
COL2 VARCHAR2(20),
CONSTRAINT PK_TEST_01 PRIMARY KEY(PK_COL, COL1)
);
무결성이 없는 테이블 생성 후 무결성 변경하는 방법
형식
1. ALTER TABLE 테이블명
ADD PRIMARY KEY (컬럼명1, 컬럼명2..);
2. ALTER TABLE 테이블명
ADD CONSTRAINT Key ID명
PRIMARY KEY(컬럼명);
참조
진행 전 무결성이 없는 테이블이 생성되어 있어야 합니다.
예시
-- 무결성 없는 테이블 생성
CREATE TABLE TB_TEST08(
PK_COL1 VARCHAR2(10),
COL1 VARCHAR2(20),
COL2 VARCHAR2(20)
);
-- 테이블 만든 후 나중에 무결성 변경가능
-- 1형식
ALTER TABLE TB_TEST08
ADD PRIMARY KEY (PK_COL);
-- 2형식
ALTER TABLE TB_TEST08
ADD CONSTRAINT PK_TEST_01
PRIMARY KEY(PK_COL1);
무결성 삭제 방법
형식
1. ALTER TABLE 테이블명
DROP PRIMARY KEY;
2. ALTER TABLE 테이블명
DROP CONSTRAINT Key ID명 ;
참조
1 형식으로 키 추가를 했다면 1 형식으로만 삭제해야 한다.
1 형식은 지정한 Key ID값을 삭제할 수 없고, 전체 키값이 삭제된다.
2 형식으로 키 추가했을 경우 2가지 경우 다 삭제 가능하다
예시
-- 1형식
ALTER TABLE TB_TEST08 DROP PRIMARY KEY;
-- 2형식
ALTER TABLE TB_TEST08
DROP CONSTRAINT PK_TEST_01;
Unique(고유키) : 중복된 값은 입력 불가. NULL은 허용
형식
1. CREATE TABLE 테이블명(
컬럼명 데이터형식(크기) UNIQUE
);
2. CREATE TABLE 테이블명(
컬럼명 데이터 형식(크기) CONSTRAINT Key ID UNIQUE
);
참조
기본키와 마찬가지로 1 형식으로 만들 수 있지만, Key ID 지정하지 않았을 경우 지정한 UNIQUE KEY만 삭제를 하지 못하게 된다.
지정한 UNIQUE 삭제 시 Key ID 필요!
예시
CREATE TABLE TB_TEST08(
-- UK_COL VARCHAR2 UNIQUE -- 가능
UK_COL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
COL1 VARCHAR2(20),
COL2 VARCHAR2(20)
);
INSERT INTO TB_TEST08(UK_COL, COL1, COL2)
VALUES('','aaa','111'); -- ''가능하다 (프라이머리키와 차이점)
INSERT INTO TB_TEST08(COL1, COL2)
VALUES('aaa','111'); -- ''가능하다 (프라이머리키와 차이점)
FOREIGN KEY(외래키) : 대상 테이블에서 pk, uk로 설정되어 있어야 한다. NULL 값을 허용. 중복 허용
형식
CREATE TABLE 테이블명(
DEPARTMENT_ID VARCHAR2(10),
CONSTRAINT Key ID FOREIGN KEY(컬럼명)
REFERENCES 대상테이블명(대상테이블의 기본키(OR 고유키) 컬럼명)
);
참조
외래키 테이블 생성 전 대상테이블이 생성되어 있어야 한다.
외래키에 데이터를 넣기 전 대상테이블의 기본키에 데이터가 들어가 있어야 한다.
외래키의 크기는 대상테이블의 기본키(OR 고유키)보다 크기가 같거나 커야 한다.
대상테이블의 기본키와 외래키의 컬럼명을 달라도 된다.
외래키 데이터는 아무 값이나 넣으면 안 된다.(대상 테이블의 기본키의 데이터와 같아야 한다.)
예시
-- 대상 테이블 생성
CREATE TABLE TB_DEPT(
DEPARTMENT_ID VARCHAR2(10),
DEPARTMENT_NAME VARCHAR2(20),
LOCATION_ID NUMBER,
CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID) -- PRIMARY KEY이거나 Unique여야 한다.
);
-- 데이터 추가
INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('10', '기획부', '120');
INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('20', '관리부', '150');
INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('30', '개발부', '180');
-- 기준 테이블 생성
CREATE TABLE TB_EMP(
EMPNO VARCHAR2(10),
ENAME VARCHAR2(20),
DEPARTMENT_ID VARCHAR2(10), -- 외래키의 크기는 대상테이블의 기본키(OR 고유키)보다 크기가 같거나 커야 한다.
CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID)
REFERENCES TB_DEPT(DEPARTMENT_ID) -- 기본키와 외래키의 컬럼명을 달라도 된다.
); -- 이제 JOIN이 가능해 진다.
-- 데이터 추가
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('100', '일', '20'); -- DEPARTMENT_ID 아무값이나 넣으면 안된다.(대상 테이블의 기본키값과 같아야한다.)
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('101', '이', '10');
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('102', '삼', '30');
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('103', '사', ''); -- 외래키는 NULL값 허용한다.
-- INNER JOIN
SELECT e.empno, e.ename, d.department_id, d.department_name
FROM tb_emp e, tb_dept d
WHERE e.department_id = d.department_id;
CHECK : 범위 값을 설정하거나 지정된 값만 넣을 수 있다. NULL 허용, 중복 허용
형식
CREATE TABLE TB_CHECK(
컬럼명1 데이터형식(크기),
컬럼명2 데이터형식(크기),
CONSTRAINT Key ID ( 컬럼명1 IN('지정값1', '지정값2', '지정값3') ),
CONSTRAINT Key ID ( 컬럼명2 > 범위1 AND COL2 <= 범위2)
);
예시
-- 테이블 생성
CREATE TABLE TB_CHECK(
COL1 VARCHAR2(10),
COL2 VARCHAR2(20),
CONSTRAINT CHK_01 CHECK( COL1 IN('사과', '배', '바나나') ), -- 사과, 배, 바나나와 NULL
CONSTRAINT CHK_02 CHECK( COL2 > 0 AND COL2 <= 100) -- 1~100 숫자와 NULL
);
-- 데이터 삽입
INSERT INTO tb_check(COL1,COL2)
VALUES('사과', 50);
INSERT INTO tb_check(COL1,COL2)
VALUES('귤', 50); -- 불가능 에러
INSERT INTO tb_check(COL1,COL2)
VALUES('', 50);
INSERT INTO tb_check(COL1,COL2)
VALUES('', 101); -- 불가능 에러
