table 생성 기본 구조

create table POST(
//	attribute명 DataType Constraints1 Constraints2,
//	...
	id BIGINT PRIMARY KEY,
    writerId VARCHAR(20) NOT NULL,
    title VARCHAR(100) NOT NULL,
    content LONGTEXT NOT NULL,
    date DATE NOT NULL,
    views INT DEFAULT 0
    );
    

table 삭제

DROP TABLE table_name;

---

1. PRIMARY KEY 설정

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

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

primary key 선언

1. attribute 하나일 때

create table POST(
	id BIGINT PRIMARY KEY,
	//(생략)
    );

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

create table POST(
	id BIGINT,
	writerId VARCHAR(20) NOT NULL,
	//(생략)
    PRIMARY KEY(id, writerId)
    );

---

2. UNIQUE

• UNIQUE로 지정된 값은 중복된 값을 가질 수 없다.

• NULL은 중복을 허용할 수도 있다. (RDBMS 마다 다름)

• NOT NULL과 UNIQUE를 같이 써주는것이 좋다.

UNIQUE 선언

1. attribute 하나일 때

create table POST(
	id BIGINT UNIQUE,
	//(생략)
    );

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

create table POST(
	id BIGINT PRIMARY KEY,
	writerId VARCHAR(20) NOT NULL  UNIQUE,
	//(생략)
    UNIQUE(id, writerId)
    );

---

3. Constraints

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

1) NOT NULL

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

create table POST(
	id BIGINT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content LONGTEXT NOT NULL,
);

2) DEFAULT

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

create table POST(
	id BIGINT PRIMARY KEY,
    title VARCHAR(100) DEFAULT '제목', 
    views INT DEFAULT 0 
    );
    

3) CHECK

attribute의 값을 제한한다.

1. attribute 하나로 구성될 때

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

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

create table PROJECT(
	startDate  DATE,   
    endDate  DATE
    //생략
    CHECK(startDate < endDate)
    );

---

4.FOREIGN KEY

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

MEMBER

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

---

POST

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

FOREIGN KEY 선언 방법

create table POST(
	//생략
    writerId 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

---

6. ALTER TABLE

ALTER TABLE을 사용해서 schema를 변경할 수 있다.

(예제) FOREIGN KEY 추가

ALTER TABLE department ADD FOREIGN KEY(leader_id)
	references EMPLOYEE(id)
    on update CASCADE
    on delete SET NULL;

1) attribute 추가

ALTER TABLE employee ADD FOREIGN KEY(leader_id);

2) attribute 이름 변경

ALTER TABLE employee RENAME COLUMN phone TO phoneNumber;

3) attribute 타입 변경

ALTER TABLE post MODIFY COLUMN writerId VARCHAR(10);

4) Table 이름 변경

ALTER TABLE post RENAME TO writings;

5) primary key 추가

ALTER TABLE member ADD PRIMARY KEY(loginId);

---

테이블 생성 예제

(예제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
);

---

(참고) 쉬운코드 인프런 강의
https://www.inflearn.com/course/%EB%B0%B1%EC%97%94%EB%93%9C-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EA%B0%9C%EB%A1%A0/dashboard