3-1 Relation Model

https://eglife.tistory.com/61

3. Relational Model

 

- Integrity Constraints ( ICs , 일명 무결성)

Relation Model 3대장 중에 하나이다.(1. Structure 2. Operation 3. Contraints) IC란 DB안 모든 Instance들에 대해서 True인 컨디션을 말한다.

ex) Domain Constraint, Primary / Foreign Key constraint. 즉, 사용자가 Declare 한 건에 대해선 DBMS가 알아서 관리해준다.

반면에, DB는 아무 instance를 다 허용하지 않는다. ex) data enrty error.

- ICs를 만족하는 instance들만 허용한다.

 

- ICs출처는 어디일까? : DB는 Entity / Relationship으로 이루어져 있다. 이를 사용하는 각 기관들이 입맛에 맞게 설정하는 것이 ICs라는 것. 문자그대로 Constraint! 제약!

 

그럼 IC는 누가 책임지고 체크하나? -> DBMS

DBMS가 체크를 해야 좀 더 정확하고, User들도 신경쓰지 않아도 돼 좋다.

 

IC support in DBMS 어떤 case가 있는 지 알아보자

 

- User가 Schema 만들 때 특정된다.(ex) DB designer or admin)

- 그리고 enabled/ disabled/ changed 할 때,

- Stored / Managed by DMBS

- Checked when relations are modified ( ex) Insert/delete/update 할 때마다 ICs 체크 들어간다)

- ICs에 위배되면? Error Return 바로 날라온다.

 

BUT! 우리는 ICs를 그냥 instance만 보고 알 수 없다. Student table에서 sid가 key인 걸 DB Designer가 아니라면 어떻게 안다는 말이야 --> Primary/Foreign Key 개념이 필요한 이유

 

 

- Set of Fields( Col 여러 개를) Key로 하는 경우는 아래 2 가지를 만족시켜야 한다.

1) Tuple들이 전부 distinct할 때

2) Set of Fileds에서 일부 subset key로는 key역할을 못할 때!

 

- Key가 2개 이상으로 이루어져 있으면 그것들을 Candidates Keys라고 하며, DBA는 자동으로 그것들 중 하나를 Primary Key로 설정한다.

 

 

- 조건에 따라 Candidate Keys / Primary Key는 알아서 지정하면 된다. Candidates => "UNIQUE"를 이용해서 지정가능.

 

 

Foreign Keys and Referential Integrity

 

Foreign Key : Set of field로, 다른 튜플을 참조하는 관계를 나타낸다.

- FK와 PK는 relation이 서로 형성되어 있어야 한다.

- FK는 다른 의미론 Logical Pointer이다.

- Value - based relationship in RDBMS이다. --> Data independence를 위해 필요한 기능

 

FK기능이 활성화 되면, 참조무결성이 활성화되어 Dangling Reference(Pointer를 쫓아갔는데 그 끝에 아무것도 없는 현상)이 안 나타난다.

 

Create Table Enrolled (sid~, cid~, grade~, Primary Key(sid,cid), Foreign Key(sid) Referece Students) 라는 코드. Child Table Enrooled가 Parent Table Students를 참조한다. Enrolled의 PK는 sid/cid 이고 sid를 FK로 해서 Student Table을 참조한다.

 

PK / FK는 NULL값 허용 X, 이 외에 COL은 NULL값 허용 O

 

Parent Table의 PK index 용도 : Table의 중복값을 빨리 찾는다 / Child Table이 FK를 통해 날려온 찾기 요청을 빨리 Check!

 

 

보틍 FK는 다른 Relation(Table)의 PK를 참조하는 것이 맞는데, 셀프참조도 가능하다.

 

Create Table EMP(EMPNO number Primary Key, ENAME varchar, Job varchar, MGR Number References EMP, DEPNO number Constraint FK_DEPTNO references Dept)

 

 

FK / PK로 엮여 있을 때, Child Table FK쪽에 수정이 발생한다면? ( About 참조무결성 = Referential Integrity )

 

 1) 새로운 Tuple이 Insert 될 때 : PK에 해당값이 있는지 Check 하고 없으면? ERROR ( To prevent Dangling refer)
 2) 기존 Tuple을 Delete

INSERT INTO EMP VALUES
        (NULL, 'SIMON',  'DBA', 7902,
        TO_DATE('17-12-1980', 'DD-MM-YYYY'),  800, NULL, 50);
--ORA-01400: cannot insert NULL into ("SQL_IOVYEIUUVLRSRFUANXPRFKMID"."EMP"."EMPNO") ORA-06512: at "SYS.DBMS_SQL"

할 때 : 그냥 진행~

 

FK / PK로 엮여 있을 때, Parent Table PK쪽에 수정이 발생한다면? ( About 참조무결성 = Referential Integrity )

 

 1) Insert? : 그냥 진행하면 됨

 2) Delete? : 삭제할 항목이 FK에 있는지 체크해야 한다. 이 때 관련 옵션을 아래 4가지 정도로 생각해볼 수 있다.

  a) PK-FK 전부 해당 Tuple을 삭제한다. (CASCADE)

  b) FK와 참조관계로 엮여 있는 Student Table의 Tuple 삭제를 막는다. (NO ACTION)

  c) 삭제 시, FK 값을 null / unknown / inapplicable 등 NULL 값으로 세팅한다. (SET NULL)

  d) 삭제 시, FK 값을 Default로 바꾼다. 이 예시에선 Enrolled Table의 sid(FK) tuple의 특정 기본값으로!(SET DEFAULT)

 

FK / PK로 엮여 있을 때, Parent Table PK쪽에 Update를 할 때도 Delete Case와 비슷하게 4가지를 고려하면 된다.

(어차피 Update라는 것이 Delete 후 Insert와 같다고 생각해도 되니까..ㅎ)

 

--> 위 내용을 SQL 코드로 나타내면,

CREATE TABLE ENROLLED
( SID CHAR(20),
  CID CHAR(20),
  GRAGE CHAR(2),
  PRIMARY KEY (SID, CID),
  FOREIGN KEY (SID)
  REFERENCES STUDENTS ON DELETE CASCADE ON UPDATE SET DEFAULT)

요렇게 Child Table 생성 시 delete / update 할 때 어떻게 할 건지 정해줄 수 있다.

이건 DBMS마다 가능여부가 다르긴 하다.. ex) Oracle에선 on delete CASCADE(O), update CASCADE(X)

 

번외로, PK에 있는 Auto-creation of Index --> FK에도 있으면 좋다. PK update/delete시 FK를 빨리 Check할 수 있어서!

 

Itegrity Constraint and Transactions

 

- Transaction을 날릴 때 IC 체크를 어느 시점에 할 것인가에 대한 논의이다.

1) Immeidate Mode ( Default ) : SQL Statement 끝날 때마다 바로바로 체크한다.

2) Deferred Mode : Commit가 끝나고 한 번에 체크한다(SET Constraint Foo Deferred)

 --> 2번으로 하면 좋은게, 아래 현상을 방지할 수 있다.

요 TX를 한뭉탱이로 보면 문제 없는데, Immediate모드로 보면 EMP Table 만들 때 Error다! why? DEPT가 있어야 PK/FK Relation을 만드는데 아직 DEPT가 없어서

 

 

- SQL 실습

select ename, empno from emp order by ename;
select ename, empno from emp order by 1;
select 1 from emp; -- Note: in the select clause, '1' does not mean 1st column.

숫자는 order by 뒤에 쓰일 때는, col 순서를 대변하는데, select 절에선 그냥 숫자 "1", 즉 무의미다.

 

Dept table

 

Insert into dept values ('XX','DATABASE','SUWON'); invalid number ORA-06512
INSERT INTO DEPT VALUES ('50', 'DATABASE', 'SUWON');

desc dept; --> DEPT 테이블의 정보가 나타난다

 

dept values에 DEPNO가 number 타입인데 이상한 문자를 넣으려고 하면 바로 ERROR.

Tuple을 insert into 할 때는 Data type을 체크해주어야 한다. (Due to Integrity Constraints)

 

2번 째 줄 Insert 적용은 잘 됐다. 삭제는 (delete from dept where deptno = 50;) 요런식으로 가능

 

EMP Table

Conceptual = Logical Schema of EMP

 

INSERT INTO EMP VALUES
        (NULL, 'SIMON',  'DBA', 7902,
        TO_DATE('17-12-1980', 'DD-MM-YYYY'),  800, NULL, 50);
--ORA-01400: cannot insert NULL into ("SQL_IOVYEIUUVLRSRFUANXPRFKMID"."EMP"."EMPNO")

EMP의 EMPNO가 PK라 NULL값을 가질 수 없는데 넣으려니까 Error가 뜬다.

 

INSERT INTO DEPT VALUES ('50', 'DATABASE', 'SUWON');
INSERT INTO DEPT VALUES (50, 'VLDB', 'SUWON');
select * from dept;
--ORA-00001: unique constraint (SQL_IOVYEIUUVLRSRFUANXPRFKMID.PK_DEPT) violated

마찬가지로 DEPT에서 PK인 DEPTNO에 중복값을 갖도록 insert 하려니까 Error 발생!

 

select * from emp where hiredate = to_date('23-01-1982','dd-mm-yyyy');
select * from emp where to_char(hiredate,'yyyy') = 1981;
select * from emp where to_char(hiredate,'mm') = 04;
select * from emp where hiredate = to_date('1982/01/23','yy/mm/dd');

EMP table의 hiredate의 경우 Logical 스키마를 봤을 때 dtype이 date이다. 그 점을 활용해 data를 조회할 수 있는 방법은 위와 같다.

 

23-01-1982 data를 추출해낸 것

to_char를 이용해 'mm'이 4월인 Data를 추출

날짜를 기준으로 하고 싶으면 (select * from emp where to_char(hiredate,'dd') = 23;) 요런 구문도 가능

 

alter session set nls_date_format='dd/Mon/yyyy'; 
alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm';
select hiredate from emp;

 

Date 포맷을 바꿀 수도 있다. alter session set nls_date_format = 'dd-mm-yyyy'; 등으로 변경 가능!

 

 

Primary Key Constraint 탐구

create table hello (a int, b char(1), primary key(a));

select constraint_name from user_constraints where table_name = 'HELLO';
-- system-generated constraints name
-- instead, you can specify the constraint name explicitly

drop table hello;
create table hello (a int, b char(1), constraint hello_pk primary key(a));
select constraint_name from user_constraints where table_name = 'HELLO';

insert into hello values (1,'A');
insert into hello values (1,'A');
-- what happens? what violation?
insert into hello values (2,'A');

insert into hello values (1,'A');
insert into hello values (1,'A');

--> Unique Constraint violated Error가 뜬다.

insert into hello values (2,'A'); 는 정상적으로 동작한다. PK는 중복된 값이 있으면 안 된다는 것!

 

Unique Key Constraint 탐구

drop table hello;
create table hello (a int, b char(1), constraint hello_pk primary key(a), 
					constraint hello_uk unique(b));
insert into hello values (1,'A');
insert into hello values (2,'A');
select index_name from user_indexes where table_name = 'HELLO';
select * from hello;

 

ORA-00001: unique constraint (SQL_IOVYEIUUVLRSRFUANXPRFKMID.HELLO_UK) violated ORA-06512: at "SYS.DBMS_SQL"

 

char B까지 unique 처리 해주니까, B에도 중복값이 추가되려고 하면 ERROR가 발생해버린다.

(create table hello (a int, b char(1), constraint hello_pk primary key(a), 
constraint hello_uk unique(b));)

 

Foreign Key Constraint

 

create table parent (a int, b char(1), primary key(a));
create table child (c int, d int, 
		constraint child_fk foreign key(d) references parent(a));

-- or, you can use the schema change command 'alter table'
drop table child;
create table child (c int, d int);
alter table child add constraint child_fk foreign key(d) references parent(a);

insert into parent values (1,'A');

insert into child values (1,1);
insert into child values (1,null);
insert into child values (1,2);

Insert into child values (1,2)를 하려고 할 땐 Integrity Constraint violate error가 뜬다. Parent Key가 없다고 하는 ERROR

근데 FK인데도 NULL값은 추가가 되네??

 

delete from child where d = 2; -- child에 d=2가 없으니 Delete x
delete from parent where a = 1; -- ICs 위배 발생 -> violated - child record found

 

delete from child where d = 1; 
delete from parent where a = 1;

child에서 d=1(FK)를 먼저 삭제해 주고, parent에서 a=1(PK)를 삭제처리 하면 ERROR없이 잘 삭제가 된다.

 

 

Parents Table에 update & delete 발생 Case에 대한 탐구

 

drop table child;
create table child (c int, d int, 
		constraint child_fk foreign key(d) references parent(a) on delete cascade);

 

child table을 다시 만들어 주고, 이번엔 parents를 참조하는데, delete on cascade 조건을 더해본다.

 

drop table child;
drop table parent;

parent 먼저 drop하려고 하면, ( unique/primary keys in table referenced by foreign keys ) error가 뜬다. 그런데 child 먼저 drop하면 drop이 잘 이루어진다. Transaction 순서가 나를 구찮게 할 때 해결책은??

 

create table parent (a int, b char(1), primary key(a));
create table child (c int, d int, 
		constraint child_fk foreign key(d) references parent(a) deferrable);

set constraint child_fk deferred;
insert into child values (1,2);
insert into parent values (2,'B');
commit;

drop table child;
drop table parent;

핵심은 deferrable 이다. 코드를 commit 시킬 때 라인 by 라인 순서대로가 아니고 코드를 통으로 읽어서 한 번에 처리하기 때문에 이 전과 같이 순서에 의한 error를 피한다.

 

-- Oracle allows foreign key to refer to UNIQUE column as well as Primary Key.
-- Why is referenced attribute required to be PRIMARY KEY or UNIQUE? 
create table parent (a int, b char(1), primary key(a), unique(b));
create table child (c int, d char(1), 
		constraint child_fk foreign key(d) references parent(b));

referenced attribute(== Parent table)이 PK, Unique 설정이 필요한 이유?? 당연히 Child table의 FK가 Parent의 뭘 PK로 참조해서 Relation이 형성되는 지 알아야하기때문 아닐까?

 

DROP TABLE EMP2;
DROP TABLE DEPT2;
CREATE TABLE DEPT2
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT2 PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
CREATE TABLE EMP2
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP2 PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4) CONSTRAINT FK_MGR REFERENCES EMP2 ON DELETE CASCADE, -- Oracle Options: NO ACTION, CASCADE, SET NULL
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO2 REFERENCES DEPT2 ON DELETE CASCADE);

INSERT INTO DEPT2 VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT2 VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT2 VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT2 VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP2 VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP2 VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP2 VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP2 VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP2 VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP2 VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP2 VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP2 VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP2 VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP2 VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP2 VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP2 VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP2 VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP2 VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

DELETE FROM DEPT2 WHERE DEPTNO = 30;      -- Check Blake, Allen, Ward, James and Martin from EMP2 table
DELETE FROM EMP2  WHERE ENAME = 'JONES';  -- Check Scott, Ford from EMP2 table 
DELETE FROM DEPT2 WHERE DEPTNO = 10;      -- Check EMP2 table. What about DEPT2 table?

DROP TABLE EMP2;
DROP TABLE DEPT2;

 

PK/FK ICs 연습할 수 있는 EMP2 / DEPT2 Table예시다. LIVE SQL로 가지고 놀면 좋을듯하다.

 

 

Child Table에서 FK를 Delete On Cascade로 설정하고 Parent Table에서 PK를 지워보자

 

DELETE FROM DEPT2 WHERE DEPTNO = 30;

요 친구들도 같이 삭제 된다.

DELETE FROM EMP2  WHERE ENAME = 'JONES';  -- Check Scott, Ford from EMP2 table

이상하다 뭐지.. JONES만 삭제해줬는데, JONES의 DEPTNO 가 20이라 그런지 DEPTNO 20인 친구들이 EMP2에서 싸그리 사라졌다. 이게 cascade의 힘인가..

 

예상 시나리오 ) EMP2에서 Jones delete -> FK인 DEPTNO 하나가 사라짐

아.. 이게 아니고, EMP2 Table에서 Jones delete -> Jones tuple에 해당하는 MGR col도 사라짐 -> 근데 MGR col은 FK로 EMP2를 셀프참조하고 있던 col이다. 즉, 이 경우 Parent table이 EMP2 자신이 되어서 PK가 삭제되는 시나리오가 발동되는 것이다. 이 때문에 EMP2에서 Jones와 DEPTNO 같은 것들이 사라진듯? 근데 왜..? DEPTNO가 FK라서 같이 사라진건가.. 아직 모르겠다~

 

위 논리면 애초에 FORD나 이런애들 지울 때도 deptno 20인 애들 다 삭제되어야 하는데 그렇지 않다. 이유가 뭘까;;

JONES를 삭제할 때 SCOTT과 FORD도 함께 삭제되는 이유는 JONES가 이들의 관리자(MGR)로 설정되어 있기 때문입니다. 여기서 중요한 부분은 EMP2 테이블의 MGR 필드에 설정된 외래키 제약조건인 ON DELETE CASCADE입니다. ON DELETE CASCADE 제약조건은 참조된 행이 삭제될 때 참조하는 모든 행을 자동으로 삭제하도록 설정하는 옵션입니다. 이 경우, EMP2 테이블에서 MGR 필드는 EMP2 테이블의 EMPNO 필드를 참조하고 있습니다. 즉, EMP2 테이블의 어떤 직원이 다른 직원의 관리자인 경우, 관리자가 삭제되면 그 관리자 밑에 있는 모든 직원도 함께 삭제됩니다. 예를 들어, JONES(EMPNO 7566)는 SCOTT(EMPNO 7788)과 FORD(EMPNO 7902)의 관리자입니다. JONES가 EMP2 테이블에서 삭제될 때, ON DELETE CASCADE 조건으로 인해 SCOTT과 FORD도 자동으로 삭제되는 것입니다. 이로 인해 데이터베이스 내에서 관리 구조의 무결성을 유지할 수 있습니다.

 

살짝 민망 ㅎㅎ;; Jones가 이들의 MGR이라서 삭제된거였다. 이래서 King을 삭제하면 EMP2 Table이 싸그리 날라갔던거구나.. Jones 삭제 - Jones의 EMPNO도 삭제될 예정 -> EMP2의 MGR은 EMP2의 EMPNO를 참조하는 FK이다. -> 즉 Jones의 EMPNO는 MGR과 연결되어 있고, 이게 CASCADE로 삭제가 되어야 해서 

 

SELECT *
FROM EMP2
WHERE MGR = (SELECT EMPNO FROM EMP2 WHERE ENAME = 'JONES')
   OR ENAME = 'JONES';

EMP2 table에서 Jones 본인과 본인이 MGR로 있는 Tuple을 걸러내는 SQL문은 다음과 같다.

Where 절에 select 써도 되는구나..

 

일단 GPT의 힘을 빌리긴 했는데, 생각보다 SQL은 직관적으로 할 수 있는게 많다는 게 느껴진다.