My SQL 테이블 제약 조건 feat.Workbench

🟨 참 고 🟨

1. NOT NULL 조건

 

2. UNIQUE 조건 

 

3. CHECK 조건

 

4. DEFAULT (컬럼 기본값) 지정

 

5. PRIMARY KEY 지정

 

6. FOREIGN KEY 지정

     6-1. 테이블 생성할 때 FOREIGN KEY 설정하기[ALTER, ADD문]

     6-2. 이미 만들어진 테이블에 FOREIGN KEY 설정하기[ALTER, ADD문]

     6-3. FOREIGN KEY 제약 조건 삭제

     6-4. ON DELETE, ON UPDATE

 

 

 

저번 시간에 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용되는 INDEX(인덱스) 에 대해 알아보았습니다.

이번시간에는 테이블 안에서 데이터의 성격을 정의하는 테이블 제약 조건에 알아보겠습니다 :)

---

◼️ 1. NOT NULL 조건 ◼️

• NOT NULL 제약조건을 설정할 시에 해당 컬럼에는 꼭 데이터를 입력해야함
  ==> NOT NULL 설정 할 시 데이터 NULL일 때 오류 남

 

---

 

◼️ 2. UNIQUE 조건 ◼️

• 데이터의 유일성을 보장[중복되는 데이터 존재할 수 없음)하고, 자동으로 인덱스가 생성
• UNIQUE는 NULL은 허용하지만, PRIMARY KEY는 NULL값 허용 안함
• UNIQUE는 하나의 테이블에 여러개 가능하지만, PRIMARY KEY는 하나만 존재

 

---

◼️ 3. CHECK 조건 ◼️

• 컬럼의 값을 어떤 특정 범위로 제한

 

---

 

◼️ 4. DEFAULT (컬럼 기본값) 지정 ◼️

• 데이터를 입력하지 않아도 지정된 값이 기본으로 입력된다.
• ddefault라고 값을 명시하면  그 입력한 기본값이 들어간다.

 

 

---

◼️ 5. PRIMARY KEY 지정 ◼️

• UNIQUE + NOT NULL 의 기능과 같다.
• 기본키는 데이터 행을 대표하는 컬럼으로서 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다.
  ==> 다른 테이블에서 외래키(FOREIGN KEY)들이 참조 할 수 있는 키로서의 자격을 가지고 있다는말이 엄청 중요한 말이다 이것을 꼭 기억해두고 이 말을 짧게 참조 무결성이라고 한다.
• UNIQUE 제약 조건과 마찬가지로 자동으로 인덱스(검색키로서 검색 속도를 향상 시킴)를 생성, 그 이름은 기본키 제약조건의 이름과 같다.

 

---

◼️ 6. FOREIGN KEY 지정 ◼️

• 기본키,유니크 키를 참조하는 컬럼
• 외래키를 가지는 컬럼의 데이터형은 외래키가 참조하는 기본키,외래키의 컬럼과 데이터 형이 일치해야 한다. 
  [이것을 어길 시에 참조 무결성 제약에 의해 Error 3780이 뜬다. 이 오류때문에 한참을 고생했던 적이 있다...]
• 외래키에 의해 참조되고 있는 기본키는 삭제 불가하다ㅣ.
• ON UPDATE CASCADE 명령어를 입력하면 기본키가 수정될 경우 외래키도 같이 수정된다.
• ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 참조하는 기본키가 삭제될때 같이 삭제된다.

외래키는 테이블을 연결해주는 연결 다리 역할을 하며, 참조하는 테이블에 무결성을 높여주는 역학을 하기 때문에 매우 중요하다.

 

 

Workbench를 이용하여 FOREIGN KEY를 설정 할때 쿼리문을 이용해서 설정을 해야한다. (내가 못찾은 것일 수 있다...)

6-1. 테이블 생성할 때 FOREIGN KEY 설정하기[ALTER, ADD문]

 

[문법]

CREATE TABLE 테이블이름

(

    필드이름 필드타입,

    ...,

    [CONSTRAINT 제약조건이름]

    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

);

 

 

6-2. 이미 만들어진 테이블에 FOREIGN KEY 설정하기[ALTER, ADD문]

[문법]

ALTER TABLE '외래키 테이블이름' ADD FOREGIN KEY('외래키필드이름') REFERENCES '참조테이블이름'('참조필드이름')

 

[예제]

table 1에 제약조건 기본키인 id varchar(10)의 필드가 있고 

table 2에 id varchar(10)의 필드가 있다고 가정해보자.

 

ALTER TABLE TABLE2 ADD FOREIGN KEY (ID) REFERENCES TABLE1(ID);

 

 

6-3. FOREIGN KEY 제약 조건 삭제

[문법]

ALTER TABLE 테이블이름

DROP FOREIGN KEY 제약조건이름

 

[Reservation 테이블의 ID 필드에 설정된 FOREIGN KEY 제약조건을 삭제하는 예제]

ALTER TABLE Reservation

DROP FOREIGN KEY CustomerID;

 

 

6-4. ON DELETE, ON UPDATE

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

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

1. CASCADE : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블에서도 삭제와 수정이 같이 이루어집니다.

2. SET NULL : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 NULL로 변경됩니다.
3. NO ACTION : 참조되는 테이블에서 데이터를 삭제하거나 수정해도, 참조하는 테이블의 데이터는 변경되지 않습니다.

4. SET DEFAULT : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 필드의 기본값으로 설정됩니다.

5. 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

);