[ DataBase - Basic ] - key, constraints, SQL

implicit constraints

relation data model 자체가 가지는 constraints로써, relation은 중복되는 tuple을 가질 수 없다나 relation 내에서 같은 이름의 attribute를 가질 수 없다 등이 있겠습니다.

 

schema-based constraints (= explicit constraints)

이는 주로 DDL을 통해 직접 명시할 수 있는 constraints를 말합니다. 

 

domain constraints

위와같이 attribute의 value는 해당 attribute의 domain에 속한 value여야 한다는 게 domain constraints입니다.

 

key constraints

이는 서로 다른 tuples는 같은 value의 key를 가질 수 없는 것을 말합니다. 여기에도 3가지 유형의 제약조건을 포함하는데, Primary Key Constraints, Foreign Key Constraints, Unique Constraints 등이 있습니다. 

 

NULL value constraints

이는 attribute가 NOT NULL로 명시됐다면 NULL을 값으로 가질 수 없음을 말합니다.

 

entity integrity constraints

이는 primary key는 value에 NULL을 가질 수 없는 것을 말합니다.

 

referential integrity constraints

이는 FK와 PK와 도메인이 같아야 하고 PK에 없는 values를 FK값으로 가질 수 없는 것을 말합니다.

 

SQL

우리는 간단히 IT 회사 RDB를 만들어 보면서, MySQL에서의 SQL문이 어떻게 돌아가는지에 대해 간단히 알아보겠습니다.

 

우선 Department, Employee, Project, Works_on 등의 Relation(Table)이 있습니다.

 

Department Table

제약사항을 SQL 아래에다 적어주는 방법

create table 문으로 Department 테이블을 생성했습니다. id는 PK로 설정해 줄 것이므로 제약조건을 추가해주었습니다. 또한 name으로는 VARCHAR(20) 으로 주었는데, 이는 가변 문자열을 사용하겠다는 의미이고, NOT NULL / UNIQUE 제약조건을 주었습니다. 이는 Key constraints인데, key의 값이 고유해야 하며, NULL이면 안된다는 것인데 둘은 통상 같이 쓰는 것이 일반적입니다.

 

여기서 VARCHAR(20) 은 최대 문자열 20개까지 저장하겠다는 건데 CHAR(20)과는 다릅니다. CHAR(20)으로 하게 되면 고정된 문자열을 저장하게 되어서 인덱싱을 통해서 검색 속도가 빠를지라도 메모리 용량을 VARCHAR보다 많이 잡아먹게 됩니다. 하지만 VARCHAR는 검색 속도가 느려지는 대신 DB의 메모리 용량을 더 효율적으로 관리할 수 있게 됩니다.

 

• PRIMARY KEY
  • primary key는 table의 tuple을 식별하기 위해 사용된다고 했었습니다. 하나 이상의 attribute(s)로 구성되며, 이는 PK가 중복된 값을 가질 수 없으며, NULl도 값으로 가질 수 없게 합니다.
• UNIQUE 
  • UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없습니다. 단, NULL은 중복을 허용할 수 있게 됩니다 (RDBMS마다 다름) -> 그래서 NOT NULL과 같이 쓰는 것이 일반적이라고 한 것입니다. 
• NOT NULL
  • attribute가 NOT NULL로 지정되면 해당 attribute는 NULL로 값을 가질 수 없게 됩니다.

 

EMPLOYEE Table

다음은 Employee 테이블을 정의하는 SQL문입니다. 여기서 위에서 안살펴본 속성만 간단히 살펴보겠습니다.

 

• attribute DEFAULT
  • attribute의 default 값을 정의할 때 사용합니다. 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장합니다.
• CHECK constraint
  • 이는 attribute의 값을 제한하고 싶을 때 사용하며, 만약 제약할 attribute가 여러개라면 맨 아래에다 따로 정의해 줄 수도 있으며, CHECK constraint의 이름도 지정해 줄 수 있습니다.

그 다음은 Referential intergrity constraints 즉 FK와 관련된 이야기입니다. 이는 attribute(s)가 다른 table의 PK나 unique key를 참조할 때 사용합니다.

위는 Employee와 Department의 예시입니다. 이에 대해서는 규칙이 좀 있는데, 저희가 JPA등을 할 때 많이 봤던 개념입니다.

Cascade를 통해 참조 값의 삭제/변경 시 값을 전이시킬 수 있으며, Set NULL을 통해 NULL로 변경할 수 있고, Restrict를 통해 삭제/변경을 금지할 수 있고 NO ACTION은 한 Transaction내에 여러개의 SQL문이 있다고 할 때, 해당 Transaction 내에서는 Referential integrity constraint를 위반하는 것을 허용하지만, 끝났는데도 여전히 위반한다면 이는 허용하지 않는다는 제약사항입니다. 그 다음으로는 SET Default입니다. 이는 MySQL에서는 제대로 지원하지는 않지만 default값을 지정해줄 수 있습니다.

 

PROJECT Table

위에서 본 속성을 그대로 사용해주었습니다.

 

WORKS_ON Table

이 또한 empl_id, proj_id에 대해 외래키를 설정해주었습니다. 

 

ALTER Table

그 다음으로는 이제 DEPARTMENT Table에서 FK를 설정해 주었는데, 이때는 Table의 Schema를 수정해 주어야 합니다. 그럴때는 alter table SQL문을 작성해주면 됩니다.

위와같이 말이죠. 이는 이미 서비스 중인 table의 schema를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요합니다. 

 

즉 종합적으로 database 구조를 저으이할 때 중요한 점은, 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요합니다