[데이터베이스]

키 무결성과, 참조 무결성을 유지하기 위한 기본키, 유니크, 외래키 제약조건

 

의존성이 없는 릴레이션부터 테이블로 추가해야 한다.

 

 

예시에서 보면, 의존성이 없는 회원 릴레이션을 먼저 테이블로 만들고, 반려동물, 게시글, 관심사 릴레이션을 테이블로 만든다. 마지막으로 댓글과 조회기록 릴레이션을 테이블로 생성한다. 

 

 

 

 

테이블을 만들 때, 기본키(primary key), 유니크(unique) 제약조건은 테이블의 키 무결성을 유지하게 한다. 만약 같은 기본키를 가지는 값을 추가할 때 에러가 나며 키 무결성을 유지한다. 

 

테이블을 생성하고 기본키나 유니크 제약조건을 추가할 수 있다. (DBMS에 따라 가능여부가 달라짐)

//MySQL에서 기본키 추가
ALTER TABLE member MODIFY COLUM m_id INT NOT NULL PRIMARY KEY;

ALTER TABLE member ADD PRIMARY KEY(m_id);

//MySQL에서 유니크 제약조건 추가
ALTER TABLE member MODIFY COLUM m_member_id VARCHAR(10) NOT NULL UNIQUE;

ALTER TABLE member ADD UNIQUE(m_member_id);

 

외래키 제약조건 작성

만약 회원 테이블의 기본기인 회원번호를 참조하는 게시글 테이블의 회원번호에 대해 외래키 제약조건을 작성하면

FOREIGN KEY (board_m_id) REFERENCES member(m_id) ON UPDATE CASCADE ON DELETE CASCADE

 

만약에 참조되는 키가 수정되는 경우 존재하지 않는 값을 참조하여 참조 무결성에 위배될 수 있다. 

ON UPDATE CASCADE: 참조되는 키가 변경될 때 외래키도 자동으로 변경해준다.

ON UPDATE RESTRICT: 변경할 키가 참조하고 있는 외래키 값이 존재할 때 변경을 불가능하게 한다. 

ON UPDATE SET NULL: 참조되는 키가 변경될 때 외래키 값을 NULL 값으로 지정한다. 

 

참조되는 키가 삭제될 때 참조하는 외래키를 처리하는 방법

ON DELETE CASCADE: 참조되는 키의 값이 삭제되면 참조하는 연관 데이터도 모두 삭제한다.

ON DELETE RESTRICT: 참조되는 키의 값이 삭제할 때 해당 키를 참조하고 있는 외래키의 값이 존재하는 경우 삭제가 제한된다. 

ON DELETE SET NULL: 참조되는 키의 값이 삭제되면 해당 키를 참조하고 있는 외래키의 값이 존재하는 경우 해당 외래키에 null 값을 저장한다. 

 

Auto increment: 자동으로 속성값을 넣어준다

ex) m_id INT NOT NULL PRIMARY KET AUTO_INCREMENT

보통 기본키를 선언할 때 사용되는데, 데이터가 추가될 때마다 칼럼 값을 1식 자동 증가되는 번호가 부여된다. 

만약 기본키가 3인 데이터를 삭제하고 새로운 데이터를 추가하면, 4를 기본키로 가지는 데이가 추가된다. 즉 한 번 사용했던 번호는 다시 사용하지 않는다. 

 

 

1. 테이블 생성 시 칼럼 기본기 제약조건 추가

테이블을 생성할 때 특정 칼럼을 기본키로 설정할 때 해당 칼럼은 not null 조건을 가져야 한다. 엔티티 무결성에 의해 기본키는 데이터 추가 시 중복된 값을 삽입할 수 없다. 

 

CREATE TABLE 테이블이름 (
    칼럼1 데이터타입 [NULL | NOT NULL] [CONSTRAINT 제약조건이름] PRIMARY KEY,
    칼럼2 데이터타입 [NULL | NOT NULL]
    …
)

CREATE TABLE MEMBER (
    M_ID INT UNSINED NOT NULL PRIMARY KEY,
    M_MEMBER_ID VARCHAR(96) NOT NULL UNIQUE,
    M_NAME VARCHAR(32) NULL,
    M_GENDER VARCHAR(32) NULL,
    M_BIRTH CHAR(32) NULL,
    M_CREATED_DATETIME DATETIME NULL
);

CREATE TABLE MEMBER_INTEREST (
    MI_M_ID INT NOT NULL,
    MI_NAME VARCHAR(18) NOT NULL,
    PRIMARY KEY (MI_M_ID, MI_NAME)
)

 

2. 테이블 생성 시 칼럼 유니크키 제약조건 추가

CREATE TABLE 테이블_이름 (
    칼럼1 데이터타입 [NULL | NOT NULL] [CONSTRAINT 제약조건이름] UNIQUE,
    칼럼2 데이터타입 [NULL | NOT NULL] [CONSTRAINT 제약조건이름] UNIQUE
    …
)

CREATE TABLE MEMBER (
    M_ID INT UNSIGNED NOT NULL PRIMARY KEY,
    M_MEMBER_ID VARCHAR(96) NOT NULL UNIQUE,
    M_NAME VARCHAR(32) NULL,
    M_GENDER VARCHAR(32) NULL,
    M_BIRTH CHAR(16) NULL,
    M_CREATED_DATETIME DATETIME NULL
);

 

 

3. 테이블 생성 시 칼럼 외래키 제약조건 추가

 

CREATE TABLE 테이블_이름 (
    칼럼1 데이터타입 [NULL | NOT NULL],
    칼럼2 데이터타입 [NULL | NOT NULL], 
    [CONSTRAINT 제약조건이름] FOREIGN KEY (칼럼1) REFERENCES 참조할_테이블(참조할_칼럼) [ACTION]
)

CREATE TABLE MEMBER (
    M_ID INT UNSIGNED NOT NULL PRIMARY KEY,
    M_MEMBER_ID VARCHAR(96) NOT NULL UNIQUE,
    M_NAME VARCHAR(32) NULL,
    M_GENDER VARCHAR(32) NULL,
    M_BIRTH CHAR(16) NULL,
    M_CREATED_DATETIME DATETIME NULL
);

CREATE TABLE BOARD (
    BO_ID INT UNSINED NOT NULL PRIMARY KEY,
    BO_M_ID INT UNSIGNED NOT NULL,
    BO_TITLE VARCHAR(128) NOT NULL,
    BO_CONTENT TEXT NULL,
    BO_CREATED_DATETIME DATETIME NULL,
    BO_MODIFIED_DATETIME DATETIME NULL,
    FOREIGN KEY (BO_M_ID) REFERENCES MEMBER(M_ID)
)

 

4. ON DELETE와 ON UPDATE 액션 추가

참조되는 튜플이 수정 또는 삭제될 때 참조하는 칼럼의 action을 지정할 수 있다. 

 

5. 테이블 생성 후 기본키, 유니크키, 외래키 제약조건 추가

기존에 기본키 제약조건이 있다면 기본키 제약조건 삭제 후 기본키 제약조건 추가 가능

기본키 제약조건 삭제
ALTER TABLE 테이블이름 DROP PRIMARY KEY;

기본키 제약조건 추가1
ALTER TABLE 테이블이름 ADD PRIMARY KEY(칼럼1, 칼럼2, …);

기본키 제약조건 추가2 (다른 방법)
ALTER TABLE 테이블이름 MODIFY COUMN 칼럼이름 데이터타입 NOT NULL PRIMARY KEY;

ALTER TABLE 테이블이름 ADD UNIQUE [제약조건이름] (칼럼1, 칼럼2, …);

ALTER TABLE 테이블이름 MODIFY COLUMN 칼럼이름 데이타타입 NOT NULL UNIQUE;

ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] FOREIGN KEY(외래키) REFERENCES 참조할_테이블(참조할_칼럼);

 

6. 테이브 생성 후 기본키, 유니크키, 외래키 제약조건 제거

제약 조건의 이름으로 삭제해야 하는 경우 테이블에 걸려 있는 제약조건이름 확인하는 방법

SHOW INDEX FROM 테이블이름;

 

ALTER TABLE 테이블이름 DROP PRIMARY KEY;

ALTER TABLE 테이블이름 DROP INDEX 제약조건이름;

ALTER TABLE 테이블이름 DROP FOREIGN KEY 제약조건이름;

 

 

7. Auto Increment

CREATE TABLE MEMBER (
    M_ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    M_MEMBER_ID VARCHAR(96) NOT NULL UNIQUE,
    M_NAME VARCHAR(32) NULL,
    M_GENDER VARCHAR(32) NULL,
    M_BIRTH CHAR(16) NULL,
    M_CREATED_DATETIME DATETIME NULL
);