2024.09.02. 제약조건. alter.

* tabld에 선언된 제약조건 조회하는 방법

 

 

* primary key 조합값에 선언하기 
- pk는 테이블당 한번만 한번만 선언할수있지만 하나만 선언하는 것이 아니다. 
   => 여러컬럼의 조합값에 pk 선언가능하다.
- 조합값에 pk를 선언할때는 무조건 테이블레벨의 문법을 사용해야한다.

* foreign key 

- 컬럼2개의 조합값에 fk 선언을 해야한다. (그러면 부모컬럼도 조합값을 참조해야한다.)

- mysql 에서는 fk만 테이블 레벨 문법을 애용하고, 나머지 제약조건들은 컬럼레벨의 문법을 많이 활용한다.

- 외래키 제약조건으로 자기 자신 테이블이나 다른 테이블의 특정 컬럼(pk, uk)을 참조하는 제약조건
- fk 제약조건이 선언된 컬럼 : 자식 컬럼
- fk 제약조건이 참조하는 컬럼 : 부모 컬럼
- 자식 컬럼에는 부모 컬럼의 값 중 하나가 삽입/수정될 수 있다.
  (null 값은 허용함. 아직 결정안되었기 떄문에. 부모컬럼에 있는값만 허용하지만 null은 허용함.)

- foreign key 를 선언하려면 테이블 만드는 순서도 중요하다.

 

 

* check 제약조건

- 해당 컬럼이 만족해야 하는 조건문을 자유롭게 지정할 수 있는 제약조건
- (ex1) salary bigint check (salary > 0)
- (ex2) jumin char(13) check (length(jumin) = 13) -> 입력되는 값이 13자리가 안되면 입력이 안됨.
- (ex3) email varchar(30) check (email like '%@%') -> 메일주소에 @ 없으면 입력을 시키지 마라.
- (ex4) start_date date check (start_date >= '2022-05-01') -> 직원의 입사일은 회사 창립일(22.05.01)이후여야 한다.
- (ex5) 성별 varchar(10) check (성별 = '남' or 성별 ='여') (==) 성별 varchar(10) check (성별 in ('남','여'))

- check 제약조건은 desc에서 볼수가 없다.
- desc 명령어 역할 : 테이블 구조 조회(컬럼명, 데이터 타입, 컬럼사이즈, not null) + (pk, uk, fk, default 값) 제공됨 -> mysql
- 상세 정보를 보려면 DB 사전을 조회해야 한다.

 

● DB 사전을 활용해서 테이블에 선언된 제약조건 정보 조회하기

-- DB 사전으로 전환하기
show databases;
use information_schema; -- db에 내장된 database인데 사전역할을 한다고 함.
show tables;

-- table에 선언된 제약조건 정보조회
-- 'hr' DB(schema)에 포함된 테이블들의 제약조건 조회
select *
from table_constraints
where table_schema = 'hr'; -- where 절이 없으면 무수히 많은 결과가 나옴. notnull 은 desc에서 나오니까 여기서는 안보임.

-- 'test5' 테이블에 선언된 제약조건 정보 조회
select
from table_constraints
where table_name = 'test5';
-- 'employees' 테이블에 선언된 제약조건 정보 조회
select
from table_constraints
where table_name = 'employees';

-- table_constraints 의 문제 : check 제약조건의 경우 조건문이 뭔지를 알 수가 없음.
-- check 제약 조건인 경우 조건문 조회 (check_constraints 테이블 활용)
select *
from check_constraints
where constraint_schema = 'hr';

 

* 서브쿼리를 사용한 테이블 생성
- 서브쿼리 구문을 사용해서 테이블 생성 시 원본 테이블의 구조와 데이터를 그대로 가지는 복사본의 테이블이 생성됨.
- 단, 제약조건은 not null 제약조건만 복사됨.
- 테이블 백업 또는 테스트용 테이블 생성 시 활용됨.

● 서브쿼리 활용 테이블 생성 예제1

create table dept80
as select employee_id, last_name, salary * 12 as annsal, hire_date
from employees
where DEPARTMENT_ID = 80; -- 테이블 구조와 데이터 모두 복사해서 만듦.
-- notnull, 은 넘어왔는데 pk, check 조건은 안넘어옴.

desc dept80;
-- # Field, Type, Null, Key, Default, Extra
------------------------------------------------
-- 'annsal', 'double(22,0)', 'YES', '', NULL, ''
-- 'employee_id', 'int', 'NO', '', NULL, ''
-- 'hire_date', 'date', 'NO', '', NULL, ''
-- 'last_name', 'varchar(25)', 'NO', '', '', ''

● 서브쿼리 활용 테이블 생성 예제2

create table emp_bk
as select *
from employees; -- employees 테이블 전체 구조와 데이터를 복사해 놓음(백업기능)

 

* 테이블 수정(alter table)
- alter table 테이블명 add --- ;
- alter table 테이블명 modify --- ;
- alter table 테이블명 drop --- ;
- alter table 테이블명 rename --- ;

- 1) 컬럼 추가
- 마지막 컬럼으로 추가되고 초기값은 null이 삽입됨.

alter table dept80 add job_id varchar(10);
select * from dept80; -- 새롭게 추가된 컬럼(job_id)의 데이터는 전부 null값이 자동으로 입력된다.

-- 마지막 컬럼으로 추가되고 초기값은 0이 삽입됨.
alter table dept80 add salary int default 0;
select * from dept80; -- 새롭게 추가된 컬럼(salary)의 데이터가 default 값인 0이 자동으로 입력된다.

-- 첫번째 컬럼으로 추가하려면
alter table dept80 add emp_number int first; -- 이렇게하면 컬럼순서중 맨처음이 된다.
-- 이 컬럼에서느 not null을 못넣는다. 이곳은 디폴트 선언을 안했는데 null을 못넣게하면 아예 생성을 못하지.

-- 특정 컬럼뒤에 추가하려면?
alter table dept80 add email varchar(30) default '미입력' not null after last_name; 
-- last_name 컬럼 뒤에 email컬럼을 추가해달라

 

 2) 컬럼 수정(데이터타입, 컬럼사이즈, default, not null)

--타입변경 : salary(int) -> salary(bigint) 
alter table dept80 modify salary bigint;

-- last_name : (varchar(20), not null -> (varchar(30)) 
-- : 사이즈를 늘리는건 무조건 되는데 줄이는거는 그럴숭ㅆ고 안될수도 있따. 예를 들어 줄이려는 길이가 10이면 이미 저장된 데이터의 길이가 10이넘으면 안된다.
alter table dept80 modify last_name varchar(30);
-- 원래 last_name은 not null 이었는데 지금은 아니도록 바꼈다. 
-- 왜? alter 절을 사용할때 not null 제약조건을 안넣었기 때문이다

-- not null을 명시해야 없어지지 않고 그대로 살아있다.
-- employee_id : (int) -> (int, not null -> (bigint, not null)
alter table dept80 modify employee_id bigint not null;

-- email : (varchar(30), default '미입력', not null)
alter table dept80 modify email varchar(20) default '미입력' not null;

 

 3) 컬럼 삭제

alter table dept80 drop annsal;
alter table dept80 drop emp_number;

 

 

 

* create table 제약조건 넣어서 해보기

create table rental
(book_date datetime default now(),
    member_id int,
    copy_id int,
    act_ret_date date,
    exp_ret_date date,
    title_id int,
    primary key(book_date, member_id, copy_id, title_id), -- 조합된 primary key
    foreign key(member_id) references member(member_id),
    foreign key(copy_id, title_id) references title_copy(copy_id, title_id) -- 조합된 foreign key
);