Database

/* 테이블 삭제 */

-- drop table employee;
-- drop table department;

/* 테이블 생성 */

-- department
create table department(
	deptno int(11),
	deptname varchar(20) not null,
	floor int(11) default 10
);

-- employee
-- utf-8: 한글 = 3Byte / 영어 = 1Byte
create table employee(
	empno int not null,
	empname varchar(20) not null,        
	title varchar(20) default '사원',
	manager int,
	salary int,
	dno int
 );

desc department;
desc employee;

insert 
	into department 
		values
			(1, '영업',  8),
			(2, '기획', 10),
			(3, '개발',  9),
			(4, '총무',  7);
		
select * from department;

insert 
	into employee(empno, empname, title, manager, salary, dno)
		values
			(4377, '이성래', '사장', null, 5000000, 2),
			(3426, '박영권', '과장', 4377, 3000000, 1),
			(1003, '조민희', '과장', 4377, 3000000, 2),
			(3011, '이수민', '부장', 4377, 4000000, 3),
			(2106, '김창섭', '대리', 1003, 2500000, 2),
			(3427, '최종철', '사원', 3011, 1500000, 3);
		
insert 
	into employee(empno, empname, manager, salary, dno)
		values(1365, '김상원', 3426, 1500000, 1);
		
select * from employee;

-- department
-- 기본키 제약조건 추가 
alter table department add constraint primary key(deptno);

-- 기본키 제약조건 삭제
-- alter table department drop primary key;

-- employee
-- 기본키(primary key) 추가
alter table employee add constraint primary key(empno);
-- 외래키(foreign key) 추가
alter table employee add constraint fk_employee_manager
	foreign key (manager) references employee(empno);
alter table employee add constraint fk_employee_dno
	foreign key (dno) references department(deptno)
	on delete no action   /* department(deptno)가 삭제 시, 해당 직원이 소속되어 있을 경우 삭제 금지 */
	on update cascade;    /* department(deptno)가 수정될 시, 연쇄적으로 해당 직원의 부서번호도 연쇄적으로 변경 */
-- alter table employee drop foreign key fk_employee_dno;
desc department;
desc employee;