- 기본적인 쿼리 연습
- create, alter, pk, fk, relation
- 중요한건 도메인을 담을 수 있는 데이터베이스를 설계하는 것
- 링크
create table DEPARTMENT(
id int primary key,
name varchar(20) not null unique,
leader_id INT
);
create table EMPLOYEE(
id int primary key,
name varchar(30) not null,
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 )
);
create table PROJECT(
id INT primary key ,
name varchar(20) 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 < PROJECT.end_date )
);
create table WORKS_ON (
empl_id int,
proj_id int,
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
);
alter table DEPARTMENT ADD FOREIGN KEY (leader_id)
REFERENCES EMPLOYEE(id) on update CASCADE on DELETE set null;