230321 DB_CONSTRAINT

Myung A Lee·2023년 3월 21일
0

DB

목록 보기
6/15
post-thumbnail

제약조건 (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 [제약조건 타입](적용 컬럼 조건);

0개의 댓글