[MYSQL / Database] 제약조건 적용해보기( NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY,FOREIGN KEY)

 제약조건 확인 방법 

---

 

select    *  from information_schema.table_constraints  where table_name = '테이블명';

select  constraint_name, constrint_type 
			from information_schema.table_constraints  where table_name = '테이블명';
            
desc '테이블명';

---

 

 NOT NULL  실습해보기 

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

 

create database mydb01;
use mydb01;

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

insert into t1 values(1, '홍길동', '부산');  -- 성공
insert into t1(id, name) values(2, '서길동'); -- 에러
insert into t1(addr) values('서울'); 
select * from t1;

desc t1;

insert into t1(id, name) values(2, '서길동');의 명령어가 에러가 난것을 확인할 수 있습니다.

not null의 조건으로 addr의 값만 넣으면 에러가 발생하지 않고, 값이 잘 들어가게 됩니다.

 

 

 

UNIQUE 제약 실습

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

 

create database mysql01;
use mysql01;

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

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

insert into t2 values(1,'남길동','서울');
insert into t2 values(2,'북길동','인천');

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

select * from t2;

MYSQL에서는 NULL이 중복으로 들어가도 오류가 나지 않는다.

MYSQL에서 같은 값만 아니면 처리됨

 

 

 

 

 

 

 DEFAULT

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

 

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;

 

 

 

 

 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;

 

 

 PRIMARY KEY

PRIMARY KEY 제약 조건을 설정하면, 해당 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가진다.
따라서 이 제약조건이 설정된 필드는 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, '홍길동', '부산');
insert into t5 values(1, '서길동', '광주'); --에러 
insert into t5 values(null,'남길동', '인천'); --에러
select * from t5;

=> PRIMARY KEY적용됨

 

 

 

 

<연습2>

create table t6(
	id int,
	age	int not null,
    name	varchar(30),
    addr	varchar(30) default 'inchoen',
	constraint t6_int_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,'hong','광주');
insert into t6 values(1,22,'서길동','광주'); --에러 => PRIMARY 값 오류
insert into t6 values(2,7,'서길동','광주'); --에러  => CHECK에서 걸림
insert into t6(id,age,name) values(3,22,'서길동');

select * from t6;

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

 

 

 

 

 FOREIGN KEY

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

<연습>

T1(부모 테이블)
ID(INT, P.K)	JOB(VARCHAR(30), UNIQUE)	SALES(INT)
1	IT	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);
select * from t1;

 

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

create table t(
	id int primary key,
    name varchar(30),
    age int,
    addr varchar(30),
    job varchar(30),
    constraint t1_job_t_job foreign key(job) references t1(job));
    
    insert into t values(1,'홍길동',40,'부산','미용');
    insert into t values(2,'서길동',40,'광주','야구');
    insert into t values(3,'남길동',50,'인천','야구');
    insert into t values(4,'북길동',70,'부산','축구');
    insert into t values(5,'이순신',50,'서울','아이티'); -에러 
    => T1의 테이블에 아이티의 값을 가진 JOB이 없습니다.

FOREIGN KEY -    DELETE CASCADE

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 t values(1,'홍길동',40,'부산','미용');
insert into t values(2,'서길동',40,'광주','야구');
insert into t values(3,'남길동',50,'인천','야구');
insert into t values(4,'북길동',70,'부산','축구');
    
delete from t1 where id=2;
select * from t1;
select * from t;

2번인 미용의 테이블을 지웠고, 외래키와 연관된 값을 가진 테이블의 행은 지워지게 설정되었습니다. 

 

 

 

 

 

 

 

 

 

 

FOREIGN KEY -    UPDATE CASCADE

UPDATE CASCADE  조건을 걸어주면 DELETE할수 없게 됩니다. 

부모테이블에서 업데이트 되면 자식 테이블은 자동으로

drop table t2;
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,'서길동',40,'광주','야구');
insert into t2 values(3,'남길동',50,'인천','야구');
insert into t2 values(4,'북길동',70,'부산','축구');
insert into t values(5,'이순신',50,'서울','아이티');

update t1 set job='IT03' where id=3;

T2 의 결과