[MySQL] 테이블, 뷰, 제약조건

1. 테이블

테이블은 행과 열로 구성되어있다. (행 : 로우, 열 : 레코드)

 

2. 테이블 만들기

CREATE TABLE sample_table (num INT);

* 열은 1개 이상 지정해야한다.

 

CREATE TABLE member
(	mem_id 		CHAR(8) 	NOT NULL	PRIMARY KEY, 	--PK
	mem_name 	VARCHAR(10) NOT NULL,
    	mem_number	TINYINT 	NOT NULL,
    	addr		CHAR(2) 	NOT NULL,
    	phone1		CHAR(3)		NULL,
    	phone2		CHAR(8)		NULL,
    	height		TINYINT UNSIGNED NULL,
    	debut_date	DATE NULL
);

CREATE TABEL buy
(
	num 		INT	AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    mem_id		CHAR(8) NOT NULL,
    prod_name	CHAR(6) NOT NULL,
    group_name	CHAR(4) NULL, 
    price		INT UNSIGNED NOT NULL, 
    amount		SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

 

3. SQL 테이블 제약조건(기본키, 외래키, 고유키)

* 제약조건으로 테이블을 견고하게

* 데이터가 완전 무결하도록 

제약조건은 기본키, 외래키, 고유키, 체크, 기본값, NOT NULL등이 있다.

 

4. 제약조건의 기본 개념과 종류

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다.

중요한 데이터의 중복이 발생하지 않도록 데이터의 무결성을 보장하기 위해 조건을 설정한다.

 

5. PRIMARY KEY

데이터의 행을 구분할 수 있는 식별자

기본 키에 입력되는 값은 중복될 수 없고, NULL도 불가하다.

테이블은 기본키를 1개만 가질 수 있다. 

 

* CREATE TABLE 문에서 설정하는 기본키 제약조건

CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL
);

CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL,
    mem_name VARCHAR(10) NOT NULL,
    PRIMARY KEY(mem_id)
);

* ALTER TABLE 문에서 설정하는 기본키 제약조건

CREATE TABLE member 
...

ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY(mem_id);

 

 

6. 외래키 제약조건

두 테이블 사이의 관계를 연결해주고, 데이터의 무결성을 보장해주는 역할

기본키가 있는 테이블을 기준 테이블이라하고,

외래키가 있는 테이블을 참조 테이블이라한다.

CREATE TABEL buy
(
	num 		INT	AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    mem_id		CHAR(8) NOT NULL,
    ...
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

* 여기서 member 테이블이 기준테이블, buy 테이블이 참조테이블이 된다.

 

CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL
);

CREATE TABLE buy
(
	num INT AUTO_INCREMENT PRIMARY KEY,
    mem_id  CHAR(8) NOT NULL,
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

 

* PK와 FK의 열이름은 동일하게 사용하는 것을 권장

 

Q. 기준 테이블의 열 데이터가 바뀐다면?

UPDATE member
SET		mem_id = 'PINK'
WHERE	mem_id = 'BLK';

-> 위 update 쿼리를 실행하면 이미 연결관계가 있기 때문에 변경할 수 없다는 에러 발생한다.

error code 1451. cannot delete or update a parent row a foreign key constraint fails

* Delete 도 불가능하다

 

 

ON UPDATE CASCADE

ON DELETE CASCADE

* 기준테이블이 변경될때 참조테이블도 자동으로 변경될 수 있도록 하는 기능

ALTER TABLE buy
	ADD CONTRAINT
	FOREIGN KEY(mem_id) REFERENCES membr(mem_id)
	ON UPDATE CASCADE
	ON DELETE CASCADE;

 

7. 기타 제약조건

 1) 고유 키 제약조건

중복되지 않는 유일한 값을 입력해야함. 

기본키와 비슷하지만 고유키는 NULL을 허용한다.

ex) 회원테이블에서 Email 주소가 중복되지않도록

CREATE TABLE member
(
    mem_id 		CHAR(8) NOT NULL PRIMARY KEY,
    mem_name 	VARCHAR(10) NOT NULL,
    email 		CHAR(8) NULL UNIQUE
);

 

2) 체크 제약조건

데이터를 입력할 때 값을 점검하는 기능

CREATE TABLE member
(
    mem_id 		CHAR(8) NOT NULL PRIMARY KEY,
    mem_name 	VARCHAR(10)	NOT NULL,
    height		TINYINT UNSIGNED NULL CHECK(height >= 100)
);

ALTER TABLE member
 ADD CONSTRAINT
 CHECK( phone1 IN ('02', '031', '032', '054'));

 

3) 기본값 정의

값을 입력하지 않았을 때의 기본값

CREATE TABLE member
( 
    mem_id CHAR(8) NOT NULL PRIMARY KEY,
    height TINYINT UNSIGNED NULL DEFAULT 150,
    phone1 CHAR(3) NULL
);

ALTER TABLE member
	ALTER COLUMN phone1 SET DEFAULT '02';