제약조건 (CONSTRAINT)
제약 조건은 컬럼에 데이터를 입력 할 때 특정한 조건을 만족시켜야 할 경우에 무결성을 유지하기 위해 사용한다. 만약 입력 데이터가 제약에 맞지 않다면 자료 입력이 거부되면서 오류가 발생한다.
무결성
무결성이란 데이터베이스에 저장된 데이터 값과 그것이 표현하는 현실 세계의 실제값이 일치하는 정확성을 의미한다
- NULL 무결성 : 릴레이션의 특정속성 값이 Null이 될 수 없도록 하는 규정
- 고유 무결성 : 릴레이션의 특정 속성에 대해서 각 튜플이 갖는 값들이 서로 달라야 한다는 규정
- 참조 무결성 : 외래키 값은 Null이거나 참조 릴레이션의 기본키 값과 동일해야 한다는 규정 즉 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다는 규정
- 도메인 무결성 : 특정 속성의 값이, 그 속성이 정의된 도메인에 속한 값이어야 한다는 규정
- 키 무결성 : 하나의 테이블에는 적어도 하나의 키가 존재해야 한다는 규정
1. 기본키 제약 조건 (Primary Key)
- 중복과 NULL 을 허용하지 않는다.
- [ERROR] Duplicate entry '1' for key 'PRIMARY' : pk 중복 불가- Field 'first_col' doesn't have a default value : NULL 불가
- 테이블에 종속적이다. 즉 테이블이 사라지면 같이 사라진다.
- 종속적 : 속해있다. 포함되어있다. 다른 곳에서는 사용이 불가능하다.
- 하나 이상의 컬럼으로 구성된다. (16개까지 가능, 하지만 하나의 컬럼으로 구성하는 것을 권장)
1) 기본키(Primary Key) 생성 방법
(1) 테이블 생성시 함께 생성
- create table pk_test (first_col int(3) primary key, second_col varchar(4) );
(2) 기본 테이블에 추가
- ALTER TABLE[테이블명] ADD CONSTRAINT [제약조건 종류](대상컬럼);
- alter table employees add constraint primary key (emp_no);
- alter table employees add primary key (emp_no);
UPSERT
- 중복을 허용하지 않는 PK의 속성을 이용하여 중복되는 PK가 없다면 INSERT, 있다면 UPDATE 하는 것을 UPSERT라고 명칭 한다. (존재하는 명령어 X)
- INSERT INTO [테이블명] ([pk 컬럼명]) values ([pk 속성값]) on duplicate key update [대상컬럼] = [수정할 내용];
(3) 복합키 (한개 이상의 컬럼으로 키를 생성 하는 것)
- 단, 복합키에 추가되는 컬럼이 외래키일 경우 update나 insert에 제한을 줄 수 있다.
create table pk_two_test ( first_col int(5), second_col varchar(10), third_col date, primary key(first_col,second_col) );
desc pk_two_test ;
2. NOT NULL (NULL 을 허용하지 않음)
- NOT NULL은 컬럼의 속성으로 취급
1) NOT NULL 적용 방법
(1) 테이블 생성시 함께 적용
- create table pk_two_test( first_col int(5), second_col varchar(10), third_col date not null, primary key(first_col,second_col));
(2) 컬럼의 속성을 수정(UPDATE)하는 것
- 컬럼의 속성으로 취급되므로 컬럼의 속성을 수정하는 방법으로 사용가능
- ALTER TABLE [테이블명] MODIFY COLUMN [컬럼명][테이터타입](사이즈) NOT NULL
-ex) alter table pk_test modify column second_col varchar(4) not null;만약 NOT NULL로 제약조건을 설정하려는 컬럼에 이미 NUll이 존재한다면
delete from pk_test; -- 이미 null이 존재하는 데이터를 삭제 후 적용한다.
3. 참조 제약조건 - 외래키 (FOREIGN KEY)
다른 외부(부모) 테이블의 PK를 가져와 사용(참조)한다.
(1) 테이블 생성시 함께 생성
- 먼저 부모 테이블을 만든다.
- create table parent_table(
user_id varchar(30) primary key
,user_name varchar(20)
,user_phone varchar(20)
,user_addr varchar(20)
); - 자식 테이블을 만든다. (비식별 관계)
- create table child_table(
order_id int(10)
,user_id varchar(30)
,product_name varchar(20)
,price int(10)
,qty int(5)
,foreign key(user_id) references parent_table(user_id)
); - 자식 테이블을 만든다. (식별 관계)
- create table iden_table(
user_id varchar(30) primary key
,user_number varchar(20)
,foreign key(user_id) references parent_table(user_id)
);
(2) 기본 테이블에 추가
- ALTER TABLE [테이블명] ADD CONSTRAINT 제약조건 타입 references 참조테이블
-ex) alter table child_table add constraint foreign key(user_id) references parent_table(user_id);
식별 VS 비 식별 관계
- 식별 관계 : 부모의 primary key 를 자식 이 primary key 로 사용, ERD에서 실선으로 표시
- 비식별 관계 : 부모의 primary key 를 자식 이 primary key 로 사용, ERD에서 점선으로 표시
- 엔터티 관계도의 예시 (ER Diagram : 엔티티 관계도)
4. 연계 참조 무결성 제약조건
- 부모-자식간(연계참조)에는 논리적으로 말이 되게 만들어 주는 (무결성) 제약 조건
- 예제
-부모 테이블
create table supplier( supplier_id int(10) primary key ,supplier_name varchar(50) not null ,phone varchar(12));
-부모 테이블 데이터
insert into supplier (supplierid, supplier_name, phone) values(1,'김현수','02-123-1234');
insert into supplier (supplier_id, supplier_name, phone) values(2,'홍길동','032-568-0078');
insert into supplier (supplier_id, supplier_name, phone) values(3,'박영수','042-323-3234');
delete from supplier where supplier_id = 1;_
// 자식이 존재하는 부모가 지워지면 무결성이 위배되므로 데이터를 맘대로 지울 수 없다.
단, 자식테이블을 생성할 때 다음과 같이 on delete cascade를 사용하면 지울 수 있다.
foreign key(supplier_id) references supplier(supplier_id) on delete cascade
-자식 테이블
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));
-자식테이블 데이터
insert into products (productid, supplier_id, product_price) values(1111,1,6000);
insert into products values(1112,2,7000);
insert into products values(1113,3,8000);
insert into products values(1114,4,9000);_
// '4'는 부모 테이블에는 없는 값이다. 그러므로 데이터를 넣는 것이 불가능하다.
5. Unique 제약 조건
- Unique는 중복을 허용하지 않지만 NULL값을 허용하는 Key로 후보키라고 부른다.
- 테이블에 여러개 둘 수 있다.
- ALTER TABLE [테이블명] ADD CONSTRAINT 제약조건 타입;
6. Check 제약 조건
- 다음 두가지 이유로 요즘은 잘 사용하지 않는다.
-제약조건에 대한 표시가 없어 조건을 알아내기도 어렵다.
-조건이 바뀌었을 경우 제약조건을 테이블 삭제후 다시 생성 해 추가해 주어야 한다는 문제가 있다. - 조건에 맞지 않으면 데이터를 받지 않는다.
- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건 타입](적용 컬럼 조건);
