[SQL/MariaDB] Constraints / Table 생성하기

table 생성 기본 구조

(attribute명) (DataType) (constraints1) (constraints2)

create table member(
	id bigint primary key,
	name varchar(20) not null,
	login_id varchar(20) not null,
	password varchar(100) not null
);

table 삭제

drop table (table_name);

---

1. PRIMARY KEY 설정

primary key는 table의 tuple을 식별하기 위해 사용한다.

하나 이상의 attribute로 구성되어 있으며, 중복된 값, Null 값을 가질수 없다.

primary key 선언

attribute 하나일 때 

create table member( 
	id bigint PRIMARY KEY,
    //...
);

attribute가 하나 이상일 때 ( 아래에서 한 번에 설정 )

create table member(
	id BIGINT,
	login_id VARCHAR(20) NOT NULL,
 	//...
	primary key(id, writerId)
 );

---

2. UNIQUE

• UNIQUE로 지정된 값은 중복된 값을 가질 수 없다.
• NULL은 중복을 허용할 수도 있다. (RDBMS 마다 다름)
• NOT NULL과 UNIQUE를 같이 써주는것이 좋다.

UNIQUE 선언

attribute 하나일 때

create table member(
	id BIGINT UNIQUE,
    //...
 );

attribute가 하나 이상일 때 ( 아래에서 한 번에 설정 )

create table member(
	id BIGINT PRIMARY KEY,
	login_id VARCHAR(20) NOT NULL  UNIQUE,
	//(...)
     UNIQUE(id, login_id)
 );

---

3. Constraints

대부분의 constraints는 data type 옆에 나열한다.

1) NOT NULL

NULL 값이 들어올 수 없도록 설정한다.

create table member(
	id bigint primary key,
	name varchar(20) not null,
	login_id varchar(20) not null,
	password varchar(100) not null
);

---

2) DEFAULT

attribute의 default 값을 설정해준다.
새로운 tuple을 저장할 때, 해당 attribute에 값이 없다면 default로 설정한 값으로 저장한다.

create table member2(
	id bigint primary key,
	name varchar(20) defalut 'user',
	login_id varchar(20) not null,
	password varchar(100) not null,
	point int default 0
);

---

3) CHECK

attribute의 값을 제한한다.

attribute 하나로 구성될 때

create table member(
	id bigint primary key,
	point int check(point <= 1000)
);

attribute가 하나 이상일 때 ( 아래에서 한 번에 설정 )

create table project(
	start_date DATE,   
	end_date DATE
	//...
	CHECK(start_date < end_date)
 );

---

4.FOREIGN KEY

MEMBER 테이블의 loginId를 FOREIGN KEY로 사용하는 POST 테이블을 CREATE 하는 예제이다.

MEMBER

CREATE TABLE MEMBER (
    id BIGINT ,
    name VARCHAR(10) NOT NULL,
    login_id VARCHAR(10) NOT NULL,
    password VARCHAR(10) NOT NULL,
    PRIMARY KEY(id)
);

POST

create table POST(
    id BIGINT PRIMARY KEY,
    writer_id VARCHAR(20),
    title VARCHAR(30) NOT NULL,
    content LONGTEXT ,
    date DATE NOT NULL,
    views INT DEFAULT 0,
    FOREIGN KEY (writer_id) references member(login_id)
        on delete CASCADE on update CASCADE
    );

---

FOREIGN KEY 선언 방법

create table POST(
    //...
    writer_id VARCHAR(20),
    FOREIGN KEY (writerId) references member(loginId)
        on delete (option)
        on update (option)
);

reference option

• CASCADE : 참조값의 삭제, 변경을 그대로 반영
• SET NULL : 참조값이 삭제, 변경 시 NULL로 변경
• RESTRICT : 참조값의 삭제, 변경을 금지
• SET DEFAULT: 참조값이 삭제, 변경 시 default 값으로 변경 ( MySQL에서는 지원하지 않음 )

---

5. constraints에 이름 붙이기

create table MEMBER(
    id BIGINT PRIMARY KEY,
    age INT CONSTRAINTS age_under_120 CHECK(age<=120)
    );

• constraints에 이름을 붙여서 어떤 constraints를 위한 했는지 쉽게 알 수 있도록 한다.
• DATA type과 constraints 사이에 CONSTRAINTS "constraints 이름"으로 이름을 만들어준다.
• 추후에 constraints 이름으로 constraint를 삭제할 수 있다.

오류메시지가 아래와 같이 변경된다.
(변경 전)
Check constraint 'test_chk_1' is violated
(변경 후)
Check constraint 'age_under_120' is violated

---

테이블 생성 예제

(예제 1)

CREATE TABLE EMPLOYEE(
    id INT PRIMARY KEY,
    name varchar(10) NOT NULL UNIQUE,
    birth_date DATE,
    sex CHAR(1) CHECK (sex in ('M', 'F')),
    position VARCHAR(10),
    salary INT DEFAULT 50000000,
    dept_id INT,
    FOREIGN KEY(dept_id) references DEPARTMENT(id)
        on delete SET NULL on update CASCADE,
    CHECK(salary >= 50000000)
);

(예제 2)

CREATE TABLE PROJECT(
    id INT PRIMARY KEY,
    name varchar(10) NOT NULL UNIQUE,
    leader_id INT,
    start_date DATE,
    end_date DATE,
    FOREIGN KEY(leader_id) references EMPLOYEE(id)
        on delete SET NULL on update CASCADE,
    CHECK(start_date <= end_date)
);

(예제 3)

CREATE TABLE WORKS_ON(
    empl_id INT,
    proj_id INT,
    PRIMARY KEY(empl_id, proj_id),
    FOREIGN KEY(empl_id) references EMPLOYEE(id)
        on delete CASCADE on update CASCADE,
    FOREIGN KEY(proj_id) references PROJECT(id)
        on delete CASCADE on update CASCADE
);