Contents

제약 조건 / 프로시저

   Apr 13, 2023     10 min read

제약 조건

01. NOT NULL

  • 해당 필드는 MULL 값을 저장할 수 없다.
  • NOT NULL 제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 한다.

create database mydb01;
use mydb01;

create table t1(id int, name varchar(30), addr varhchar(30) not null);

insert into t1 values(1, '홍길동','부산');
select * from t1;

insert into t1(id, name) values(1, '서길동'); -- 에러
select * from t1;

insert into t1(addr) values('서울');
select * from t1;

select constraint_name, constraint_type
    from information_schema.table_constraints
    where table_name = 't1';



02. UNIQUE

  • UNIQUE 제약 조건을 설정하면, 해당 필드는 서로 다른 값을 가져야 한다.
  • 즉, 이 제약 조건이 설정된 필드는 중복된 값은 금지되지만, 비어있는 것은 허용한다.(MSSQL 제외)

create database mysql01;
use mysql01;

create table t2(id int unique, name varchar(30), addr varchar(30));

select constraint_name, constraint_type
    from information_schema.table_constraints
    where table_name = 't2';

insert into t2(name,addr) values('홍길동','부산');
insert into t2(namd,addr) values('홍길동','부산');
select * from t2;

insert into t2 values(1,'남길동','서울');
insert into t2 valueS(1,'북길동','인천'); -- 에러

insert into t2 values(null,'남길동','서울');
insert into t2 values(null,'북길동','인천'); -- 성공



03. DEFAULT

  • DEFAULT 제약 조건은 해당 필드의 기본값을 설정할 수 있게 해준다.
  • 만약 레코드를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본 값을 저장한다.

use mysql01;

create table t3(id int, name varchar(30), addr varchar(30) default '인천');

select constraint_name, constraint_type
    from information_schema.table_constraints
    where table_name = 't3';

insert into t3(id, name, addr) values(1,'홍길동','서울');
select * from t3;

insert into t3(id, name) values(2,'서길동');
select * from t3;



04. CHECK

  • CHECK 제약 조건은 입력되는 데이터를 점검하는 기능

use mysql01;

create table t4(id int check(id>=2 and id<=4), name varchar(30), addr varchar(30));

select constraint_name, constraint_type
    from information_schema.table_constraints
    where table_name = 't4';

insert into t4 values(2,'홍길동','서울');
select * from t4;

insert into t4 values(5, '이순신','부산');  --에러
select * from t4;



5. PRIMARY KEY

  • PRIMARY KEY 제약 조건을 설정하면, 해당 필드는 NOT NULL과 UNIQUIE 제약 조건의 특징을 모두 가진다.
  • 따라서 이 제약 조건이 설정된 필드는 NULL 값을 가질 수 있으며, 또한 중복된 값을 가질 수 없다.
  • 이러한 PRIMARY KEY 제약 조건을 기본 키라고 한다.
  • 테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 한다.

use mysql01;
create table t5(id int primary key, name varchar(30), addr varchar(30));

select constraint_name, constraint_type
    from information_schema.table_constraints
    where table_name = 't5';

insert into t5 values(1,'홍길동','부산');
select * from t5;

insert into t5 values(1, '서길동','광주');  --에러
select * from t5;

insert into t5 values(null, '남길동','인천'); --에러
select * from t5;



Ex. 제약 조건

use mysql01;

create table t6(id int, age int not null, name varchar(30), addr varchar(30)default '인천',
constraint t6_id_pk primary key(id),
constraint t6_age_uq unique(age),
constraint t6_age_ck check(age >=10)
);

select * from information_schema.table_constraints where table_name = 't6';

insert into t6 values(1,11,'홍길동','광주');
select * from t6;

insert into t6 values(1,22,'서길동','광주'); --에러
select * from t6;

insert into t6 values(2,7,'서길동','광주'); --에러
select * from t6;

insert into t6(id, age, name) values(3, 22, '서길동');
select * from t6;



6. FOREIGN KEY

  • FOREIGN KEY 제약 조건을 설정한 필드를 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 한다.
  • 외래 키가 설정된 테이블에 레코드를 입력하ㅕㅁㄴ, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력된다.
  • 즉, FOREIGN KEY 제약 조건은 하나의 테이블을 다른 테이블에 의존하게 만든다.
  • FOREIGN

Ex. FOREIGN KEY

  • T1 (부모 테이블)
ID(INT, P.K)JOB(VARCHAR(30), unique)SALES(INT)
1아이티7000
2미용4000
3야구5000
4축구6000
5농구8000
6운전5000


create table t1(id int primary key, job varchar(30) unique, sales int);
insert into t1 values(1,'아이티',7000);
insert into t1 values(2,'미용',4000);
insert into t1 values(3,'야구',5000);
insert into t1 values(4,'축구',6000);
insert into t1 values(5,'농구',8000);
insert into t1 values(6,'운전',5000);



  • T2 (자식 테이블)
ID(INT, P.K)NAME(VARCHAR(30))AGE(INT)ADDR(VARCHAR(30))JOB(VARCHAR(30), F.K)
1홍길동40부산미용
2서길동50광주야구
3남길동40인천야구
4북길동70부산축구


create table t1(id int primary key, name varchar(30), age int, addr varchar(30), job varchar(30), constraint t1_job_t2_job foreign key(job));

insert into t1 values(1,'홍길동',40,'부산','미용');
insert into t1 values(2,'서길동',50,'광주','야구');
insert into t1 values(3,'남길동',40,'인천','야구');
insert into t1 values(4,'북길동',70,'부산','축구');
insert into t1 values(5,'이순신',50,'서울','IT'); --에러

select * from t2;



EX. 02

  • 부모가 지워질 때 같이 지워지는 것

create table t2(id int primary key, name varchar(30), age int, addr varchar(30), job varchar(30), constraint t1_job_t2_job foreign key(job) references t1(job) on delete cascade);

insert into t2 values(1,'홍길동',40,'부산','미용');
insert into t2 values(2,'서길동',50,'광주','야구');
insert into t2 values(3,'남길동',40,'인천','야구');
insert into t2 values(4,'북길동',70,'부산','축구');

delete from t1 where id = 2;
select * from t1;
select * from t2;



Ex. 03

  • 부모 업데이트 -> 자식 테이블 자동 업데이트

create table t2(id int primary key, name varchar(30), age int, addr varchar(30), job varchar(30), constraint t1_job_t2_job foreign key(job) references t1(job) on update cascade);

insert into t2 values(1,'홍길동',40,'부산','미용');
insert into t2 values(2,'서길동',50,'광주','야구');
insert into t2 values(3,'남길동',40,'인천','야구');
insert into t2 values(4,'북길동',70,'부산','축구');
select * from t2;

update t1 set job='IT02' where id=2;
select * from t1;
select * from t2;



Untitled

Untitled (1)

잘 업데이트가 되었다.

Foreign Key 구성시 동작 구성

  • ON DELETE [?]
    참조되는 테이블의 값이 삭제될 경우의 동작을 ON DELETE 구문으로 설정
  • ON UPDATE [?]
    참조되는 테이블의 값이 수정될 경우의 동작을 ON UPDATE 구문으로 설정

  • [?]에 동작 종류
  • CASCADE : 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블에서도 삭제와 수정이 같이 이루어짐
  • SET NULL : 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블의 데이터는 NULL로 변경됨
  • NO ACTION : 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블의 데이터는 변경되지 않음
  • SET DEFAULT : 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블의 데이터는 필드의 기본값으로 설정
  • RESTRICT : 참조하는 테이블에 데이터가 남아 있으면 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없음

07. 제약 조건 설정 방식

  • 컬럼 레벨 방식
create table t1(
    id int primary key,
    name varchar(30) not null,
    addr varchar(30) unique,
    job varchar(30) references t2(job)
);



  • 테이블 레벨 방식
create table t1(
    id int,
    name varchar(30) not null,
    addr varchar(30),
    job varchar(30),
    primary key(id), unique(addr), foreign key(job) references t2(job)
);



제약 조건 설정시, 테이블 레벨 방식만 가능한 경우

  • 기본키를 복합키로 설정하는 경우
    2개 이상의 칼럼을 기본키로 설정하는 경우
  • alter table 로 제약 조건을 추가할 경우

create table t1(id int primary key, age int primary key); -- 잘못된 문법

create table t1(id int, age int, primary key(id,age));

insert into t1 values(1,1);
insert into t1 values(1,2);
insert into t1 values(1,1); -- 실패
insert into t1 values(1,2); -- 실패



프로시저

  • 쿼리문의 집합
  • 프로그래밍 할 때 우리가 만드는 함수와 거의 비슷함

  • 반환 값이 없는 함수

  • 변수 사용
    무조건 변수명 앞에 @를 붙여줘야 함.
    ex. sety @my = 1; 또는 set @my := 1;

  • 프로 시저, 사용자 정의 함수 만들 때 변수 선언 많이 사용함

  • 프로시저 특징
    1. 보안을 강화할 수 있음(뷰와 같음)
    2. 유지보수가 편함
    3. 프로시저 호출 시 처음 1회 컴파일 이후 메모리에 로딩 되어서 성능 향상이 있음.
    4. 긴 수십줄의 쿼리 문자열을 생성할 필요 없이 프로지서 이름만 호출

01. 생성 / 제거

drop procedure if exists select_proc;  -- 기본 프로시저 있으면 제거 (주석 없이 실행하기)

delimiter $$  -- 종류 문자 $$로 변경
create procedure select_proc() -- 프로시저 생성(주석 없이 실행하기)
begin  
    select * from usertbl where height > 170;
end $$
delimiter ; -- 종류 문자 원래대로 복구 (주석 없이 실행하기)

call select_proc(); -- 프로시저 실행(주석 없이 실행하기)



02. IN 매개변수

  • 프로시저에서 매개 변수를 받을 때 사용함
drop procedure if exists select_proc;

delimiter $$
create procedure select_proc(in param_userid varchar(20))
begin  
    select * from usertbl where userid = param_userid;
end $$
delimiter ;

call select_proc('BBK');
call select_proc('SSK');



03. OUT 매개 변수

  • 프로 시저에서 결과를 반환받을 때 사용
drop procedure if exists select_proc;

delimiter $$
create procedure select_proc(in param_userid varchar(20), out ret_name varchar(20))
begin
    select name into ret_name from usertbl where userid = param_userid;
end $$
delimiter ;

call select_proc('BBK',@ret_name);
select @ret_name; -- 바비킴



04. INOUT 매개변수

  • 프로 시저에서 매개 변수를 받을 때 그리고 결과 값을 받을 때 둘 다 사용 가능
drop procedure if exists select_proc;

delimiter $$
create procedure select_proc(inout val varchar(20))
begin
    select name into val from usertbl where userid = val;
end $$
delimiter ;

set @val = 'BBK';
call select_proc(@val);
select @val;



Ex.

  • t1
id(p.k)nameaddr
1홍길동부산
2서길동광주
3남길동서울
4북길동인천



  • view01 : user1/select -> id>3
  • proc01 : user2/execute -> id<4

use mysql;

create table t1(id int primary key, name varchar(30), addr varchar(30));
insert into t1 values(1,'홍길동','부산');
insert into t1 values(2,'서길동','광주');
insert into t1 values(3,'남길동','서울');
insert into t1 values(4,'북길동','인천');
select * from t1;

create user user1@'%' identified by '123456';
create user user2@'%' identified by '123456';

grant select on *.* to user1@'%';
grant execute on *.* to user2@'%';

create role view01;
create role proc01;

grant select on mysql.view01 to user1;
grant execute on procedure mysql.proc01 to user2;

grant view01 to user1@'%';
grant proc01 to user2@'%';

create view view01
as
select * from t1 where id>3;

drop procedure if exists proc01;
delimiter $$
create procedure proc01()
begin
    select * from t1 where id<4;
end $$
delimiter ;

call select_proc();