[SQL] 제약조건

데이터 무결성을 위해 테이블에 제약조건을 설정한다.

 

💥 제약조건

PRIMARY KEY 제약

• 기본키 설정하는 제약조건
• 기본키는 테이블당 한 개여야 한다.
• 기본키는 NOT NULL 이어야 한다.

UNIQUE 제약

• NULL 값 허용, 컬럼에 유일한 값을 넣는다.
• 자동으로 인덱스 생성

FOREIGN KEY 제약

• 외래키 설정하는 제약조건
• 참조하는 테이블의 기본키이거나 NULL이어야 한다.

 

💨 무결성

➡️ 개체 무결성

: 기본키 컬럼에 중복된 값 X, NULL값 X

 

➡️ 참조 무결성

: 외래키는 참조되는 테이블의 행을 참조하는 외래키가 존재하는 한 삭제될 수 없고 기본키 변경될 수 없다.

 

 

 

- 제약조건 추가해서 테이블 생성;

create table 학과
(
학과번호 char(2) primary key
, 학과명 varchar(20) not null
, 학과장명 varchar(20)
);

- 학과 테이블의 학과번호를 외래키로 가지는 테이블 생성;

create table 학생
(
학번 char(5) primary key
, 이름  varchar(20) not null
, 생일 date not null
, 연락처 varchar(20) unique
, 학과번호 char(2) references 학과(학과번호) 
, 성별 char(1) check(성별 in ('남','여'))
, 등록일 date default(curdate())
, foreign key(학과번호) references 학과(학과번호)
);

create table 과목
(
과목번호 char(5) primary key
, 과목명 varchar(20) not null
, 학점 int not null check(학점 between 2 and 4)
, 구분 varchar(20) check(구분 in('전공','교양','일반'))
);

- 일부러 primary key를 여러 개 지정 : 4개가 기본키로 지정되어 하나로 묶임(이 4개의 값이 값이면 같은 걸로 인식함);

create table 수강_1
(
수강년도 char(4) not null
, 수강학기 varchar(20) not null check(수강학기 in('1학기','2학기', '여름학기', '겨울학기'))
, 학번 char(5) not null  references 학생(학번)
, 과목번호 char(5) not null references 과목(과목번호)
, 성적 numeric(3,1) check(성적 between 0 and 4.5)
, primary key(수강년도, 수강학기, 학번, 과목번호)
, foreign key (학번) references 학생(학번)
, foreign key (과목번호) references 과목(과목번호)
);

 

- 수강_1 테이블에 데이터 삽입;

insert into 수강_1(수강년도, 수강학기, 학번, 과목번호, 성적)
values('2023', '1학기', 'S0001', 'C0001', 4.3);


// 에러발생 기본키로 지정한 것들이 위에 삽입한 내용과 똑같기 때문에 하나로 인식한다.;
insert into 수강_1(수강년도, 수강학기, 학번, 과목번호, 성적)
values('2023', '1학기', 'S0001', 'C0001', 4.5);

insert into 수강_1(수강년도, 수강학기, 학번, 과목번호, 성적)
values('2023', '1학기', 'S0001', 'C0002', 4.4);

insert into 수강_1(수강년도, 수강학기, 학번, 과목번호, 성적)
values('2023', '1학기', 'S0002', 'C0002', 4.3);

- auto_increment : 자동으로 생성;

create table 수강_2
(
수강번호 int primary key auto_increment
,수강년도 char(4) not null
, 수강학기 varchar(20) not null check(수강학기 in('1학기','2학기', '여름학기', '겨울학기'))
, 학번 char(5) not null 
, 과목번호 char(5) not null
, 성적 numeric(3,1) check(성적 between 0 and 4.5)
, foreign key (학번) references 학생(학번)
, foreign key (과목번호) references 과목(과목번호)
);

 - 학과 테이블 데이터 삽입;

insert into 학과
values('AA', '컴퓨터공학과', '배경민');

insert into 학과
values('BB', '소프트웨어학과', '김남준');

insert into 학과
values('CC', '디자인융합학과', '박선영');

- 학생 테이블 데이터 삽입;

insert into 학생(학번, 이름, 생일, 학과번호)
values('S0001', '이윤주', '2020-01-30', 'AA');

insert into 학생(학번, 이름, 생일, 학과번호)
values('S0003', '백재용', '2018-03-31', 'CC');

- (error) 학번은 primary key -> not null, 반드시 지정;

insert into 학생(이름, 생일, 학과번호)
values('이승은', '2021-02-23', 'BB');

- 과목 테이블 데이터 삽입;

insert into 과목(과목번호, 과목명, 구분, 학점)
values('C0001', '데이터베이스실습', '전공', 3);

insert into 과목(과목번호, 과목명, 구분, 학점)
values('C0002', '데이터베이스 설계와 구축', '전공', 3);

insert into 과목(과목번호, 과목명, 구분, 학점)
values('C0003', '데이터 분석', '전공', 3);

 

- 수강_2 테이블에 데이터 삽입;

insert into 수강_2(수강년도, 수강학기, 학번, 과목번호, 성적)
values('2023', '1학기', 'S0001', 'C0001', 4.3);

insert into 수강_2(수강년도, 수강학기, 학번, 과목번호, 성적)
values('2023', '1학기', 'S0001', 'C0001', 4.5);

- 제약조건 추가;

alter table 학생 add constraint check(학번 like 'S%');

- 학생 테이블 제약조건 명세 확인;

select * 
from information_schema.table_constraints
where constraint_schema = '한빛박사' and table_name = '학생';

- 제약조건 삭제;

alter table 학생 drop constraint 연락처;

- check 제약조건 삭제;

alter table 학생 drop constraint 학생_chk_1;
alter table 학생 drop constraint 학생_chk_2;

- check 제약조건 다시 추가;

alter table 학생 add check (학번 like 'S%');

 

-  제약조건명 지정;

create table 학생_2
(
학번 char(5) primary key
, 이름  varchar(20) not null
, 생일 date not null
, 연락처 varchar(20) 
, 학과번호 char(2)
, 성별 char(1) )
, 등록일 date default(curdate())
, constraint UK_학생2_연락처 unique(연락처)
, constraint CK_학생2_성별 check(성별 in ('남','여')
, constraint FK_학생2_학과번호 foreign key(학과번호) references 학과(학과번호)
);

- 외래키 제약조건 옵션(1) 연쇄삭제 : casecade;

create table 수강평가
(
평가순번 int primary key auto_increment
, 학번 char(5) not null
, 과목번호 char(5) not null
, 평점 int check(평점 between 0 and 5)
, 과목평가 varchar(50)
, 평가일시 datetime default current_timestamp
, foreign key (학번) references 학생(학번)
, foreign key (과목번호) references 과목(과목번호) on delete cascade
);

insert into 수강평가(학번, 과목번호, 평점, 과목평가)
values('S0001','C0001', 5,'SQL학습에 도움이 되었습니다.')
,('S0001','C0003', 5,'SQL활용을 배워좋았습니다.')
,('S0002','C0003', 5,'데이터분석에 관심이 생겼습니다..')
,('S0003','C0003', 5,'머신러닝과 시각화 부분이 유용했습니다.');

과목 테이블에서 삭제한 과목번호가 수강평가에서도 연쇄삭제;
delete from 과목 where 과목번호 = 'C0003';

(error) 'C0001'은 참조되어 있기 때문에 - 자식레코드가 있어서 부모 레코드를 삭제할 수 없음;
delete from 과목 where 과목번호 = 'C0001';