constraint

use information_schema;
show tables;
select * from TABLE_CONSTRAINTS tc ;

use lecture;
desc student;
drop table student; #student table 삭제
where 
create table member(
  sno int primary key
);

#컬럼을 지정하면서 제약 조건 지정
create table student(
  sno    int primary key,
  mName  varchar(20) not null,
  phone  varchar(30) unique,
  address varchar(50) default 'seoul',
  gender varchar(2) check(gender in('m','f')),
  score int check(score between 0 and 100),
  
   foreign key(zipcode) references member(sno)
 );

#지정된 무결성 테스트
insert into student(sno, mName) values(100,'hong');
select* from student;

#외래키 체크 전 부모 테이블(참조되는 테이블) 값 입력
insert into member values(100);
select * from member;

#외래키 체크
insert into student(sno, mName, zipcode)
values(200, 'kim', 200); #member 테이블에 값이 없어서 입력되지 않음
select * from student;


#중복값 입력 테스트
insert into student(sno, mName, zipcode)
values(200, 'lee', 100)

#성별('m', 'f')이외의 값 입력 테스트
insert into student(sno, mName, zipcode, gender)
values(300, 'park', 100, 'x');

#score의 값이 0~100만을 허용하는지 테스트
insert into student(sno, mName, zipcode, gender, score)
values(400,'lee',100,'f',30);
select * from student;

#동일한 phone값의 입력 테스트
insert into student(sno, mName, zipcode, gender, score, phone)
values(500, 'hong', 100, 'm', 90, '010');
insert into student(sno, mName, zipcode, gender, score, phone)
values(600, 'hong', 100, 'm', 90, '010'); #010이 중복되서 입력되지 않음.

#student에 걸려 있는 제약 조건을 확인
use information_schema;
select * from TABLE_CONSTRAINTS where table_name='student';

use lecture;
drop table student;

#제약 조건 설정 type2
create table student(
 sno int,
 mName varchar(20) not null,
 phone varchar(30),
 address varchar(50) default '서울',
 gender varchar(2),
 
 zipcode int,
 score int,
 
 constraint std_sno_pk primary key(sno),
 constraint std_phone_uk unique(phone),
 constraint std_gender_ck check(gender in ('m','f')),
 constraint std_score_ck check(score>=0 and score<=100),
 constraint std_zipcode_fk foreign key(zipcode) references member(sno)
);

#student에 지정된 제약 조건을 확인
use information_schema;
show tables;
select * from table_constraints where table_name='student';

use lecture;
#student 테이블에 지정되어 있는 제약 조건을 모두 삭제
#pk
alter table student drop primary key;

#uk
alter table student drop index std_phone_uk;

#ck
alter table student drop constraint std_gender_ck;
alter table student drop constraint std_score_ck;

#fk
alter table student drop foreign key std_zipcode_fk;

#not null->null 
alter table student modify column mName varchar(20) null;

#default -> null 
alter table student alter column address set default null;

# 테이블 생성 후 제약조건 추가
#pk
alter table student add constraint std_sno_pk primary key(sno);
insert into student(sno) values(100);
select * from student;

#uk
insert into student(sno, phone) values(200, '010');
insert into student(sno, phone) values(300,'010');
select * from student;
alter table student add constraint std_phone_uk unique(phone); #error
delete from student where sno=300; #중복 제거
alter table student add constraint std_phone_uk unique(phone); #success
insert into student(sno, phone) values(300,'010'); #error

#ck
alter table student add constraint std_gender_ck check(gender in('m','f'));
alter table student add constraint std_score_ck check(score between 0 and 100);

#fk
alter table student add constraint std_zipcode_fk foreign key(zipcode)
references member(sno);

#null -> not null
update student set mName='hong';
alter table student modify column mName varchar(20) not null;

#default '서울'
alter table student alter column address default '서울';

#student에 지정된 제약조건 확인
use information_schema;
select* from table_constraints tc where table_name='student';
desc lecture.student;