[MySQL] 외래키(FOREIGN KEY)의 정의 무결성 제약조건
왜래키는 잘 쓰면 보약이고 잘못하면 관련된 테이블 모두 확인 하느라 부하가 심해진다.
그래서 무조건 쓰지 못하게 하는곳도 있는 실정이다. 사실 우리가 그런부분이 있다.
사이트의 부하가 많지 않다면 사용하는것을 권장 하는데 문제가 될일이 별로 없기 때문에 그렇다.
외래키는 두 테이블 간의 관계를 정의하는 데 사용 되는 것으로 보통 부모 테이블과 자식 테이블 간의 관계를 나타내는 데에 사용됩니다.
1. 관계 정의
외래키는 부모 테이블의 기본 키(primary key) 또는 유일한 키(unique key)와 연결됩니다. 이 관계를 통해 자식 테이블의 행들이 부모 테이블의 행들을 참조할 수 있습니다.
2. 무결성 유지
외래키 제약 조건을 사용하여 데이터 무결성을 유지할 수 있습니다. 이는 자식 테이블에 존재하는 값이 부모 테이블에 있는 값과 일치해야 한다는 것을 의미합니다. 이를 통해 부모 테이블에서 삭제되거나 변경된 행에 대한 적절한 처리가 이루어질 수 있습니다.
3. 참조 무결성 제약 조건
MySQL에서 외래키를 정의할 때 ON DELETE 및 ON UPDATE와 같은 옵션을 사용하여 부모 테이블에서 행이 삭제되거나 업데이트 될 때의 동작을 정의할 수 있습니다. 예를 들어 부모 테이블에서 행이 삭제될 때 자식 테이블에서 해당 행에 대한 참조를 자동으로 삭제하도록 설정할 수 있습니다.
4. 인덱스 생성
MySQL에서 외래키를 정의하면 해당 열에 대한 인덱스가 자동으로 생성됩니다. 이는 외래키 관련 작업의 성능을 향상시키는 데 도움이 됩니다.
5. 제약 조건
외래키 제약 조건을 사용하여 특정 작업이 수행되기 전에 조건을 검증할 수 있습니다. 예를 들어 외래키 제약 조건을 사용하여 부모 테이블에 존재하지 않는 값을 자식 테이블에 삽입하는 것을 방지할 수 있습니다.
요약하자면 MySQL의 외래키는 데이터베이스에서 데이터의 일관성과 무결성을 유지하기 위한 중요한 기능 중 하나입니다.
-- 주문 테이블 생성
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
-- 고객 테이블 생성
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
);
-- 주문과 고객 정보를 함께 조회하는 SQL
SELECT Orders.OrderID, Orders.OrderDate, Orders.TotalAmount, Customer.FirstName, Customer.LastName
FROM Orders
JOIN Customer ON Orders.CustomerID = Customer.CustomerID;
mysql> desc Customer;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| CustomerID | int | NO | PRI | NULL | |
| FirstName | varchar(50) | YES | | NULL | |
| LastName | varchar(50) | YES | | NULL | |
| Email | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> desc Orders; select * from Orders;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| OrderID | int | NO | PRI | NULL | |
| CustomerID | int | YES | MUL | NULL | |
| OrderDate | date | YES | | NULL | |
| TotalAmount | decimal(10,2) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> desc SELECT Orders.OrderID, Orders.OrderDate, Orders.TotalAmount, Customer.FirstName, Customer.LastName FROM Orders JOIN Customer ON Orders.CustomerID = Customer.CustomerID;
+----+-------------+----------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
| 1 | SIMPLE | Orders | NULL | ALL | CustomerID | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | Customer | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test1.Orders.CustomerID | 1 | 100.00 | NULL |
+----+-------------+----------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
-- 오류가 발생 한다.
mysql> delete from Customer where CustomerID IN (1,3,5,7);
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ("test1"."Orders", CONSTRAINT "Orders_ibfk_1" FOREIGN KEY ("CustomerID") REFERENCES "Customer" ("CustomerID"))
-- 확인을 테이블 생성된것을 보고 해야 한다. show index from Orders; 을 보고 하면 안된다.
mysql> SHOW CREATE TABLE Orders;
CREATE TABLE "Orders" (
"OrderID" int NOT NULL,
"CustomerID" int DEFAULT NULL,
"OrderDate" date DEFAULT NULL,
"TotalAmount" decimal(10,2) DEFAULT NULL,
PRIMARY KEY ("OrderID"),
KEY "CustomerID" ("CustomerID"),
CONSTRAINT "Orders_ibfk_1" FOREIGN KEY ("CustomerID") REFERENCES "Customer" ("CustomerID")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 외래키를 삭제 해준다.
mysql> ALTER TABLE Orders DROP FOREIGN KEY Orders_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 다시 생성하고 확인 한다.
mysql> ALTER TABLE Orders ADD CONSTRAINT CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE CASCADE;
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings:0
-- 또는 업데이트도 가능하게 해준다.
ALTER TABLE Orders ADD CONSTRAINT CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;
mysql> SHOW CREATE TABLE Orders;
CREATE TABLE "Orders" (
"OrderID" int NOT NULL,
"CustomerID" int DEFAULT NULL,
"OrderDate" date DEFAULT NULL,
"TotalAmount" decimal(10,2) DEFAULT NULL,
PRIMARY KEY ("OrderID"),
KEY "CustomerID" ("CustomerID"),
CONSTRAINT "CustomerID" FOREIGN KEY ("CustomerID") REFERENCES "Customer" ("CustomerID") ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
-- 같이 삭제 되었는지 확인해 본다.
mysql> delete from Customer where CustomerID IN (1,3,5,7);
Query OK, 4 rows affected (0.01 sec)
mysql> select * from Orders;
+---------+------------+------------+-------------+
| OrderID | CustomerID | OrderDate | TotalAmount |
+---------+------------+------------+-------------+
| 2 | 2 | 2024-04-12 | 150.00 |
| 4 | 4 | 2024-04-11 | 120.00 |
| 6 | 6 | 2024-04-10 | 300.00 |
| 8 | 8 | 2024-04-09 | 180.00 |
| 9 | 9 | 2024-04-08 | 250.00 |
| 10 | 10 | 2024-04-08 | 130.00 |
+---------+------------+------------+-------------+
-- 관계가 있으면 드랍도 안된다.
mysql> drop table Customer;
ERROR 3730 (HY000): Cannot drop table 'Customer' referenced by a foreign key constraint 'CustomerID' on table 'Orders'.