데이터베이스 제약조건 Integrity Constraints

Integrity Constraint는 데이터베이스에 실수로 피해를 주지 않게 만들어 놓은 제약조건들입니다

 

-> 데이터 일관성 Data Consistency를 유지하기 위함이죠

 

그래서 실생활 예시들로는

- 은행 계좌는 잔고가 0원 이상이여야한다

- 은행 직원은 시급이 5000원 이상이여야한다

- 고객들의 번호 형식은 000-0000-0000 이여야 한다

 

등이 있다

 

그래서 제약조건은 크게 2가지로 분류 할 수 있는데요

 

한 Relation을 대상으로:

• not null
• primary key
• unique
• Check(c) c:condition

여러 Relation들을 대상으로

• foreign keys

그래서 복합적으로 활용되는 SQL 코드를 확인해보면

CREATE TABLE employees(
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR (50),
	birth_date DATE not null,
	joined_date DATE CHECK (joined_date > birth_date),
	salary numeric CHECK (salary > 0),
	Primary key(id),
	CHECK (birth_date > '1900-01-01').
	UNIQUE (first_name, last_name)
);

 

이런 모습인데요

반응형

테이블 전체를 대상으로 하는 constraint들은 맨 밑에 있는것을 확인 할 수 있죠

 

Foreign Key

---

Foreign Key는 다른 테이블을 참조하는데

꼭 그 참조 당하는 Key는 Primary Key일 필요는 없고

Primary Key 이거나 Unique해야한다

foreign key (dept_name) references department (dept_name)

 

그러나 Foreign Key의 attribute이름이 참조하는 다른 테이블 이름이랑 같으면

attribute명은 생략해도된다

foreign key (dept_name) references department

728x90

Ex)

Integrity Constraint에 위배되는 커맨드들이 들어오면

 

1. Normal Procedure: 위배되는 커맨드들을 Reject한다

2. Alternative: Cascade나 다른 action들을 통해 처리한다, (delete나 update의 경우)

 

Cascade

---

참조 당하는 Row가 update되거나 Delete되면, 참조하고 있는 row도 또한 같이 update/delete 된다

 

Other Options

---

• Set Null: 참조하는 값을 null로 설정
• Set Default: 참조하는 값을 default 값으로 설정
  • Default 값은 constraint들을 위반해서는 안된다
• Restriction: 참도 당하는 row는 그냥 삭제 불가능
• No Action:

No Action

---

대부분의 DB Default 값이다

 

No Action. Constraint에 위반 되었는지 계속 확인 하는게 아니라

Transaction 전체가 끝날 때 까지 기다렸다 위배 되었는지 확인한다

 

예시는 영어로 적어보겠다

---

 

The key idea is that NO ACTION allows the database to consider the entire transaction's sequence of operations before deciding whether the referential integrity rule has been violated. This behavior is particularly useful for complex transactions involving multiple operations that, individually, might appear to violate the constraint but are ultimately resolved within the same transaction.

Ex)

we have two tables

1. department:
2. CREATE TABLE department ( id INT PRIMARY KEY, name VARCHAR(50) );
3. employee:
4. CREATE TABLE employee ( emp_id INT PRIMARY KEY, department_id INT, FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE NO ACTION );

Transaction:

Suppose we execute the following:

BEGIN TRANSACTION;

-- Step 1: Delete the department with id = 1
DELETE FROM department WHERE id = 1;

-- Step 2: Delete all employees in that department
DELETE FROM employee WHERE department_id = 1;

COMMIT;

What Happens with NO ACTION?

1. Step 1: The department with id = 1 is marked for deletion, but the foreign key NO ACTION rule does not immediately raise an error, even though employee rows with department_id = 1 still exist
2. Step 2: The employee rows with department_id = 1 are deleted.
3. End of Transaction: When the transaction is about to commit, the database checks the constraint. Since the employee rows referencing department.id = 1 are now gone, the constraint is not violated, and the transaction commits successfully.

If Checked Immediately

If the database checked the constraint immediately after Step 1 (like with RESTRICT), it would block the deletion of the department because there were still referencing rows in employee.

Difference Between NO ACTION and RESTRICT

While they seem similar, there is a subtle but crucial difference:

• RESTRICT: Enforces the constraint immediately. If a violation occurs, the operation is blocked on the spot.
• NO ACTION: Defers the constraint check until the transaction is about to commit, allowing subsequent operations to resolve potential violations.

Summary

In NO ACTION, the database treats operations in the transaction as part of a cohesive unit. It delays enforcing the foreign key constraint until the transaction is about to commit, ensuring that intermediate states that might temporarily violate the constraint are ignored, as long as the final state satisfies the constraint.

---