3:56

1. 제약조건

데이터 무결성을 목적으로 SQL에서는 몇 가지 제약조건을 제공하고 있음.

1) PRIMARY KEY (기본키)

기본키는 데이터를 구분할 수 있게 하는 식별자로 중복,NULL값 입력이 불가능함. 한 테이블에 하나의 기본키를 설정할 수 있음. (두 개 이상의 기본키 지정 불가.)

- 테이블을 만들면서 기본키를 지정

CREATE TABLE table1
(column1 CHAR(8) NOT NULL
...
PRIMARY KEY (column1)
); --column1이 기본키로 지정됨

CREATE TABLE table2
(column2 CHAR(8) NOT NULL PRIMARY KEY,
...
);
-- column2가 기본키로 지정됨

- 테이블을 만든 후에 사후적으로 기본키를 지정

ALTER RABLE table3 ADD CONSTRAINT PRIMARY KEY (column3);
-- column3을 기본키로 지정

2) FOREIGN KEY (외래키)

외래키는 두 테이블 사이의 관계를 연결해줌으로서 데이터의 무결성을 보장함. 외래키가 설정된 열은 다른 테이블의 기본키와 연결되며, 기본키가 있는 테이블과 외래키로 연결된 테이블은 기준테이블과 참조테이블의 관계를 가지게 됨. (참조 테이블의 외래키는 항상 기준 테이블의 기본키로 존재함. 고유키도 가능)

- 테이블을 만들면서 외래키를 지정

CREATE TABLE table1
(...
FOREIGN KEY (column1) REFERENCES member(column1)
);

- 테이블을 만든 후에 사후적으로 외래키를 지정

CREATE table2 buy 
(...); -- 테이블을 만듦.

ALTER TABLE table2
ADD CONSTRAINT
FOREIGN KEY(column2)
REFERENCES tabl3(column2); -- table2가 table3의 기본키를 참조하는 외래키 column2를 가짐.

cf) 기본키-외래키 관계가 정해진 후 기준 테이블의 기본키 열 이름 변경

참조 테이블에 해당 열의 데이터가 존재하는 경우 기준테이블만 단독적으로 열 이름을 변경할 수 없으며 참조 테이블의 열 이름도 함께 바뀌어야 함. 이때 ON UPDATE/DELETE CASCADE() 문을 사용.

-- table4는 참조테이블, table5는 기준테이블

ALTER TABLE table4
ADD CONSTRAINT
FOREIGN KEY(column4)
REFERENCES table5(column4)
ON UPDATE CASCADE
ON DELETE CASCADE;

3) UNIQUE (고유키)

고유키는 중복되지 않는 유일한 값을 입력해야 하는 조건으로, 기본키와 유사해보이나 기본키와 다르게 NULL값을 허용하고 한 테이블에 여러개의 고유키를 사용할 수 있음.

CREATE TABLE table1
( ...
email CHAR(30) NULL UNIQUE
);

4) CHECK (조건)

체크는 입력되는 데이터가 특정 조건에 부합하는지 점검함.

CREATE TABLE member
( ...
height TINYINT UNSIGNED NULL CHECK (height>=100),
...
);
-- 멤버의 키 데이터가 100 이상일때만 입력을 받음.

ALTER TABLE member
ADD CONSTRAINT
CHECK (phonenumber1 IN (‘02’,‘031’,‘032’,‘054’,...));
-- 멤버의 전화번호 데이터가 특정 입력값일때만 입력을 허용함.

5) DEFAULT (기본값)

디폴트는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 저장해 놓는 방법임.

CREATE TABLE member
(
...
height TINYINT UNSIGNED NULL DEFAULT 160
);
-- 사용자가 height 값을 입력하지 않을시 자동으로 160으로 설정함.

ALTER TABLE member
	ALTER COLUMN phonenumber1 SET DEFAULT ‘02’;

INSERT INTO member VALUES (... , ... , DEFAULT);
-- 사용자가 번호를 입력하지 않을시 자동으로 02로 설정함. 이때 해당 입력값에 'DEFAULT'를 입력해줘야 정상적으로 지정됨.

2. 뷰

뷰는 테이블과 유사하나 테이블처럼 실제로 데이터를 가지고 있지는 않으며, 사용자가 원하는 방식으로 테이블의 내용을 보여주는 일종의 가상 테이블임. 데이터 보안과 간결한 SQL문을 목적으로 사용함.

1) 생성 및 조회

CREATE VIEW view1
AS
SELECT column1, column2, column3 FROM table;

SELECT *FROM view1;

2) 수정

CREATE VIEW view1
AS
SELECT column1, column2, column3 FROM table;


ALTER VIEW view1
AS
SELECT column4, column5 FROM table1;

해당 코드를 수행하면 view1은 table1의 column1, 2, 3을 참조하는 것에서 4, 5를 참조하는 것으로 바뀜.

3) 삭제

DROP VIEW v_viewtest1;

DROP VIEW view1;

4) 생성 정보 확인

DESCRIBE view2; -- 생성 정보 확인 가능
SHOW CREATE VIEW view2; -- 소스코드 확인 가능

cf) DESCRIBE로 PREMARY KEY 등의 정보는 알 수 없음

5) 뷰를 이용한 테이블 데이터 변경

① 입력

INSERT INTO 
v_member(mem_id, mem_name, mem_number, addr) 
VALUES ('HONG','홍길동',1,'서울'); -- member테이블의 view v_member에 데이터 입력

SELECT * FROM member; -- 조회하면 테이블에서도 해당 데이터 확인 가능

② 수정

UPDATE v_member SET addr='인천' where mem_id='HONG';

③ 삭제

DELETE FROM member WHERE mem_number<10;

cf) VIEW 생성시 WHERE문을 이용하여 특정 조건만 테이블에서 가져오도록 설정이 가능

cf) VIEW 생성시 'WITH CHECK OPTION' 을 SELECT문 뒤에 입력하여 해당 조건에 어긋나면 INSERT를 받지 못하도록 설정이 가능

cf) 뷰가 참조하는 테이블이 삭제되면 해당 VIEW도 조회 불가하며  'CHECK TABLE view;'로 view가 참조하는 테이블의 상태를 확인할 수 있음.