무결성 제약(constraint)조건 : Primary Key(PK), Check, Unique, Default, Foreign Key(
▤ 목차
✔ 무결성 제약조건
잘못된 자료의 입력을 막고자 다양한 입력 제한 조건을 줄 수 있다.
- Primary Key Constraint : PK 조건에 만족하는가?
- Foreign Key Constraint : FK 조건에 만족하는가?
- UNIQUE Constraint : 유니크한 Value인가?
- CHECK Constraint : 체크값 만족하는가?
- DEFAULT Constraint : Default 값을 부여하는 제약조건.
크게 5가지로 나눌 수 있다.
💻 코드로 보기
CREATE TABLE aa(bun INT(5), irum VARCHAR(5) NOT NULL, juso CHAR(50));
INSERT INTO demo VALUES(1,'tom', 'seoul');
INSERT INTO demo(bun,juso) VALUES('이', '서울');
irum의 조건을 보자. not null이다. 즉, null값을 허용하지 않는다.
이런 경우, 데이터의 무결성이 깨지기 때문에 error를 낸다.
✔ Primary Key Constraint : PK 조건에 만족하는가?
⌨ 형식
테이블의 유일성을 보장해 주는 키이다. 만약 같은 공간에 tom이 있다고 가정해 보자.
tom을 불렀다면 어떤 상황이 나올까? 서울에서 사는 tom과 제주에서 사는 tom 둘 다 대답할 것이다.
이런 경우를 대비해서 값을 구별하기 위한 값이 필요한데, 이런 값에 primary key를 붙여주는 것이다.
💻 코드로 보기
방법 1) 칼럼 레벨 - 각 칼럼 선언 시 제약조건 부여
# 테이블 생성
create table aa(bun int(5) primary key,irum char(10));
# 값 삽입
insert into aa values(1,'tom');
insert into aa values(1,'john'); -- err ( key 'PRIMARY' 오류)
결과적으로 해당 쿼리문은 err를 내고 수행되지 않는다.
방법 2) 테이블 레벨 - 칼럼을 모두 정의한 후 제약조건을 부여
CREATE TABLE aa(bun INT, irum CHAR(10), CONSTRAINT aa_bun_pk PRIMARY KEY(bun));
CONSTRAINT에서는 primary key의 이름을 부여할 수 있다.
👏 중요
방법 1에서는 제약 조건명을 프로그램에서 자동으로 만든다.
방법 2는 primary key 명을 지정할 수 있는데
실무에서는 제약조건명 지정을 보통 안 한다. 즉, 방법 1을 더 많이 사용한다.
확인하기 ( information_schema.TABLE_CONSTRAINTS )
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='aa';
✔ CHECK Constraint : 체크값에 만족하는가?
입력되는 자료의 특정 칼럼 값 검사한다.
💻 코드로 보기
CREATE TABLE aa(bun INT, irum CHAR(10), nai INT CHECK(nai >= 20));
INSERT INTO aa VALUES(1,'tom',23);
INSERT INTO aa VALUES(2, 'tom2',13); -- 제약 조건 에러
이미 만들어진 table에 제약 조건을 추가할 수 있다.
ALTER table aa ADD constraint CK_NAME CHECK(IRUM IN ('tom','james', 'oscar'));aa 테이블의 irum은 'tom', 'james', 'oscar'만 허용된다.
INSERT INTO aa VALUES(3,'james',33);
INSERT INTO aa VALUES(4,'paga',43); -- err결과적으로 4번 칼럼은 err를 일으키고 값이 입력되지 않는다.
👏 중요
프로그램단에서도 할 수 있고 데이터베이스 단에서도 할 수 있다.
특정 칼럼에 제약을 주어 들어오는 값의 검사를 하여 무결성을 지킬 수 있다.
✔ Foreign Key Constraint : FK 조건에 만족하는가?
참조키, 외래키라고 부른다. 테이블 간의 관계를 만들어주는 역할을 한다.
한 테이블을 다른 테이블과 연결해 주는 역할을 한다.
참조되는 테이블을 부모 테이블이라고 부르며 참조하는 테이블을 자식 테이블이라고 한다.
자식테이블이 있다면 자체적으로 삭제할 수 없다.
FK로 지정된 부모 테이블 column은 unique특성을 가져야 한다. (당연히 pk도 포함이다.)
💻 코드로 보기
CREATE TABLE jikwon(
bun INT primary KEY,
irum VARCHAR(10) NOT NULL,
buser CHAR(10)
);
INSERT INTO jikwon VALUES(1,'한송이','인사과');
INSERT INTO jikwon VALUES(2,'백송이','인사과');
INSERT INTO jikwon VALUES(3,'천송이','판매과');
SELECT * from jikwon;우선 테이블을 하나 만들고 데이터를 넣어줬다.
CREATE TABLE gajok(
CODE INT PRIMARY KEY,
NAME VARCHAR(10),
birth DATETIME,
jikwon_bun INT,
FOREIGN KEY(jikwon_bun) REFERENCES jikwon(bun));gajok 테이블을 만들어 직원 테이블의 pk와 연결해 준다.
INSERT INTO gajok VALUES(100,'백합','1977-07-17',1);
INSERT INTO gajok VALUES(101,'도민준',NOW(),3);
INSERT INTO gajok VALUES(102,'강나루','2000-07-17',2);
INSERT INTO gajok VALUES(103,'개나리','1999-07-17',1);
INSERT INTO gajok VALUES(104,'이진수','1999-02-12',7); -- err pk없음
SELECT * FROM gajok;
SELECT * from jikwon;데이터를 삽입해 준다.
fk로 연결되었으니 부모 클래스의 pk로 연결되어 있다.
가장 아래, insert 문을 주목하자.
fk이자 jikwon 테이블의 pk값이 없으니 err가 발생했다.
여기서부터 중요하다.
위에서 서술했듯, fk는 테이블의 관계를 형성하는 키이다.
부모 테이블이 된 jikwon테이블의 칼럼은 함부로 지울 수 없다.
자식테이블이 참조하고 있기 때문이다.
DELETE FROM jikwon WHERE bun =3; --err
만약 부모테이블의 특정칼럼을 지우고 싶다면?
DELETE FROM gajok WHERE CODE=101;
DELETE FROM jikwon WHERE bun=3;이렇게 부모 테이블의 특정 칼럼을 참조하고 있는 자식 테이블을 먼저 지우고 부모 칼럼을 지워야 한다.
DROP table jikwon; -- err 참조하는 테이블이 있기때문에
DELETE FROM gajok; -- gajok 테이블 모든 레코드 삭제
SELECT * FROM gajok;
위의 방식처럼 jikwon 테이블은 지울 수 없다.
gajok의 테이블을 모두 지우면 부모 테이블을 지울 수 있을까?
DROP table jikwon; -- err 직원 테이블을 참조하는 가족 테이블의 구조는 살아있기때문
DROP TABLE gajok;
DROP TABLE jikwon;
SHOW TABLES;err가 발생한다.
왜냐하면 자식 테이블인 gajok테이블의 구조 자체는 삭제되지 않았기 때문이다.
테이블 자체를 삭제하는 DROP구문을 사용해서 자식 테이블 자체를 없애준 후 jikwon테이블을 삭제해 주자.
👏 중요
on delete cascade 구문이 있지만 사용은 자제하자.
부모 테이블의 행을 삭제하면 참조하는 자식도 자동으로 삭제되는 구문이다.
데이터를 삭제하는 것도 조심해야 하는데, 같이 삭제된다면 더더욱 조심해야 한다.
위험한 코드라고 인식하고 꼭 필요한 테이블이 아니면 되도록 사용하지 말자.
✔ UNIQUE Constraint : 유니크한 Value인가?
입력되는 자료의 특정 칼럼 값 중복 불가
💻 코드로 보기
CREATE TABLE aa(bun INT, irum CHAR(10) UNIQUE);보통은 이름에 unique를 주진 않지만 예를 들어 이름에 unique를 부여한다면 중복을 허용하지 않는 것이다.
INSERT INTO aa VALUES(1,'james');
INSERT INTO aa VALUES(2,'tom');
INSERT INTO aa VALUES(3,'tom'); -- err 이름 중복
✔ DEFAULT Constraint : Default 값을 부여하는 제약조건
특정 칼럼에 초기값 부여을 부여 한다.
not null 방지가 목적이다.
💻 코드로 보기
CREATE TABLE aa(
bun INT PRIMARY KEY auto_increment,
irum CHAR(10),
juso CHAR(20) DEFAULT '역삼동');
INSERT INTO aa VALUES(1, 'james', '서초1동');
INSERT INTO aa(irum,juso) VALUES('john', '서초1동');
INSERT INTO aa(irum) VALUES('tom');
INSERT INTO aa VALUES(6, 'oscar', '서초3동');
INSERT INTO aa(irum) VALUES('page');
null값은 '역삼동'으로 들어가는 것을 알 수 있다.
👏 중요
`auto_increment`는 int type의 pk칼럼에서 자동으로 번호가 증가되는 키워드이다.
하지만, 벤더사마다 용어가 다르다.
번호 증가가 필요하면 프로그램에서 짜자. 실무에 들어가서 사용하는 DB 벤더가 바뀌면
하나씩 바꿔주는 불상사가 발생할 수 있다.
😊정리
나중에 정리하겠지만 unique와 PK 에는 index가 부여된다.
pk에는 unique의 특성이 있다. not null 조건이 붙는 것을 잊지 말자.
foreign key의 대상이 다른 테이블의 pk 값임을 기억하자.
다른 테이블을 참조하고 있는 테이블이다.
자신을 참조하는 테이블이 있으면 삭제할 수 없다.
즉, 부모 테이블은 자식테이블이 참조하고 있는 이상 삭제할 수 없다.
자식테이블이 삭제된 다음, 부모테이블이 삭제가 가능하다.
(되도록이면, on delete cascade문은 사용하지 않도록 하자.)
-- foreign key(외부키, 참조키) : 다른 테이블의 칼럼 값을 참조
-- FOREIGN 키의 대상은 pk이어야 한다.
-- on delete cascade: 부모 테이블의 행이 삭제되는 경우 자식테이블 함께 삭제
CREATE TABLE jikwon(
bun INT primary KEY,
irum VARCHAR(10) NOT NULL,
buser CHAR(10));
INSERT INTO jikwon VALUES(1,'한송이','인사과');
INSERT INTO jikwon VALUES(2,'백송이','인사과');
INSERT INTO jikwon VALUES(3,'천송이','판매과');
SELECT * from jikwon;
CREATE TABLE gajok(
CODE INT PRIMARY KEY,
NAME VARCHAR(10),
birth DATETIME,
jikwon_bun INT,
FOREIGN KEY(jikwon_bun) REFERENCES jikwon(bun));
-- on delete cascade 적어주면 부모까지 같이 지워짐 (위험한 명령이다. 프로그램으로 짜라)
DESC gajok;
INSERT INTO gajok VALUES(100,'백합','1977-07-17',1);
INSERT INTO gajok VALUES(101,'도민준',NOW(),3);
INSERT INTO gajok VALUES(102,'강나루','2000-07-17',2);
INSERT INTO gajok VALUES(103,'개나리','1999-07-17',1);
INSERT INTO gajok VALUES(104,'이진수','1999-02-12',7); -- err pk없음
SELECT * FROM gajok;
SELECT * from jikwon;
DELETE FROM jikwon WHERE bun =3; -- 바로 못지운다. gajok이 참조하고 있기때문에
-- 참조 테이블을 먼저 지워야한다.
DELETE FROM gajok WHERE CODE=101;
DELETE FROM jikwon WHERE bun=3;
DROP table jikwon; -- err 참조하는 테이블이 있기때문에
DELETE FROM gajok; -- gavok 테이블 모든 레토드 삭제
SELECT * FROM gajok;
DROP table jikwon; -- err 직원 테이블을 참조하는 가족 테이블의 구조는 살아있기때문
DROP TABLE gajok;
DROP TABLE jikwon;
SHOW TABLES;