[MYSQL] 📚 테이블 제약 조건 정리

제약 조건(constraint)

제약 조건(constraint)이란 데이터의 무결성을 지키기 위해, 데이터를 입력받을 때 실행되는 검사 규칙을 의미합니다.

이러한 제약 조건은 CREATE 문으로 테이블을 생성할 때나 ALTER 문으로 필드를 추가할 때도 설정할 수도 있습니다.

---

NOT NULL

- NULL 비허용

- 중복값 허용

 

NOT NULL 제약 조건은 CREATE 문으로 테이블을 생성할 때나, 나중에 ALTER 문으로 추가할 수도 있습니다.

CREATE TABLE Test
(
    ID INT NOT NULL,
    Name VARCHAR(30),
    ReserveDate DATE,
    RoomNum INT
);

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 NOT NULL -- 새로운 not null 지정한 필드 추가

ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 NOT NULL -- 기존 필드를 not null로 수정

---

UNIQUE

- NULL 허용

- 중복값 비허용

CREATE TABLE 테이블이름
(
  필드명 필드타입 UNIQUE, -- 선언할 때 같이 추가
  ...
)

CREATE TABLE 테이블이름
(
  필드이름 필드타입,
  ...,
  [CONSTRAINT 제약조건이름] UNIQUE (필드이름) -- 뒤에 따로 추가. 이름 설정 가능
)

위의 두 문법은 모두 해당 필드에 UNIQUE 제약 조건을 설정합니다.

이때 두 번째 문법을 사용하면, 해당 제약 조건에 이름을 설정할 수 있습니다.

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 UNIQUE -- 이름 설정 불가능

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름] UNIQUE (필드이름) -- 이름 설정 가능

​UNIQUE 제약 조건을 설정하면, 해당 필드는 자동으로 보조 인덱스(INDEX)로 만들어집니다.

만일 제약 조건에 이름을 설정하면, 다음과 같이 이름을 사용하여 해당 제약 조건을 삭제할 수 있습니다.

-- 유니크키는 클러스터 인덱스가 아닌 그냥 보조 인덱스 이기 때문에 인덱스만 삭제하여도 된다.
ALTER TABLE 테이블이름
DROP INDEX 제약조건이름

---

PRIMARY KEY

- NULL 비허용

- 중복값 비허용

- 테이블당 하나

CREATE TABLE 테이블이름
(
  필드이름 필드타입 PRIMARY KEY,
  ...
)

CREATE TABLE 테이블이름
(
  필드이름 필드타입,
  ...,
  [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)
)

위의 두 문법은 모두 해당 필드에 PRIMARY KEY 제약 조건을 설정합니다.

이때 두 번째 문법을 사용하면, 해당 제약 조건에 이름을 설정할 수 있습니다.

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 PRIMARY KEY
​
ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)

-- 기본키 삭제
ALTER TABLE 테이블이름
DROP PRIMARY KEY

---

FOREIGN KEY

- FOREIGN KEY 제약 조건을 설정한 필드는 외래 키라고 부르며,

- 한 테이블을 다른 테이블과 연결해주는 역할을 합니다.

 

FOREIGN KEY 제약 조건을 설정할 때 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어 있어야 합니다.

외래키는 설정할 필드가 기본키나 유니크가 아니면 생성이 제약됩니다.

CREATE TABLE 테이블이름
(
  필드이름 필드타입,
  ...,
  [CONSTRAINT 제약조건이름] FOREIGN KEY (필드이름) REFERENCES 테이블이름2 (필드이름)
)

위의 문법을 사용하면 해당 필드에 FOREIGN KEY 제약 조건을 설정합니다.

이때 참조되는 테이블의 이름은 REFERENCES 키워드 다음에 명시됩니다.

​

다음 예제는 Test2 테이블의 ParentID 필드에, Test1 테이블의 ID 필드를 참조하는 FOREIGN KEY 제약 조건을 설정하는 예제입니다.

CREATE TABLE Test2
(
  ID INT,
  ParentID INT,
  FOREIGN KEY (ParentID) REFERENCES Test1(ID) ON UPDATE CASCADE​
);

위의 예제에서 Test2 테이블의 ParentID 필드는 Test1 테이블의 ID 필드를 참조하도록 설정됩니다.

따라서 Test1 테이블의 ID 필드가 변경되면, 같은 값의 Test2 테이블의 ParentID 필드도 같이 변경됩니다.

​

alter문으로도 외래키를 지정할수도 있습니다.

alter table '추가할 테이블 명'
add constraint '제약조건명' foreign key '컬럼명' references '부모 테이블 명' ('참조하는 PK 컬럼명') [ON DELETE CASCADE/ ON UPDATE CASCADE];

-- 외래키 삭제
ALTER TABLE 테이블이름
DROP FOREIGN KEY 제약조건이름

---

ON DELETE / ON UPDATE

FOREIGN KEY 제약 조건에 의해 참조되는 테이블에서 데이터의 수정이나 삭제가 발생하면, 참조하고 있는 테이블의 데이터도 같이 영향을 받습니다.

이때 참조하고 있는 테이블의 동작은 다음 키워드를 사용하여 FOREIGN KEY 제약 조건에서 미리 설정할 수 있습니다.

 

1. ON DELETE + 동작

2. ON UPDATE + 동작

 

참조되는 테이블의 값이 삭제될 경우의 동작은 ON DELETE 구문으로 설정할 수 있습니다.

또한, 참조되는 테이블의 값이 수정될 경우의 동작은 ON UPDATE 구문으로 설정할 수 있습니다.

 

이때 설정할 수 있는 동작은 다음과 같습니다.

• CASCADE : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블에서도 삭제와 수정이 같이 이루어집니다.
• SET NULL : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 NULL로 변경됩니다.
• NO ACTION : 참조되는 테이블에서 데이터를 삭제하거나 수정해도, 참조하는 테이블의 데이터는 변경되지 않습니다.
• SET DEFAULT : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 필드의 기본값으로 설정됩니다.
• RESTRICT : 참조하는 테이블에 데이터가 남아 있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없습니다.

 

다음 예제는 Test 테이블의 ID 필드를 Customer 테이블의 ID 필드를 참조하는 외래 키로 설정하는 예제입니다.

이때 참조되는 필드의 데이터가 수정될 때는 CASCADE 방식으로, 삭제될 때는 RESTRICT 방식으로 동작합니다.

CREATE TABLE Test2
(
  ID INT,
  ParentID INT,
  FOREIGN KEY (ParentID)
  REFERENCES Test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT
);

위의 예제에서 Test2 테이블의 ID 필드가 참조하고 있는 Test1 테이블의 ID 값이 수정되면, Test2 테이블의 ID 값도 같이 수정됩니다. 그리고 Test2 테이블의 레코드 중에서 Test1 테이블의 레코드가 ID 필드를 참조하는 레코드는 삭제할 수 없습니다.

---

DEFAULT

- DEFAULT 제약 조건은 해당 필드의 기본값을 설정할 수 있게 해줍니다.

 

만약 레코드를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본값을 저장합니다.

CREATE TABLE 테이블이름
(
  필드이름 필드타입 DEFAULT 기본값,
  ...
)

-- 기본값 추가
ALTER TABLE 테이블이름
ADD 필드이름 필드타입 DEFAULT 기본값 

-- 기본값 설정
ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 DEFAULT 기본값

-- DEFAULT 삭제
ALTER TABLE 테이블이름
ALTER 필드이름 DROP DEFAULT