Skip to content
Search

Database:Constraints

lastmod 2015-03-15created 2015-03-152 edits

Description of database constraints.

Comparison of identifying relationships

The types of identifying relationships are as follows.

  • Identifying
  • Non-Identifying
  • Optional Non-Identifying

One answer reads as follows. 

식별관계냐 비식별관계냐는 erd를 작성하는 사람의 판단에 따른다고 생각합니다.

자식테이블의 pk를 구성하는 칼럼이 부모로부터 받는 거라면 식별이고
그렇지 않고 단순한 데이타 저장용 칼럼이라면 비식별 관계가 되겠지요.

이때 부모로 부터 받은 칼럼을 일반 칼럼으로 잡을 수도 있지만 pk로도 잡을 수 있는 애매한 상황등이 존재하는데 pk칼럼을 구성하는 원칙중 최소한의 원칙이란 것이 있습니다.
즉 자식테이블의 pk를 구성하는 칼럼이 col1,col2,col3,col4 4개가 있고 col4는 부모로부터 받은 칼럼입니다.
그런데 자식테이블 로우의 유일성을 구분할 수 있는 것이 col1,col2,col3만으로도 충분하다면 col4는 pk 구성에서 빠져야 합니다.
그렇다면 처음에는 식별관계였다가 이후 비식별관계가 되겠지요.

간단히 설명드렸는데 데이타베이스의 설계는 1+2=3 이다 이런식으로 수학공식처럼 딱 떨어지지 않는 부분이 많습니다.
상당히 인문학적인 요소가 들어간다고 할까요?
사실 개판으로 erd를 만들어도 프로그래머가 고생하고 엄청나게 비효율적인 프로그램이 만들어지고 유지보수가 어려울 뿐이지 프로그램이 안돌아가는 것은 아닙니다.
다만 훌륭하게 erd가 만들어지면 프로그램 소스가 깔끔하게 나오고 유지보수도 쉬어지면 그만큼 효율적인 시스템이 되겠지요.

장황하게 말씀드렸는데 결론은 erd작성에는 정답이 별로 없다라는 것입니다.

Foreign key constraints

ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE statement is rolled back.
ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE statement is rolled back.

CASCADE, SET NULL, and SET DEFAULT allow deletions or updates of key values to affect the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. If cascading referential actions have also been defined on the target tables, the specified cascading actions also apply for the rows deleted or updated in those tables. CASCADE cannot be specified for any foreign keys or primary keys that have a timestamp column.

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.
ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row whose key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key.
ON DELETE SET NULL
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.
ON UPDATE SET NULL
Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.
ON DELETE SET DEFAULT
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a DEFAULT definition for this constraint to execute. If a column is nullable and there is no explicit default value set, NULL becomes the implicit default value of the column. Any non-null values set because of ON DELETE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.
ON UPDATE SET DEFAULT
Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a DEFAULT definition for this constraint to execute. If a column is nullable and there is no explicit default value set, NULL becomes the implicit default value of the column. Any non-null values set because of ON UPDATE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.

Favorite site

sitemap