CREATE TABLE department(
_id INT PRIMARY KEY AUTO_INCREMENT,
department_code CHAR(5) NOT NULL
);
INSERT INTO department(department_code) SELECT belong FROM student ORDER BY belong;
INSERT INTO 테이블(컬럼명) + 넣을 데이터
위의 결과에서 중복된 데이터 제거
DELETE FROM department;
INSERT INTO department(department_code)
SELECT DISTINCT(belong) FROM student
ORDER BY belong;
데이터를 삭제 후 DISTINCT(컬럼명)을 통해 중복되지 않은 데이터만으로 컬럼 구성
CREATE TABLE department2(department_code CHAR(5) PRIMARY KEY)
AS SELECT DISTINCT(belong) AS department_code FROM student ORDER BY belong;
SELECT * FROM department2;
department2 테이블 생성
AS를 통해 테이블 생성 직후 SELECT로 가져온 중복되지 않은 데이터를 바로 추가 할 수 있다. 중복을 제거 했기 때문에 department_code는 유일한 값이 되어서 department2 테이블의 기본키는 department_code가 된다.
student 테이블의 belong이 department 테이블의 department_code를 참조하도록 설정
ALTER TABLE student
ADD CONSTRAINT student_department
FOREIGN KEY(belong)
REFERENCES department2(department_code)
ON UPDATE CASCADE ON DELETE CASCADE;
DESC student;
CONSTRAINT 제약명 : 제약명의 제약 지정
FOREIGN KEY(컬럼명) : 컬럼을 외래키로 지정
REFERENCES 테이블(컬럼) : 지정한 테이블의 컬럼을 참조
CASCADE
참조 관계(기본키와 외래키 관계)가 있을 경우 참조되는 데이터도 자동으로 삭제 가능
자신이 참조하고 있는 테이블의 데이터가 삭제되면 자동으로 자신의 데이터도 삭제
CASCADE 사용시 참조 무결성을 준수할 수 있다.
DB의 값을 수정/삭제할 때, 해당 값을 참조하고 있는 레코드 역시 종속적으로 수정/삭제를 가능하게 해준다.
ON UPDATE CASCADE : 참조 무결성을 유지하며 PK값을 수정 할 수 있다.
ON DELETE CASCADE : 해당 값을 참조 중인 레코드들을 모두 종속적으로 삭제
department2 테이블의 department_code가 바뀌면 belong도 바뀌고, 삭제가 되면 함께 삭제 된다.
department2 테이블의 값을 바꾸면 student 테이블의 belong도 함께 수정된다.(종속)
UPDATE department2 SET department_code = 'CCE' WHERE department_code = 'IME';
SELECT * FROM student;
belong이 MSE인 데이터 삭제
DELETE FROM department2 WHERE department_code = 'MSE';
SELECT COUNT(*) FROM student;
참조의 무결
department2의 테이블에서 MSE가 제거가 되었고, student(belong)에는 여전히 5개의 값을 가지고 있는 상태에서 department2(department_code)를 외래키로 지정할 때 오류가 발생한다.
그 이유는 참조하는 테이블에 없는 값을 가지고 있기 때문이다.
외래키로 설정한 값이 참조하는 테이블의 속성 없으면 안된다는 규칙이다.
외래키 제거
DELETE FROM department2;
ALTER TABLE student
DROP FOREIGN KEY student_department,
DROP KEY student_department;
DROP FOREIGN KEY : 외래키 제약조건 제거
DESC 또는 SHOW CREATE TABLE로 보면 여전히 키로 명시되어있다.
DROP KEY : KEY 제거
1NF
CREATE TABLE toy
(
toy_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(12) NOT NULL,
colors VARCHAR(32)
);
INSERT INTO toy (name, colors)
VALUES ('kite', 'red, pink, blue');
INSERT INTO toy (name, colors)
VALUES ('yoyo', 'red, blue');
INSERT INTO toy (name, colors)
VALUES ('doll', 'pink, red');
열에 여러값이 있기 때문에 1정규화가 되어 있지 않다.
제 1정규화 규칙
1. 원자적 데이터로 구성된 열은 그 열에 같은 타입의 데이터를 여러개 가질 수 없다.
2. 원자적 데이터로 구성된 테이블은 같은 타입의 데이터를 여러개 가질 수 없다.
ALTER TABLE toy
ADD COLUMN color1 CHAR(6),
ADD COLUMN color2 CHAR(6),
ADD COLUMN color3 CHAR(6);
UPDATE toy
SET color1 = SUBSTRING_INDEX(colors, ',', 1);
SELECT * from toy;
SUBSTRING_INDEX를 통해 ,기준으로 color1에 데이터 저장
UPDATE toy
SET colors = SUBSTRING(colors, LENGTH(color1)+3);
UPDATE toy
SET color2 = SUBSTRING_INDEX(colors, ',', 1);
UPDATE toy
SET colors = SUBSTRING(colors, LENGTH(color2)+3);
UPDATE toy
SET color3 = SUBSTRING_INDEX(colors, ',', 1);
UPDATE toy
SET colors = SUBSTRING(colors, LENGTH(color3)+3);
colors에 있는 데이터를 color1, color2, color3에 ,을 기준으로 나누어서 저장
모든 색상을 저장하기 위한 color테이블 생성
CREATE TABLE color
(
color_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(12),
rgb CHAR(6)
);
INSERT INTO color (name)
SELECT DISTINCT(color1)
FROM toy;
INSERT INTO color (name)
SELECT DISTINCT(color2)
FROM toy
WHERE color2 NOT IN (SELECT name FROM color);
INSERT INTO color (name)
SELECT DISTINCT(color3)
FROM toy
WHERE color3 NOT IN (SELECT name FROM color);
name에 color1에서 중복되지 않은 색깔을 넣어준다.
color테이블의 name 컬럼에서 없는 값 중 color2에 있는 값중 중복되지 않은 값을 name에 추가
color테이블의 name 컬럼에서 없는 값 중 color3에 있는 값중 중복되지 않은 값을 name에 추가
-- 토이 컬러 테이블 생성
CREATE TABLE toy_color
(
toy_id INT NOT NULL, -- 토이 ID 열, NULL 허용 안 함
color_id INT NOT NULL, -- 컬러 ID 열, NULL 허용 안 함
PRIMARY KEY(toy_id, color_id), -- 주 키 설정 (토이 ID, 컬러 ID)
CONSTRAINT c_toy_id -- 외래 키 제약 조건 이름 설정
FOREIGN KEY(toy_id) -- 외래 키 설정 (토이 ID)
REFERENCES toy(toy_id) -- 참조하는 테이블 및 열 (토이 테이블의 토이 ID)
ON UPDATE CASCADE ON DELETE CASCADE, -- 참조 무결성을 유지하기 위한 업데이트 및 삭제 규칙
CONSTRAINT c_color_id -- 외래 키 제약 조건 이름 설정
FOREIGN KEY(color_id) -- 외래 키 설정 (컬러 ID)
REFERENCES color(color_id) -- 참조하는 테이블 및 열 (컬러 테이블의 컬러 ID)
ON UPDATE CASCADE ON DELETE CASCADE -- 참조 무결성을 유지하기 위한 업데이트 및 삭제 규칙
);
-- 토이 컬러 테이블에 데이터 삽입
INSERT INTO toy_color (toy_id, color_id)
SELECT toy.toy_id, color.color_id
FROM toy, color
WHERE toy.color1 = color.name; -- 토이 테이블의 color1이 컬러 테이블의 name과 일치하는 데이터 삽입
INSERT INTO toy_color (toy_id, color_id)
SELECT toy.toy_id, color.color_id
FROM toy, color
WHERE toy.color2 = color.name; -- 토이 테이블의 color2가 컬러 테이블의 name과 일치하는 데이터 삽입
INSERT INTO toy_color (toy_id, color_id)
SELECT toy.toy_id, color.color_id
FROM toy, color
WHERE toy.color3 = color.name; -- 토이 테이블의 color3이 컬러 테이블의 name과 일치하는 데이터 삽입
-- 특정 토이 ID에 해당하는 토이 컬러 테이블의 데이터 조회
SELECT * FROM toy_color WHERE toy_id = 3;
