무결성 제약조건

무결성 제약조건은 잘못된 자료의 입력을 막고자 테이블 작성시 다양한 입력 제한 조건을 줄 수 있다.
1) Domain 제약조건 : 칼럼의 이름, 성격(type), 크기. null 허용여부...
2) 기본키 제약조건 : primary key
3) 사용자 정의 제약조건 : check, unique, foreing key...

기본키 제약조건 : primary key - 중복 자료(record) 입력 방지, null 허용 금지

방법1) 가장 일반적임

CREATE TABLE aa(bun INT PRIMARY KEY, irum CHAR(10) NOT NULL);
SHOW INDEX FROM aa;
SELECT*FROM informaion_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME ='aa';
DESC aa;
INSERT INTO aa VALUES(1,'aa');
INSERT INTO aa VALUES(1,NULL); -- err : irum NOT NULL, bun은 pk

방법2)

CREATE TABLE aa(bun INT, irum CHAR(10) NOT NULL, CONSTRAINT aa_bun PRIMARY KEY (bun)); -- 뒤에 제약조건 입력
INSERT INTO aa VALUES(1,aa);

방법3) pk없이 테이블이 만들어진 상태에서 나중에 추가

ALTER TABLE aa ADD CONSTRAINT aa_bun PRIMARY KEY (bun);

사용자 정의 제약조건
check : 입력되는 자료의 특정 컬럼 값 검사

CREATE TABLE aa(bun INT, irum CHAR(10), nai int CHECK(nai>=20));
INSERT INTO aa VALUES(1,'tom',33);
INSERT INTO aa VALUES(2,'tom2',13); -- err : check에 걸림
-- 참고 : check 없이 테이블을 만든 후
-- alter table add constraint ck_nai check(nai>=20); 기존 자료를 검사

외부키(참조키, foreign key, fk)
테이블 작성 시 다른 테이블에 칼럼값을 참조 (fk 대상은 pk)
on delete cascade : 참조 테이블의 행이 삭제되는 경우 현재 테이블의 행도 함께 삭제

CREATE TABLE sawon(bun INT PRIMARY KEY, irum VARCHAR(10), buser CHAR(10));
INSERT INTO sawon VALUES(1,'한송이','인사과');
INSERT INTO sawon VALUES(2,'박치기','인사과');
INSERT INTO sawon VALUES(3,'한송이','영업과');
SELECTFROM sawon;
CREATE TABLE gajok(CODE INT PRIMARY KEY, NAME VARCHAR(10), birth DATETIME, sawon_bun INT, FOREIGN KEY(sawon_bun) REFERENCES sawon(bun));
-- fk sawon_bun이 pk bun 참조
DESC gajok;
INSERT INTO gajok VALUES(100,'가나다',NOW(),1); -- oracle에서는 now가 sysdate
INSERT INTO gajok VALUES(101,'한가해','2001-12-12',1);
INSERT INTO gajok VALUES(102,'한국인','2011-2-12',3);
INSERT INTO gajok VALUES(102,'한국인','2011-2-12',10); -- err : 10 사원은 없기때문에
SELECTFROM gajok;
DELETE from sawon WHERE bun=1;
-- err : 가족테이블이 있기때문에 지워질 수 없음, on delete cascade 쓰면 사원과 그의 관련된 가족도 지워짐
DELETE FROM gajok WHERE sawon_bun = 1;
SELECTFROM gajok;
DELETE from sawon WHERE bun=1; -- success : 가족을 먼저 삭제한 후 작업
SELECTFROM sawon;
DROP TABLE sawon; -- err : 사원테이블을 참조하는 가족 테이블이 있기 때문
DROP TABLE gajok;
DROP TABLE sawon;
SHOW TABLES;

출력

default : 특정 칼럼에 초기값 부여. null 입력을 방지하기 위함

CREATE TABLE aa (bun INT, irum CHAR(10), juso CHAR(50));
ALTER TABLE aa ALTER COLUMN juso SET DEFAULT '역삼동';
INSERT INTO aa VALUES(1,'tom','서초동');
INSERT INTO aa(bun,irum) VALUES(2,'oscar'); -- 주소에 DEFAULT값 들어감
INSERT INTO aa VALUES(3,'tom',DEFAULT);
INSERT INTO aa VALUES(4,'tom',NULL);
SELECT*FROM aa;

출력

CREATE TABLE aa (bun INT AUTO_INCREMENT primary KEY, irum CHAR(10), juso CHAR(50) DEFAULT '여의도동');
-- AUTO_INCREMENT primary KEY : pk 자동증가 / Ms-SQL은 autoincrement, oracle은 sequence 객체를 별도 작성
INSERT INTO aa VALUES(1,'tom',DEFAULT);
INSERT INTO aa (irum) VALUES('oscar'); -- 번호 안줘도 자동증가, 주소 DEFAULT값
INSERT INTO aa VALUES(0,'tom',DEFAULT); -- 0을 넣어도 번호 자동 증가
INSERT INTO aa VALUES(null,'tom',DEFAULT); -- null을 넣어도 번호 자동 증가
ALTER TABLE aa AUTO_INCREMENT = 100;
INSERT INTO aa (irum) VALUES('sujan');
INSERT INTO aa (irum) VALUES('page');
SELECT*FROM aa;

출력

제약조건 연습 문제

CREATE TABLE 교수 (교수코드 INT PRIMARY KEY, 교수명 VARCHAR(10), 연구실 INT CHECK (연구실 >=100 and 연구실 <=500));
-- CREATE TABLE 교수 (교수코드 INT PRIMARY KEY, 교수명 VARCHAR(10), 연구실 INT CHECK)
-- ALTER TABLE 교수 ADD CONSTRAINT CHECK (연구실>=100 AND 연구실 <=500);
SELECT FROM 교수;
INSERT INTO 교수 VALUES(1, '가가가', 101);
INSERT INTO 교수 VALUES(2, '나나나', 505); -- err
INSERT INTO 교수 VALUES(2, '나나나', 102);
INSERT INTO 교수 VALUES(3, '다다다', 103);
INSERT INTO 교수 VALUES(4, '다다다', 104);
SELECT FROM 교수;

CREATE TABLE 과목 (과목코드 INT AUTO_INCREMENT PRIMARY KEY, 과목명 VARCHAR(10) UNIQUE, 담당교수 INT,
FOREIGN KEY(담당교수) REFERENCES 교수(교수코드)) CHARSET=UTF8;
INSERT INTO 과목 (과목명,담당교수) VALUES('자바',1);
INSERT INTO 과목 (과목명,담당교수) VALUES('DB',2);
INSERT INTO 과목 (과목명,담당교수) VALUES('데이터분석',2);
SELECT * FROM 과목;

CREATE TABLE 학생(학번 INT PRIMARY KEY, 학생명 VARCHAR(10), 수강과목 INT, 학년 INT DEFAULT 1,
FOREIGN KEY(수강과목) REFERENCES 과목(과목코드)) CHARSET=UTF8;
ALTER TABLE 학생 ADD CONSTRAINT CHECK (학년 >=1 AND 학년<4);
INSERT INTO 학생 VALUES ( 111,'오징어',1,2);
INSERT INTO 학생 (학번,학생명,수강과목) VALUES (222,'꼴뚜기',1);
SELECT * FROM 학생;

삭제는 역순으로

DROP TABLE 학생;
DROP TABLE 과목;
DROP TABLE 교수;