목차
RDBMS란?
식별 / 비식별관계란?
참조제약조건 (외래키)
1) 테이블 생성시 추가
2) 테이블 생성후 추가
연계참조 무결성 제약조건
1) 연계참조 무결성 예시
2) 옵션: ON DELETE CASCADE
UNIQUE 제약조건
CHECK 제약조건
1) 사용하지 않는 이유
🧩 RDBMS (Relation Data Base Management System) (관계형 데이터 베이스)
- R은 Relation의 약자이다.
- 테이블 간에 부모-자식 관계를 설정할 수 있다.
- RDB를 생성/수정/관리할 수 있는 소프트웨어이다.
- 관계형 모델을 기반으로 하는 DBMS 유형이다.
- RDBMS 테이블은 서로 연관되어 있어 일반 DBMS보다 효율적으로 데이터를 저장, 구성, 관리할 수 있다.
🧩 식별관계 / 비식별관계
- 부모자식 관계란 부모의 기본키를 자식이 가져다 쓰는 관계이다.
- 부모의 기본키를 나의 기본키로 사용하는 경우를 식별관계라 하고, 그렇지 않을 경우 비식별관계라고 한다.
식별관계 예)
iden_table테이블의 user_id는 primarykey인 동시에 parent_table테이블의 user_id( parent_table의 기본키) 를 외래키로 가져다 쓴다.
CREATE TABLE iden_table(
user_id VARCHAR(30) PRIMARY KEY,
FOREIGN KEY(user_id) REFERENCES parent_table(user_id)
);🧩 참조 제약 조건 (외래키 제약조건)
- foreign key 제약조건 설정한 필드를 외래키라 한다.
- 하나의 테이블(자식테이블)이 다른 테이블(부모테이블)에 의존하는 형태
- 외래키가 설정된 테이블에 레코드 입력시 기준이 되는 테이블 내용 참조해 레코드가 입력된다.
- 참조되는 테이블은 반드시 primary key나 unique가 설정되어있어야 한다.
1) 테이블 생성 시 추가
CREATE TABLE [테이블명](
FOREIGN KEY ([컬럼명]) REFERENCES [부모테이블명]([컬럼명])
);
사용해보기
-- > 부모테이블
CREATE TABLE parent_table(
user_id VARCHAR(30) PRIMARY KEY,
user_name VARCHAR(20)
);
-- > FOREIGN KEY 사용할 자식테이블
CREATE TABLE child_table(
order_id INT(10),
user_id VARCHAR(30),
FOREIGN KEY (user_id) REFERENCES parent_table(user_id)
);
2) 테이블 생성 후 추가
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건종류](적용필드) REFERENCERS [가져올테이블](가져올컬럼)
사용해보기
-- child_table 테이블의 user_id 컬럼을 parent_table의 user_id(primary key)가져와 외래키로 만들어라
ALTER TABLE child_table ADD CONSTRAINT FOREIGN KEY(user_id) REFERENCES parent_table(user_id);
결과 : 제약조건확인 sql로 확인해보기
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME ='child_table';
🧩 연계 참조 무결성 제약조건
- 부모자식간에 논리적으로 앞뒤 말이 맞아야 한다.
연계참조 무결성이란?
↓ 부모테이블과 자식테이블에 아래 데이터가 있다고 가정할때,
↓ 부모한테 없는 값을 넣어보자
INSERT INTO child_table VALUES(4,'user04','의류',6000,1);
↓ 아래에 해당하는 에러가 뜨는걸 볼 수 있다.
↓ 해당 에러가 뜨는 이유는
- 연계참조 무결성이 깨졌다.
- 부모테이블의 기본키에 user04가 없는데 자식이 그것을 쓰려고 했기 때문이다. --> 부모없는 자식을 만들려고 한것
↓ 그럼 부모테이블 테이터를 지워보자!
DELETE FROM parent_table WHERE user_id = 'user01';
↓ 이 경우에도 에러가 뜬다.
이는 연계참조 무결성을 해치지않고 삭제하기 위해서는 자식이 먼저 삭제되고, 이후 부모가 삭제되어야하기 때문이다.
이 과정을 자동으로 수행해주는 ON DELETE CASCADE 옵션이 있다.
🧩 연계 참조 무결성 제약조건 옵션 : ON DELETE CASCADE
- 부모테이블에 대한 삭제를 허용한다.
- 부모테이블 값이 삭제되면 연쇄적으로 자식 테이블 값 또한 삭제된다.
CREATE TABLE [테이블명](
FOREIGN KEY [(컬럼명)] REFERENCES [부모테이블명][(컬럼명)] ON DELETE CASCADE
);
사용해보기
-- 부모테이블
CREATE TABLE supplier(
sup_id INT(10) PRIMARY KEY,
sup_name VARCHAR(50) NOT NULL,
phone VARCHAR(12)
);
-- 자식테이블
CREATE TABLE products(
pd_id INT(10) PRIMARY KEY,
sup_id INT(10),
price INT(10),
FOREIGN KEY (sup_id) REFERENCES supplier(sup_id) ON DELETE CASCADE
);
-- 부모테이블 값넣기
INSERT INTO supplier VALUES (1,'김철수','01012341234');
INSERT INTO supplier VALUES (2,'이철수','01012341234');
INSERT INTO supplier VALUES (3,'박철수','01012341234');
-- 자식테이블 값넣기
INSERT INTO products VALUES (1111,1,6000);
INSERT INTO products VALUES (1112,2,6000);
INSERT INTO products VALUES (1113,3,6000);DELETE FROM supplier WHERE sup_id = 1;
결과 : 에러없이 삭제가 부모테이블과 자식테이블 모두에서 sup_id = 1에대한 데이터가 삭제된다.
🧩 UNIQUE 제약조건
- 중복을 허용하지않는 제약조건이다.
- 테이블에 다수 존재할 수 있다.
- unique제약조건에 not null까지 된다면 = PK
- 후보키 = 기본키가 될 수 있는 조건을 갖을 수 있으므로...
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건종류](컬럼명);
사용해보기
ALTER TABLE supplier ADD CONSTRAINT UNIQUE(sup_name);
결과
+) unique제약조건이 걸린 sup_name에 이미 '박철수'라는 데이터가 있을때,
↓ '박철수'라는 이름을 또 넣을 경우
INSERT INTO supplier VALUES (4,'박철수','01012341234');
↓ Duplicate entry 에러가 뜬다. => 이것이 중복을 허용하지 않는 unique 제약조건이다.
🧩 CHECK 제약조건
- 조건이 맞지 않으면 받지 않는 제약조건
- 주의사항 : 조건은 기존 데이터가 만족해야할 조건을 걸어야 한다.
ALTER TABLE [테이블명] ADD CONSTRAINT CHECK(조건);
사용해보기
↓ products테이블에 이미 price에 대하여 6000원 값들이 있을 경우
↓ price가 5000원 미만인 경우만 받겠다는 조건을 걸 경우
ALTER TABLE products ADD CONSTRAINT CHECK(price = 5000);
↓ 기존데이터에 6000원 값들이 있기때문에 에러 SQL Error [3819] [HY000]: (conn=15) 발생
↓ 기존값 6000원을 만족하는 price가 6000원인 경우만 받겠다는 조건을 걸어보자
ALTER table products add constraint ck_price check(price <= 6000);
↓ 에러없이 조건이 걸렸다.
↓ 이제 price에는 6000원 보다 클 경우 들어가지 않는다.
INSERT INTO products VALUES (1118,2,8000);
check 제약조건은 잘 사용하지 않는다. 그 이유는?
- 조건의 유연성이 떨어진다.
- 조건 변경시 DDL을 수정해야한다.
- 어떤 조건이 걸려있는지 파악하기 쉽지 않다.
