[SQL] 제약조건 (CONSTRAINT)

데이터 무결성을 지키기 위해 제한하는 조건

반응형

PRIMARY KEY (기본키)

데이터를 구분할 수 있는 식별자를 기본 키라고 부른다. 예를 들어, 회원 테이블의 아이디, 학생 테이블의 학번, 직원 테이블의 사번 등이 해당된다. 기본 키는 테이블 당 하나만 가질 수 있고, 입력되는 값은 중복될 수 없고, NULL 값도 올 수 없다.

그리고 대부분의 테이블은 기본 키를 가져야 한다. 기본 키 없이도 테이블 구성은 가능하지만, 기본 키를 설정해야 중복된 데이터가 입력되지 않는다. 예를 들어 회원 테이블의 아이디가 기본 키가 되면 아이디가 중복될 수 없다. 기본 키로 생성한 것은 클러스터형 인덱스가 자동으로 생성된다.

 

 

1) CREATE TABLE에서 설정하는 PK 제약조건

USE db_name;
CREATE TABLE table_name
( id    CHAR(8) NOT NULL PRIMARY KEY,   -- 열 이름 뒤에 PRIMARY KEY를 붙여주면 기본 키로 설정된다.
  name  VARCHAR(10) NOT NULL );

USE db_name;
CREATE TABLE table_name
( id    CHAR(8) NOT NULL,
  name  VARCHAR(10) NOT NULL,
  [CONSTRAINT] PRIMARY KEY (id) );   -- 테이블 제일 마지막에 PRIMARY KEY(column_name)을 붙여준다.

 

 

2) ALTER TABLE에서 설정하는 PK 제약조건

USE db_name;
CREATE TABLE table_name
( id    CHAR(8) NOT NULL,
  name  VARCHAR(10) NOT NULL );

ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (id)
-- ALTER TABLE에서 ADD CONSTRAINT PRIMARY KEY (column_name)을 추가하면 된다.

 

 

+) PK에 이름 지정하는 방법

CREATE TABLE( CONSTRAINT PRIMARY KEY pk_name (column_name) )
-- PRIMARY KEY와 (column_name) 사이에 이름을 붙여주면 된다.

 

 

 

 

 

FOREIGN KEY (외래키)

외래 키는 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다. 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다. 또한, 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키로 설정되어 있어야 한다.

 

기준 테이블 - 기본 키 & 참조 테이블 - 외래 키

 

 

1) CREATE TABLE에서 설정하는 FK 제약조건

FOREIGN KEY(참조 테이블의 컬럼명) REFERENCES 기준 테이블(컬럼명)

USE db_name;
CREATE TABLE table_name
( id    CHAR(8) NOT NULL PRIMARY KEY,   
  name  VARCHAR(10) NOT NULL );
CREATE TABLE table_name_2
( id2   CHAR(8) NOT NULL,   
  pass  VARCHAR(10) NOT NULL,
  FOREIGN KEY(id2) REFERENCES table_name(id) );
-- CREATE TABLE 끝에 FOREIGN KEY 설정
-- 형식 : FOREIGN KEY(column_name) REFERENCES 기준테이블(column_name)

 

 

2) ALTER TABLE에서 설정하는 FK 제약조건

FOREIGN KEY(참조 테이블의 컬럼명) REFERENCES 기준 테이블(컬럼명)

 

USE db_name;
CREATE TABLE table_name
( id    CHAR(8) NOT NULL,
  name  VARCHAR(10) NOT NULL);

ALTER TABLE table_name ADD CONSTRAINT FOREIGN KEY(id) REFERENCES 기준테이블명(column)
-- ALTER TABLE에서 ADD CONSTRAINT 뒤에 추가하면 된다.

 

기본 키-외래 키 관계가 설정되면 기준 테이블의 열은 변경되거나 삭제되지 않는다. 하지만, 참조 무결성 제약을 이용하여 기준 테이블의 열 이름이 변결될 때 참조 테이블의 열 이름이 자동으로 변경되게 할 수 있다.

 

FK 제약 조건 뒤에 ON UPDATE CASCADE ON DELETE CASCADE 를 붙여주면 된다.

USE db_name;
CREATE TABLE table_name
( id    CHAR(8) NOT NULL,
  name  VARCHAR(10) NOT NULL);

ALTER TABLE table_name 
    ADD CONSTRAINT FOREIGN KEY(id) REFERENCES 기준테이블명(column)
    ON UPDATE CASCADE    -- 기준 테이블의 데이터가 변경되면 참조 테이블의 데이터도 변경
    ON DELETE CASCADE;   -- 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제

 

 

 

 

 

UNIQUE (고유키)

고유 키는 중복되지 않는 유일한 값을 입력해야 하는 조건이다. 기본 키와 유사하지만, 고유 키는 NULL 값을 허용하며, NULL 값은 여러 개가 입력되어도 된다. 또한 고유 키는 한 테이블에 여러 개를 설정할 수 있다.

CREATE TABLE table_name
( id    CHAR(8) NOT NULL PRIMARY KEY,
  name  VARCHAR(10) NOT NULL,
  age   TINYINT UNSIGNED NULL,
  email CHAR(30) NULL UNIQUE );   -- 뒤에 UNIQUE를 붙여 고유 키로 설정한다.

 

 

 

 

 

CHECK

체크는 입력되는 데이터를 점검하는 기능을 한다. 예를 들어, 나이에 제한을 두어 성인만 가능하도록 않도록 할 수 있다.

CREATE TABLE table_name
( id    CHAR(8) NOT NULL PRIMARY KEY,
  name  VARCHAR(10) NOT NULL,
  age   TINYINT UNSIGNED NULL CHECK (age >= 19),   -- 뒤에 CHECK (조건식)을 추가한다.
  email CHAR(30) NULL );

 

그리고 필요하다면 ALTER TABLE을 통해서 제약조건을 추가할 수도 있다.

 

 

 

 

 

DEFAULT

기본값 정의는 값이 입력되지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

CREATE TABLE table_name
( id    CHAR(8) NOT NULL PRIMARY KEY,
  name  VARCHAR(10) NOT NULL,
  age   TINYINT UNSIGNED NULL DEFAULT 20,   -- 뒤에 DEFAULT 값을 입력한다.
  email CHAR(30) NULL UNIQUE );

 

ALTER TABLE을 이용할 때는 ALTER COLUMN문을 사용한다.

ALTER COLUMN column_name SET DEFAULT value;

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT '20';

 

INSERT INTO를 이용할 때 VALUES에 default라고 써주면 원하는 값이 입력된다.

INSERT INTO table_name VALUES('id', 'name', default, 'e@mail.com')

 

 

 

 

 

NULL

NULL은 빈 값을 허용, NOT NULL은 빈 값을 허용하지 않는다는 의미이다. 참고로 PK에는 자동으로 NOT NULL이 설정된다.

 

 

 

 

---

 

제약조건 정리

PRIMARY KEY (PK, 기본키) : 테이블에 저장된 각각의 row에 대한 고유성 보장, 한 테이블당 하나씩만 정의 가능, 중복이나 NULL 값 x, 자동으로 UNIQUE 인덱스로 생성됨 (UNIQUE KEY + NOT NULL)

UNIQUE KEY (고유키) : 테이블에 저장된 각각의 row에 대한 고유성 보장, NULL 값 o

NOT NULL : 해당 컬럼의 NULL 값의 입력 금지

CHECK : 컬럼에 저장될 수 있는 값의 범위 제한

FOREIGN KEY (외래키) : 하나의 테이블이 다른 테이블을 참조하고자 할 때 정의, '참조 무결성 제약' 옵션 별도로 선택 가능
    - `CASCADE` : Parent 값 삭제 시 Child 값 같이 삭제
    - `SET NULL` : Parent 값 삭제 시 Child의 해당 컬럼 NULL 처리
    - `SET DEFAULT` : Parent 값 삭제 시 Child의 해당 컬럼 DEFAULT 값으로 변경
    - `RESTRICT` : Child 테이블에 해당 데이터가 PK로 존재하는 경우에만 Parent 값 삭제 및 수정 가능
    - `NO ACTION` : 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가