DB

[Relational database] 02. 관계형 모델 (Relational model) *제약조건

Joo.v7 2024. 10. 1. 20:49

Chapter 1: 관계 모델 개요

  • 관계 모델 개요
  • Dr.Codd의 12법칙
  • 관계 데이터베이스 시스템 역사 

Chapter 2: Relation

  • Relation Schema
  • Relation Instance
  • Domain
  • Relational Database

실습: SQL을 사용한 Relation 생성 및 수정 (MySQL)

Chapter 3: Integrity Constraint (제약 조건)

  • 무결성 제약 조건 개요 (Integrity Constraint)
  • 개체 무결성 (Entity Integrity)
  • 참조 무결성 (Referential Integrity)
  • 도메인 무결성 (Domain Integrity)
  • 무결성 제약조건 집행 (Integrity Constraint Enforce)

Chapter 4: Query 

  • Relational Database Query
  • SQL

Chapter 1: 관계 모델 개요

관계 모델 개요

  • 데이터 간의 관계를 정의하고 관계를 사용하여 DB를 Modeling하는 기법.
  • 1970년, IBM의 Dr. Codd가 제안한 모델.
  • 하나 이상의 Relation들의 집합으로, Relation은 row(행)와 column(열)을 가진 Table 형태로 표시됨.
  • 관계 논리식과 관계 대수식에 기반한 데이터 조회 및 제어.

Dr.Codd의 12법칙

참조: https://ko.wikipedia.org/wiki/%EC%BB%A4%EB%93%9C%EC%9D%98_12_%EA%B7%9C%EC%B9%99

 

커드의 12 규칙 - 위키백과, 우리 모두의 백과사전

위키백과, 우리 모두의 백과사전. 커드의 12 규칙(Codd's 12 rules)이란 어떤 데이터베이스가 "관계형 데이터베이스"라고 여겨지기 위한 조건을 나열한 13가지의 규칙을 말한다. 데이터베이스를 위한

ko.wikipedia.org

 

관계 데이터베이스 시스템 역사 

 

Chapter 2: Relation

 Relational model에서 데이터의 표현은 Relation으로 구성되며, Relatioan은 Relation Schema와 Relation Instance로 구성.

Relation Schema

  • DB의 데이터 구조와 제약조건에 대한 전반적인 명세를 의미 -> relation을 정의하는 청사진.
  • Relation의 name / Field, Column, Attribute의 name / 각 속성의 Domain을 명세.
  • Relation Schema를 기반으로 실제 데이터가 저장되는 Table -> Relation Instance.
    ex) 

Relation Instance

  • Schema에 따라 DB에 실제 저장된 값 (Tuple의 집합)
  • Cardinality: Relation Instance의 Tuple 수
  • Degree: Relation Instance의 attribute 수

Domain

  • 하나의 attribute가 가질 수 있는 동일한 유형의 원자들의 집합.
  • Relation Schema는 Relation Instance의 각 필드의 domain을 명세 -> Domain Constraint (도메인 제약조건)

 

Relational Database

  • Relational Database: 서로 다른 Relation 이름을 가진 Reltaions의 모임.
  • Relational Database Schema: DB에 속한 Relation의 Schema들의 집합.

 

실습: SQL을 사용한 Relation 생성 및 수정 (MySQL)

더보기 
# mysql 실행.
$ mysql -U root -P

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# DB 생성.
mysql> CREATE DATABASE Module02;
Query OK, 1 row affected (0.04 sec)

# Module02 DB를 사용하겠다.
mysql> use Module02;
Database changed

# 현재 접속된 DB를 보여줌
mysql> select database();
+------------+
| database() |
+------------+
| module02   |
+------------+
1 row in set (0.00 sec)

# 현재 DB의 Tables를 보여줌.
mysql> show tables;
Empty set (x.xx sec)

# Table 생성, Category Relation의 Schema 명세.
mysql> CREATE TABLE Category (
    -> CategoryNo INTEGER,
    -> CategoryName VARCHAR(20) # VARCHAR은 가변 길이 레코드
);
Query OK, 0 rows affected (x.xx sec)

mysql> show tables;
+--------------------+
| Tables_in_Module02 |
+--------------------+
| Category           |
+--------------------+
1 row in set (x.xx sec)

mysql> SELECT * FROM Category;
Empty set (x.xx sec)

# Category Table에 Tuple 넣기.
mysql> INSERT INTO Category VALUES (1, 'Novel');
Query OK, 1 row affected (x.xx sec)

mysql> INSERT INTO Category VALUES (2, 'Poem');
Query OK, 1 row affected (x.xx sec)

mysql> SELECT * FROM Category;
+-------------+--------------+
| CartegoryNo | CategoryName |
+-------------+--------------+
|           1 | Novel        |
|           2 | Poem         |
+-------------+--------------+
2 rows in set (x.xx sec)

# CategoryName을 VARCHAR(20)으로 해서 20이 넘어가면 오류.
mysql> INSERT INTO Category VALUES (3, 'History / Religion and Magazine');
ERROR 1406 (22001): Data too long for column 'CategoryName' at row 1

# Category Relation의 data 수정.
mysql> UPDATE Category SET
-> CategoryName = 'History';
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0

# where문을 사용하지 않아서 CategoryName 전체가 바뀜.
mysql> SELECT * FROM Category;
+------------+--------------+
| CategoryNo | CategoryName |
+------------+--------------+
|          1 | History      |
|          2 | History      |
+------------+--------------+
2 rows in set (0.00 sec)

mysql> UPDATE Category SET
    -> CategoryName = 'Novel'
    -> WHERE CategoryNo = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Category SET
    -> CategoryNo = 3
    -> WHERE CategoryNo = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Category;
+------------+--------------+
| CategoryNo | CategoryName |
+------------+--------------+
|         1  | Novel        |
|         3  | History      |
+------------+--------------+

 

 

Chapter 3: Integrity Constraint (제약 조건)

무결성 제약 조건 개요 (Integrity Constraint)

 DB의 품질은 저장되는 data의 품질에 따라 결정된다. 따라서 DBMS는 부정확한 정보가 입력되는 것을 방지하는 수단이 필요하다.

  • Integrity Constraint
    • Database Schema에 명세(정의)되어 있는 조건으로, Database Instance에 저장될 수 있는 데이터를 제한함.
  • DBMS는 Integrity Constraint를 사용하여 Relational DB에 부정확한 data가 입력되는 것을 방지함.
  • DBMS는 Integrity Constraints를 enforce하여 DB를 접합하게 만듦.
  • Legal Instance: DB Schema에 명세된 Integrity Constraints를 모두 만족하는 DB Instance.
  • Relational Integrity Constraint는 크게 3가지로 정의.
    1. Domain Integrity -> Domain Constraint, data type 명세. ex) CategoryNo: Integer
    2. Entity Integrity -> Key Constraint, ex) PK Constraint, Unique
    3. Referential Integrity -> Foreign Key Constraint. - 참조하는 data가 존재해야 한다.

 

개체 무결성 (Entity Integrity)

  • 각 개체는 DB내에서 유일해야 한다. (data 중복 X)
    ex) 학생 (학번: 문자열, 이름: 문자열, 나이: 정수) -> 학생 relation에서 학생은 동일한 학번을 가질 수 X.
  • Entity Integrity을 위해 Key Constraint 사용. -> PK Constraint, Unique(중복값 X) -> 둘 다 개체 식별을 위해 사용.
  • Key
    • Key Constraint에서 Tuple을 유일하게 식별하는 부분집합.
    • Legal Instance의 서로 다른 Tuple들은 하나의 key에 대해 모든 필드 전체에서 동일한 값을 가질 수 없다.

회원(이름: 문자열, id: 문자열, pw: 문자열, 성별: (), e-mail: 문자열, age: 정수)
개체를 식별할 수 있는 부분집합(key)-> {이름 + id}, {이름 + id + pw}, ...

  • Super Key: 유일성을 만족하는, 필드들의 부분 집합 -> 위 예에서 key가 될 수 있는 부분 집합.
  • Candidate Key: 유일성과 최소성을 만족하는 Key -> 위 예에서 id, e-mail. 2개 나옴.
  • Primary Key: candidate key에서 선택된 고유한 식별자 -> id, e-mail 둘 중 하나 선택.

Entity Integrity를 위한 Key Constraint.

 

참조 무결성 (Referential Integrity)

  • Relation들은 각각 저장된 정보가 연결되어 있다. 이 참조 관계에 있는 relation들의 data는 항상 일관된 값으로 유지되어야함.
  • Referential Integrity를 위해 Foreign Key Constraint 사용.
  • Foreign Key
    • 한 relation의 key 중에서 다른 relation의 tuple을 유일하게 식별할 수 있는 key.(PK, UK)
    • 한 relation의 tuple에서 다른 relation의 tuple을 참조하기 위해 사용.

Relation Product와 Relation Category의 Reference 관계.

 

도메인 무결성 (Domain Integrity)

  • Relation의 각 attribute에는 범위에 지정된 데이터만 저장되어야 함.
  • Domain Integrity를 위해 Domain Constraint 사용.
  • Domain: Relation Instance의 각 필드에 저장될 수 있는 데이터의 범위. ex) '성별' domain에 '이름' 들어가면 X.  
    • 각 attribute의 값은 반드시 원자값이어야 한다.
    • attribute에 지정된 domain의 범위 내에 해당하는 값이어야 함.
    • attribute의 기본 값과 null 포함 가능 여부 등에 대한 제약조건 지정할 수 있다.

 

무결성 제약조건 집행 (Integrity Constraint Enforce)

  • Constraint는 Relation Schema에 정의되고, Relation의 수정 때 집행됨.
  • Domain Integrity와 Entity Integrity는 직관적으로 즉시 집행됨.
  • Referential Integrity
    1. 참조하는 릴레이션에 없는 값에 대한 삽입이 시도되는 경우. -> 명령 거부
    2. 참조 릴레이션의 데이터가 삭제되는 경우. -> (1) 모두 삭제, (2) 삭제 거부, (3) 다른 값으로 갱신.
    3. 참조하는 릴레이션의 데이터가 변경되는 경우. -> (1) 모두 변경, (2) 변경 거부, (3) 다른 값으로 갱신.

 

Chapter 4: Query 

Relational Database Query

  • 데이터에 대한 문의이며, 그 답은 질의에 대해 새롭게 구성된 Relation이다.

SQL (Structed Query Language)

  • RDBMS의 data를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어.
  • SQL 문법은 3가지로 구분된다.
    • DDM, Data Definition Language: Table과 Index 구조를 관리.
      • CREATE, ALTER, DROP ...
    • DML, Data Manipulation Language: Data 검색, 등록, 삭제, 갱신을 위해 사용.
      • SELECT, INSERT, UPDATE, DELETE
    • DCL, Data Control Language: DB에서 Data에 대한 access를 제어하기 위한 DB 언어.
      • GRANT, DENY, REVOKE

 

실습: SQL을 사용한 무결성 제약조건 설정 (MySQL)

 * Foreign Key Constraint 설정 시

  • ON DELETE/UPDATE SET NULL: 내가 삭제/변경되면 나를 참조하는 것들은 NULL로 갱신.
  • ON DELETE/UPDATE CASCADE: 내가 삭제/변경되면 나를 참조하는 것들도 삭제/변경.
  • ON DELETE NO ACTION: 내가 참조되고 있으면 삭제/변경 불가.
더보기 
$ mysql -U root -P

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Module02           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

SELECT database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use Module02;
Database changed

SELECT database();
+------------+
| database() |
+------------+
| Module02   |
+------------+
1 row in set (0.00 sec)


# 연습 1: Entity Constraint를 위한 PK 제약 조건 설정.


# Category Table의 schema 정보 확인.
mysql> desc Category;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CategoryNo   | int         | YES  |     | NULL    |       |
| CategoryName | varchar(20) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# Category Table의 CategoryNo column에 PK Constraint 추가.
mysql> ALTER TABLE Category ADD CONSTRAINT pk_Category PRIMARY KEY(CategoryNo);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Category Table의 schema 정보 다시 확인. -> PRI 적용됨.
mysql> desc Category;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CategoryNo   | int         | YES  | PRI | NUL     |       |
| CategoryName | varchar(20) | YES  |     | NUL     |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# Category Table의 data 확인.
mysql> SELECT * FROM Category;
+------------+--------------+
| CategoryNo | CategoryName |
+------------+--------------+
|          1 | Novel        |
|          3 | History      |
+------------+--------------+
2 rows in set (0.00 sec)

# Error PK값 중복: Category Table에 data 삽입.
mysql> INSERT INTO Category (CategoryNo, CategoryName) VALUES (3, 'Science');
ERROR 1062 (23000): Duplicate entry '3' for key 'Category.PRIMARY'


# Product Relation에 PK Constraint 설정.

# Product Table의 schema 확인.
mysql> desc Product;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ProductNo   | int           | YES  |     | NULL    |       |
| ProductName | varchar(30)   | YES  |     | NULL    |       |
| Price       | decimal(10,0) | YES  |     | NULL    |       |
| CategoryNo  | int           | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

# Product Table의 ProductNo column에 PK Constraint 추가.
mysql> ALTER TABLE Product ADD CONSTRAINT pk_Product PRIMARY KEY(ProductNo);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Product;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ProductNo   | int           | YES  | PRI  | NULL   |       |
| ModelNumber | char(12)      | YES  |      | NULL   |       |
| ProductName | varchar(30)   | YES  |      | NULL   |       |
| Price       | decimal(10,0) | YES  |      | NULL   |       |
| CategoryNo  | int           | YES  |      | NULL   |       |
+-------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

# Product Table에 data 삽입.
mysql> INSERT INTO Product (ProductNo, ProductName, Price) VALUES (20101927, 'The Second World War', 37800);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM Product;
+-----------+-------------+-------------+-------+------------+
| ProductNo | ModelNumber | ProductName | Price | CategoryNo |
+-----------+-------------+-------------+-------+------------+
|  20101927 | NULL        | 2           | 37800 |       NULL |
+-----------+-------------+-------------+-------+------------+
1 row in set (0.00 sec)


# 연습 2: Referential Integrity를 위한 Foreign Key Constraint 설정.


# Product table에 CategoryNo를 foreign key constraint 적용.
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo);
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Category;
+------------+--------------+
| CategoryNo | CategoryName |
+------------+--------------+
|         1  | Novel        |
|         3  | History      |
+------------+--------------+


# 참조하는 릴레이션에 존재하지 않는 값으로 변경하려는 경우


# Error: 2는 category에 존재 X 따라서 변경 X
# Product table의 ProductNo가 20101927인 tuple의 CategoryNo 값을 2로 변경.
mysql> UPDATE Product SET
    -> CategoryNo = 2
    -> WHERE ProductNo = 20101927;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Module02`.`Product`, CONSTRAINT `fk_Product_Category` FOREIGN KEY (`CategoryNo`) REFERENCES `Category` (`CategoryNo`))

# Product table의 ProductNo가 20101927인 tuple의 CategoryNo 값을 3으로 변경.
mysql> UPDATE Product SET
    -> CategoryNo = 3
    -> WHERE ProductNo = 20101927;
Rows matched: 1  Changed: 1  Warnings: 0

# Product table 확인.
mysql> SELECT * FROM Product;
+-----------+-------------+-------------+-------+------------+
| ProductNo | ModelNumber | ProductName | Price | CategoryNo |
+-----------+-------------+-------------+-------+------------+
|  20101927 | NULL        | 2           | 37800 |          3 |
+-----------+-------------+-------------+-------+------------+
1 row in set (0.00 sec)

# Product table에 data 삽입.
mysql> INSERT INTO Product (ProductNo, ProductName, Price, CategoryNo) VALUES (97422537, 'Hobbit', 28800, 1);

mysql> INSERT INTO Product (ProductNo, ProductName, Price, CategoryNo) VALUES (97422515, 'Lord of the Rings 1', 28800, 1);

# Product table data 확인.
mysql> SELECT * FROM Product;
+-----------+-------------+----------------------+-------+------------+
| ProductNo | ModelNumber | ProductName          | Price | CategoryNo |
+-----------+-------------+----------------------+-------+------------+
|  20101927 | NULL        | The Second World War | 37800 |          3 |
|  97422515 | NULL        | Lord of the Rings 1  | 28800 |          1 |
|  97422537 | NULL        | Hobbit               | 28800 |          1 |
+-----------+-------------+----------------------+-------+------------+
3 rows in set (0.00 sec)


# 참조하는 relation에 존재하지 않는 값에 대한 삽입 시도하는 경우.


# Error: CategoryNo에 2는 값이 없다.
mysql> INSERT INTO Product (ProductNo, ProductName, Price, CategoryNo) VALUES (2312211, 'Cosmos', 28800, 2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Module02`.`Product`, CONSTRAINT `fk_Product_Category` FOREIGN KEY (`CategoryNo`) REFERENCES `Category` (`CategoryNo`))

# Category Table에 CategoryNo가 2인 data 삽입.
mysql> INSERT INTO Category VALUES (2, 'Science');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM Category;
+------------+--------------+
| CategoryNo | CategoryName |
+------------+--------------+
|          1 | Novel        |
|          2 | Science      |
|          3 | History      |
+------------+--------------+
3 rows in set (0.00 sec)

# 다시 Product table에 categoryNo가 2인 data 삽입.
mysql> INSERT INTO Product (ProductNo, ProductName, Price, CategoryNo) VALUES (2312211, 'Cosmos', 28800, 2);
Query OK, 1 row affected (0.01 sec)


# Relational Relation의 data가 삭제되는 경우.


# Category Table에서 CategoryNo가 2인 Tuple 삭제 시도.
# Error: Foreign Key Constraint가 적용 되었기 때문.
mysql> DELETE FROM Category WHERE CategoryNo = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`Module02`.`Product`, CONSTRAINT `fk_Product_Category` FOREIGN KEY (`CategoryNo`) REFERENCES `Category` (`CategoryNo`))

# Product Table의 Constraint 확인
mysql> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.table_constraints WHERE table_name = 'Product';
+---------------------+-----------------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE |
+---------------------+-----------------+
| PRIMARY             | PRIMARY KEY     |
| fk_Product_Category | FOREIGN KEY     |
+---------------------+-----------------+
2 rows in set (0.00 sec)

# Product table의 FK Constraint 삭제.
mysql> ALTER TABLE Product DROP CONSTRAINT fk_Product_Category;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# FK Constraint 다시 추가 + ON DELETE CASCADE
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) ON DELETE CASCADE;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

# CategoryNo 삭제 가능.
mysql> DELETE FROM Category WHERE CategoryNo = 2;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM Category;
+------------+--------------+
| CategoryNo | CategoryName |
+------------+--------------+
|          1 | Novel        |
|          3 | History      |
+------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Product;
+-----------+-------------+----------------------+-------+------------+
| ProductNo | ModelNumber | ProductName          | Price | CategoryNo |
+-----------+-------------+----------------------+-------+------------+
|  20101927 | NULL        | The Second World War | 37800 |          3 |
|  97422515 | NULL        | Lord of the Rings 1  | 28800 |          1 |
|  97422537 | NULL        | Hobbit               | 28800 |          1 |
+-----------+-------------+----------------------+-------+------------+
3 rows in set (0.00 sec)

# Product table의 FK Constraint 삭제
mysql> ALTER TABLE Product DROP CONSTRAINT fk_Product_Category;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Product table에 FK Constraint 적용 + ON DELETE SET NULL
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) ON DELETE SET NULL;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DELETE FROM Category WHERE CategoryNo = 3;
Query OK, 1 row affected (0.01 sec)

# ON DELETE SET NULL로 인해 참조하는 값이 삭제되면 NULL로 값이 갱신됨.
mysql> SELECT * FROM Product;
+-----------+-------------+----------------------+-------+------------+
| ProductNo | ModelNumber | ProductName          | Price | CategoryNo |
+-----------+-------------+----------------------+-------+------------+
|  20101927 | NULL        | The Second World War | 37800 |       NULL |
|  97422515 | NULL        | Lord of the Rings 1  | 28800 |          1 |
|  97422537 | NULL        | Hobbit               | 28800 |          1 |
+-----------+-------------+----------------------+-------+------------+
3 rows in set (0.00 sec)

# Product table의 FK Constraint 삭제.
mysql> ALTER TABLE Product DROP CONSTRAINT fk_Product_Category;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Product table에 FK Constraint 적용 + ON DELETE NO ACTION
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) ON DELETE NO ACTION;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

# ON DELETE NO ACTION은 참조하는 table이 있으면 data 삭제 안됨.
mysql> DELETE FROM Category WHERE CategoryNo = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`Module02`.`Product`, CONSTRAINT `fk_Product_Category` FOREIGN KEY (`CategoryNo`) REFERENCES `Category` (`CategoryNo`))


# 참조 relation의 data가 갱신되는 경우


# Product table의 FK Constraint 삭제.
mysql> ALTER TABLE Product DROP CONSTRAINT fk_Product_Category;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Product 테이블에 FK Constraint 적용 + ON UPDATE CASCADE
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) ON UPDATE CASCADE;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

# CategoryNo 1 -> 2로 변경.
mysql> UPDATE Category SET CategoryNo = 2 WHERE CategoryNo = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Product;
+-----------+-------------+----------------------+-------+------------+
| ProductNo | ModelNumber | ProductName          | Price | CategoryNo |
+-----------+-------------+----------------------+-------+------------+
|  20101927 | NULL        | The Second World War | 37800 |       NULL |
|  97422515 | NULL        | Lord of the Rings 1  | 28800 |          2 |
|  97422537 | NULL        | Hobbit               | 28800 |          2 |
+-----------+-------------+----------------------+-------+------------+
3 rows in set (0.00 sec)

# Product table의 FK Constraint 삭제
mysql> ALTER TABLE Product DROP CONSTRAINT fk_Product_Category;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Product table에 FK Constraint 적용 + ON UPDATE NO ACTION
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) ON UPDATE NO ACTION;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

# Error: NO ACTION이기 때문에 이 값을 다른 relation이 참조하고 있다면 UPDATE 불가능.
mysql> UPDATE Category SET CategoryNo = 1 WHERE CategoryNo = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`Module02`.`Product`, CONSTRAINT `fk_Product_Category` FOREIGN KEY (`CategoryNo`) REFERENCES `Category` (`CategoryNo`))

# Product table의 FK Constraint 삭제
mysql> ALTER TABLE Product DROP CONSTRAINT fk_Product_Category;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Product table에 FK Constraint 적용 + ON DELETE SET NULL
mysql> ALTER TABLE Product ADD CONSTRAINT fk_Product_Category FOREIGN KEY(CategoryNo) REFERENCES Category(CategoryNo) ON UPDATE SET NULL;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

# ON DELETE SET NULL이기 떄문에 삭제하면 값이 NULL로 갱신됨.
mysql> UPDATE Category SET CategoryNo = 1 WHERE CategoryNo = 2;

mysql> SELECT * FROM Product
+-----------+-------------+----------------------+-------+------------+
| ProductNo | ModelNumber | ProductName          | Price | CategoryNo |
+-----------+-------------+----------------------+-------+------------+
|  20101927 | NULL        | The Second World War | 37800 |       NULL |
|  97422515 | NULL        | Lord of the Rings 1  | 28800 |       NULL |
|  97422537 | NULL        | Hobbit               | 28800 |       NULL |
+-----------+-------------+----------------------+-------+------------+
3 rows in set (0.00 sec)

 

 

 

출처: https://github.com/gikpreet/class-relational_database

 

GitHub - gikpreet/class-relational_database

Contribute to gikpreet/class-relational_database development by creating an account on GitHub.

github.com

 

저작자표시 비영리 동일조건 (새창열림)

'DB' 카테고리의 다른 글

[Relational database] 04. 파일 조직과 인덱스  (1) 2024.10.03
[Relational database] DB 확장 - Scale Up vs Scale Out  (2) 2024.10.03
[Relational database] 03. 디스크와 파일  (1) 2024.10.03
[Relational database] DDL, DML, DCL, TCL  (0) 2024.10.01
[Relational database] 01. 데이터 베이스 개요 *ACID  (0) 2024.09.30

'DB'의 다른글

  • 현재글[Relational database] 02. 관계형 모델 (Relational model) *제약조건

관련글

티스토리툴바