SQL - 제약조건
*제약 조건
컬럼에 데이터를 넣을 경우 특정한 조건을 만족시켜야 할 때 사용한다.
*기본키 제약 조건
중복 허용하지 않고, null도 허용하지 않는다.
primary key(pk)
테이블 종속(하나의 테이블에 하나의 기본키)
테이블 생성시 pk도 함께 생성(많이 사용)
테이블 생성 시 컬럼 사이즈 뒤에 primary key 키워드를 적는다.
혹은 primary key(컬럼)으로 정한다.
*이미 만들어진 테이블에 제약조건 추가
alter table [테이블 명] add constraint [제약조건 타입](적용 컬럼);
-> constraint는 생략 가능
*upsert 사용
기본 키가 정해졌을 경우 값을 insert 했을 때 키가 중복된다면 update를 실행한다.
*복합키
여러 개의 컬럼을 조합하여 하나의 키로 만드는 것
16개까지 조합 가능
외래키를 이용해서 복합키를 만드는 경우 update나 insert에 제한을 주므로 사용하지 않는다.
*not null
key 보다는 컬럼의 속성으로 취급한다.
따라서 add costraint가 아닌 modify로 변경한다.
alter table [테이블 명] modify [컬럼 명][데이터타입]([사이즈]) not null;
*제약 조건 확인
not null은 컬럼 속성 취급을 받기 때문에 검색되지 않는다.
select * from information_schema.TABLE_CONSTRAINT로 확인 가능하다.
*제약 조건 삭제
alter table [테이블 명] drop [제약조건 종류];
-- 1. 기본키 제약 조건 : 중복 허용하지 않고, NULL도 허용하지 않는다.
-- PRIMARY KEY(PK)
-- 테이블 종속(하나의 테이블에 하나의 기본키)
-- 기본키는 하나 이상의 컬럼으로 구성된다.
-- 테이블 생성시 PK도 함께 생성(가장 많이 사용)
create table pk_test(
first_col int(3) primary key,
second_col varchar(4)
);
-- 이미 만들어진 테이블에 추가
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건 타입](적용 컬럼);
alter table employees add constraint primary key (emp_no);
alter table employees add primary key (emp_no);
select * from employees;
-- SQL Error [1062] [23000]: (conn=28) Duplicate entry '112' for key 'PRIMARY'
insert into employees (
emp_no,
first_name,
family_name,
email,
mobile,
salary,
depart_no,
commission
) values (
112,
'철수',
'홍',
'asd123@email.com',
'01012345678',
9000000,
'dev001',
90
);
-- 키가 중복되면 에러가 나는데 UPSERT를 사용하면 키가 중복될 경우 뒤에 있는 UPDATE를 실행
insert into employees (emp_no, first_name, family_name, email, mobile, salary)
values (112, '태근', '김', 'email@naver.com', 01011112222, 7000000)
on duplicate key update first_name='태곤', family_name='박';
-- 복합키(여러 개의 컬럼을 조합하여 하나의 키로 만드는 것)
-- 16개까지 조합 가능
-- 외래키를 이용해서 복합키를 만드는 경우가 있는데 UPDATE나 INSERT에 제한을 주므로 사용하지 않는 것이 좋다.
create table pk_two_table (
first_col int(5),
second_col varchar(10),
third_col date,
primary key(first_col, second_col)
);
-- 2. NOT NULL
-- KEY보다는 컬럼의 속성으로 취급한다.
-- ALTER TABLE [테이블 명] MODIFY [컬럼 명][데이터타입]([사이즈]) NOT NULL;
alter table pk_test modify second_col varchar(4) not null;
desc pk_test;
-- 제약 조건 확인
-- NOT NULL은 컬럼 속성 취급을 받기 때문에 제약조건에서 검색되지 않는다.
-- database.table
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'pk_test';
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'pk_two_table';
-- 3. 제약 조건 삭제
-- ALTER TABLE [테이블 명] DROP [제약조건 종류];
alter table pk_two_table drop primary key;*RDBMS
관계형 데이터베이스
부모-자식 관계를 설정할 수 있다.
*참조 제약 조건
foreign key(외래키) : 다른 테이블의 기본키를 가져와서 사용하는 것
부모 테이블에 기본키가 존재해야 한다.
테이블을 생성하면서 설정하거나 이미 만들어진 테이블에도 add constraint를 통해 설정 가능하다.
*relation
실선 : 부모의 기본키를 자식이 기본키로 사용할 때 (식별관계)
점선 : 부모의 기본키를 자식이 기본키로 사용하지 않을 때 (비식별관계)
*연계 참조 무결성 제약조건
부모-자식 관계에서 논리적으로 맞는지 확인하는 것
무결성이란? 데이터의 정확성, 일관성, 유효성이 유지되는 것
부모가 지워지기 전에 자식이 먼저 지워져야 한다.
원래는 부모를 먼저 지울 수 없지만 on delete cascade를 통해 부모를 삭제하면 자식도 같이 삭제할 수 있다.
부모의 기본키에 없는 값을 자식에서 넣을 수가 없다.
*유니크 제약조건
중복을 허용하지 않는다. (null은 허용한다.)
유니크 + not null = 기본키
테이블에 다수 존재한다.
유니크 제약조건 또한 add contraint로 설정가능하다.
*check 제약 조건
조건에 맞지 않으면 받지 않는다.
미리 입력 값의 범위나 조건을 지정한다.
유지보수가 어렵기에 거의 쓰이지 않는다.
-- 1. 참조 제약 조건
-- FOREIGN KEY(외래키) - 다른 테이블의 기본키를 가져와 사용하는 것
-- 부모 테이블(기본키가 존재해야 한다.)
create table parent_table(
userId varchar(30) primary key,
userName varchar(20),
userPhone varchar(20),
userAddr varchar(100)
);
desc parent_table;
-- 자식 테이블
-- 1) 테이블을 만들면서 외래키 지정
create table child_table(
orderId int(10),
userId varchar(30),
productName varchar(20),
price int(10),
qty int(5)
-- foreign key(userId) references parent_table(userId)
);
desc child_table;
-- 2) 테이블이 만들어진 다음 키 설정
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건타입]([적용컬럼]) REFERENCES [부모테이블 명]([참조컬럼]);
ALTER TABLE child_table ADD CONSTRAINT foreign key(userId) REFERENCES parent_table(userId);
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'child_table';
-- 식별관계 만들기 (부모의 기본키를 내 기본키로 사용)
create table iden_table(
userId varchar(30) primary key,
foreign key(userId) references parent_table(userId)
);
-- 2. 연계 참조 무결성 제약조건
-- 부모 자식 관계 간에 논리적으로 맞는지?
-- 무결성 : 데이터의 정확성, 일관성, 유효성이 유지되는 것
-- 부모가 지워지기 전 자식이 먼저 지워져야 한다.
-- ON DELETE CASCADE : 부모를 지우면 자식을 자동으로 지워준다.
-- 부모 테이블
create table supplier(
supplier_id int(10) primary key,
supplier_name varchar(50) not null,
phone varchar(12)
);
desc supplier;
-- 자식 테이블
create table products(
product_id int(10) primary key,
supplier_id int(10),
product_price int(10),
foreign key(supplier_id) references supplier(supplier_id) on delete cascade
);
-- 부모 테이블에 값 입력
insert into supplier values (1,'김철수','01012341234');
insert into supplier values (2,'홍길동','01011111111');
insert into supplier values (3,'박영수','01022222222');
select * from supplier;
-- 자식 테이블에 값 입력
insert into products values (1111,1,6000);
insert into products values (1112,2,7000);
insert into products values (1113,3,8000);
-- Cannot add or update a child row: a foreign key constraint fails
-- 4는 부모(supplier)의 기본키에 없는 값이므로 넣을 수 없다.
insert into products values (1114,4,9000);
select * from products;
-- 부모 테이블의 값을 지우기
-- 원래는 자식 값이 남아있어서 부모를 지울 수 없지만
-- on delete cascade에 의해 부모를 지우면 자식도 지워진다.
delete from supplier where supplier_id = 3;
-- 부모 테이블을 지울 수 없다 (일부 DB에서는 특정 설정에 의해 가능하긴 하다.)
drop table supplier;
-- 3. 유니크(Unique) 제약조건
-- 중복을 허용하지 않는다. (NULL은 허용한다.)
-- Unique + NOT NULL = PRIMARY KEY
-- 테이블에 다수 존재한다.
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건타입]([적용컬럼]);
alter table supplier add constraint unique(supplier_name);
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'supplier';
-- 이름이 중복되는 값을 넣어보자
-- Duplicate entry '홍길동' for key 'supplier_name'
insert into supplier values (3,'홍길동','01054546565');
-- 4. check 제약 조건(조건에 맞지 않으면 받지 않는다.) - 유지보수가 어렵기에 거의 쓰이지 않는다.
-- 미리 입력 값의 범위나 조건을 지정한다. (예:product_price를 5000~10000 사이만 받는다.)
ALTER TABLE products ADD CONSTRAINT check(product_price between 5000 and 10000);
-- 정상 데이터
insert into products values(1113,2,9000);
-- 비정상 데이터(제약 조건에 걸린다.)
insert into products values(1114,1,18000);
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'products';