Table of Contents

• 제약조건 확인
• NOT NULL
• PRIMARY KEY
• FOREIGN KEY
• UNIQUE KEY
• DEFAULT
• CHECK
• 복합키
• 제약조건 삭제

제약조건

제약조건(Constraint)에 대해 정리한다.

제약조건 확인

select * from information_schema.table_constraints;

select * from information_schema.table_constraints WHERE table_name = 'member';

NOT NULL

CREATE TABLE member (
    email VARCHAR(255) NOT NULL
    ...
);

ALTER TABLE member MODIFY email VARCAHR(255) NOT NULL;

PRIMARY KEY

CREATE TABLE member (
	id BIGINT PRIMARY KEY,
    ...
);

CREATE TABLE member (
	id BIGINT,
    ...
    PRIMARY KEY(id)
);

CREATE TABLE member (
	id BIGINT,
    ...
    CONSTRAINT pk_member PRIMARY KEY(id)
);

FOREIGN KEY

CREATE TABLE post (
    writer_id BIGINT
    ...
    FOREIGN KEY (writer_id) REFERENCES member(id)
    ...
);

CREATE TABLE post (
    writer_id BIGINT
    ...
    CONSTRAINT fk_post_writer_id_member_id FOREIGN KEY (writer_id) REFERENCES member(id)
    ...
);

ALTER TABLE post 
ADD CONSTRAINT
FOREIGN KEY writer_id REFERENCES member (id) ON UPDATE CASCADE ON DELETE CASCADE

ALTER TABLE post 
ADD CONSTRAINT fk_post_writer_id_member_id 
FOREIGN KEY writer_id REFERENCES member (id) ON UPDATE CASCADE ON DELETE CASCADE

UNIQUE KEY

CREATE TABLE member (
    email VARCHAR(255) UNIQUE KEY
)

CREATE TABLE member (
    email VARCHAR(255),
    ...
    UNIQUE KEY(email)
)

CREATE TABLE member (
    email VARCHAR(255),
    ...
    CONSTRAINT unq_member_email UNIQUE KEY(email)
)

ALTER TABLE member 
ADD CONSTRAINT UNIQUE KEY(email);

ALTER TABLE member 
ADD CONSTRAINT unq_member_email UNIQUE KEY(email);

DEFAULT

CREATE TABLE member (
    ...
	name VARCHAR(255) DEFAULT 'Annonymous'
);

ALTER TABLE member MODIFY email VARCHAR(255) DEFAULT 'Annonymous';

CHECK

CREATE TABLE member (
    ...
	age INT,
    CHECK (age >= 0)
);

ALTER TABLE member ADD CONSTRAINT CHECK(age > 0);

ALTER TABLE member ADD CONSTRAINT chk_member_age CHECK(age > 0);

복합키

복합 기본키

CREATE TABLE order (
    member_id bigint NOT NULL,
    product_id bigint NOT NULL,
    ...
    PRIMARY KEY(member_id, product_id)
);

복합 유일키

CREATE TABLE order (
    id biging NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id bigint NOT NULL,
    product_id bigint NOT NULL,
    ...
    UNIQUE KEY(member_id, product_id)
);

제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;