[SQL] Constraint(선언적 무결성 제약 사항)

👀 유용한 정보

데이터 딕셔너리 조회

💡피드백 Tip

📘 Constraint (선언적 무결성 제약 사항)

데이터 무결성

NOT NULL

CHECK

Unique Key

Primary Key

Foreign Key

---

👀 유용한 정보

데이터 딕셔너리 조회

💡

USER_ : ~소유의 모든 USER_TABLES : 내 소유의 테이블 ↔ SELECT * FROM TAB : 내가 사용 가능한 테이블 USER_TAB_COLUMNS : 내소유의 테이블의 컬럼 정보 USER_INDEXES : 내 소유의 인덱스 USER_CONS_COLUMNS : 내 소유의 컬럼 제약 사항

select * from user_tables;
select * from tab;
select * from USER_TAB_COLUMNS;
select * from USER_INDEXES;
select * from user_cons_columns;

💡피드백 Tip

💡

테이블을 삭제하면 (DROP TABLE) : 테이블 생성시 정의했던 제약사항 등 모두 함께 삭제된다.

💡

제약 사항만 삭제하는 방법

ALTER TABLE 테이블이름
DROP CONSTRAINT 제약사항이름;

제약 사항만 추가하는 방법

ALTER TABLE 테이블이름
ADD CONSTRAINT 제약사항이름 제약조건;

💡

추후 프로젝트에 OAUTH 인증 활용 도입 DB 모델링 시, 제약 사항을 잘 걸자

---

📘 Constraint (선언적 무결성 제약 사항)

💡

무결성 제약사항 : 쓰레기 데이터(Business Rule을 위반하는 데이터)를 없애고 Business Rule을 따르는 데이터만을 가지도록 하기 위함

선언적 무결성 제약사항 : 정의 / 선언

데이터 무결성

💡

무결성 무효갱신으로 부터 테이블내의 데이터를 위/변조,훼손하지 못하도록 제약(Constraint)하여 데이터의 유효성, 일관성, 완전성, 정확성, 정밀성을 보장하는 성질 - 무결성을 지키는 것은 매우 중요하다!!

⭐ 무결성 제약사항 구현 방법 3가지 ⭐ ① 선언적 무결성 제약사항(Declarative Integrity Constraint) * DBMS에서 테이블 정의 시 P.K, U.K 등 제약사항 정의 ② Trigger (PL/SQL) * DBMS 레벨에서 PK/SQL 으로 제약사항 로직 구현 ③ Application Logic (Coding) * JAVA, C (어플리케이션 레벨에서 제약사항 구현) ⇒ 무결성 제약사항의 구현 및 검증은, 어플리케이션, DBMS 레벨 각각 모두 이중으로 구현하는 것이 좋다. 선언적 무결성 제약사항 선언적이란 프로그램 코딩을 하지 않고 테이블 생성시 또는 생성후 컬럼이나 테이블에 무결성 제약사항을 표기하여 정의(define)하는 방식 ① PRIMARY KEY : 대표성(여러 후보키 들 중), 고유성, 존재성(Not NULL)을 보장하며 테이블당 1개만 정의, Unique Index 자동 생성 , Unique Key + NOT NULL의 결합형태 ex) 아이디 ② UNIQUE KEY : 데이터의 고유성 보장, 테이블에 N개의 정의 가능, NULL 허용,Unique Index 자동 생성 ex) email, 휴대전화 번호 등 ③ CHECK : 값의 범위 나 조건을 지정,Boolean 연산 ④ NOT NULL : NULL(걀측치)을 허용하지 않는 필수 입력 사항 정의 ⑤ FOREIGN KEY : 테이블 개체간의 참조 관계 정의, 내용에 의한 참조

Primary Key는 테이블 당 1개, Unique Key는 테이블당 여러개를 가질 수 있다. ⇒ Primary Key의 대표성

• 회원 정보 중 유일성을 가진 후보키 ex) ID, SocialNo, Email, PhoneNo 등
  • 포털 사이트에서의 기본키 : ID
  • OAUTH에서의 기본키 : Email
  • 국가 기관 홈페이지에서의 기본키 : SocialNo

→ 이처럼 기본 키는 사용처에 따라 달라질 수 있다.

LEVEL 과 생성시기 LEVEL ① TABLE : 테이블에 정의하는 제약 사항으로 여러 컬럼이 제약 사항에 관여하는 경우 사용 ② COLUMN : 특정 컬럼에 정의하는 제약 사항 생성 시기 ① TABLE 생성시 생성 ② TABLE 생성후 임의의 시점에 추가

NOT NULL

💡

NOT NULL은 NULL을 허용하지 않는 즉 데이타가 필수적으로 존재하도록 하는 제약 사항 ① 테스트 대상 테이블 생성

CREATE TABLE CUSTOMER2(
ID VARCHAR2(8) NOT NULL,                             -- NOT NULL 제약 사항 정의
PWD VARCHAR2(8) CONSTRAINT CUSTOMER_PWD_NN NOT NULL, -- 해당 제약 사항의 이름을 명시
NAME VARCHAR2(20), -- 이름
SEX CHAR(1), -- 성별 [M|F] M:Male F: Female
AGE NUMBER(3) -- 나이
);

DESC CUSTOMER2

• NOT NULL은 일반적으로 제약 사항의 이름을 굳이 붙이지는 않는다.

• 제약 사항의 이름을 부여하지 않으면 DBMS가 임의로 설정한다.

②

INSERT INTO CUSTOMER(ID,PWD,NAME,SEX, AGE) VALUES('xman','ok','kang', 'M',21);
INSERT INTO CUSTOMER(ID,PWD,NAME,SEX,AGE) VALUES('XMAN','no','kim', 'T',-20);

• 정상적 입력

③

INSERT INTO CUSTOMER(ID,NAME,AGE) VALUES('zman','son',99);
INSERT INTO CUSTOMER(ID,PWD,NAME,AGE) VALUES('rman',NULL,'jjang',24);
INSERT INTO CUSTOMER(ID,PWD,NAME,AGE) VALUES('', 'pwd' ,'jjang',24);

• PWD,SEX 컬럼에 암시적/명시적 NULL을 삽입하려 하지만 PWD컬럼의 NOT NULL 제약사항으로 INSERT는 에러 발생.

• DML 연산시 데이터를 변경함에 따라 부적절한 데이터가 발생할수 있다. DML 연산으로 데이터를 변경 하기전에 무결성 제약사항을 위반하는지 여부를 점검한 후 무결성 제약사항을 위반하게 되면 해당 DML 연산을 수행하지 않고 취소 처리.

④

UPDATE CUSTOMER SET PWD = NULL WHERE ID = 'XMAN'; -- ID가 XMAN인 ROW만 수정
SELECT * FROM CUSTOMER;

• UPDATE연산은 PWD의 NOT NULL 제약사항으로 에러가 발생

⑤ 데이터딕셔너리(시스템 카타로그)에서 제약사항 확인하기

-- CUSTOMER2 테이블에 대한 제약 사항 확인
SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CUSTOMER';

--
SELECT TABLE_NAME,CONSTRAINT_NAME,POSITION,COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'CUSTOMER' ORDER BY CONSTRAINT_NAME,POSITION;

💡

제약 사항을 충족시키지 못하면 DML 연산이 거부되는 것을 볼 수 있었다.

CHECK

💡

❏ CHECK

• 설정된 조건을 체크하여 Boolean 결과가 TRUE인 경우에만 DML연산 허용.

① 테이블 생성후 제약사항 추가

ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_SEX_CK CHECK (SEX IN ('M','F')); -- 조건

• SQL 오류: ORA-02293: (SCOTT.CUSTOMER_SEX_CK)을 검증할 수 없습니다.

• 잘못된 제약 사항 확인

💡

테이블 생성시 제약사항 정의 / 기존 생성된 테이블에 제약사항 추가 가능 제약사항을 추가할 때 기존 데이터를 검증한다. 이미 기존에 추가할 제약사항에 위반하는 데이터가 있을 경우, 제약 사항 추가 불가.

②

UPDATE CUSTOMER SET SEX='M' WHERE SEX='T';
COMMIT;
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_SEX_CK CHECK (SEX IN ('M','F'));

• 제약사항 추가시 기존의 데이터에 대한 무결성 검사.

• 기존 데이터가 추가되는 제약사항을 위반하여 에러가 발생하여 데이터 처리후 제약사항을 추가한다.

③

INSERT INTO CUSTOMER(ID,PWD,NAME,SEX, AGE) VALUES('xman','ok','kang', 'M',21);
INSERT INTO CUSTOMER(ID,PWD,NAME,SEX,AGE) VALUES('xman','ok', 'jjang','M',20);

• ID가 중복되지만 입력

④

 INSERT INTO CUSTOMER(ID,PWD,NAME,AGE) VALUES('asura','ok', 'joo',99);

-성별(SEX) 컬럼에 NOT NULL 제약사항이 없기 때문에 NULL 입력. → CHECK( IN(’M’, ‘F’))의 제약사항을 추가했지만, NULL은 거르지 못한다.

→ NULL에 대한 제약사항은 꼭 NOT NULL을 통해서만 가능하다.

⑤

INSERT INTO CUSTOMER(ID,PWD,NAME,SEX,AGE) VALUES('harisu','ok', 'susu','T',33);

-성별(SEX) 컬럼에 Check 제약사항으로 M 과 F만 입력 가능 에러발생

⑥

INSERT INTO CUSTOMER(ID,PWD,NAME,SEX,AGE) VALUES('shinsun','ok', '도사', 'M',999);

• AGE컬럼은 NUMBER(3)으로 정의되어 999가 입력된다.

⑦

UPDATE CUSTOMER SET AGE = AGE + 1;

• 1년이 지나 고객의 나이를 +1 하려고 UPDATE 연산을 수행하는데 회원 shisun은 1000으로 자리수를 초과하는 에러가 발생하여 문장 수준 롤백(Statement Level Rollback) 발생.

Unique Key

💡

Unique Key

• 컬럼 데이터의 고유성을 보장하고 NULL을 허용하여 데이터가 없을수 있지만 데이터가 존재하는 경우 고유해야 한다.

①

• CUSTOMER 테이블 삭제후 다시 생성

• ID는 필수 입력사항이면서 고유성을 가져야 해서 NOT NULL + UNIQUE 제약사항 정의

• MOBILE(헨드폰)은 헨드폰이 없는 고객도 있지만 헨드폰이 있는경우 고유한 번호를 가져야 하기 때문에 UNIQUE 제약사항 정의

DROP TABLE CUSTOMER;

CREATE TABLE CUSTOMER(
		ID VARCHAR2(8) NOT NULL CONSTRAINT CUSTOMER_ID_UK UNIQUE,
		PWD VARCHAR2(8) NOT NULL,
		NAME VARCHAR2(20),
		SEX CHAR(1) DEFAULT 'M' -- 디폴트
		CONSTRAINT CUSTOMER_SEX_CK CHECK (SEX IN ('M','F')),
		MOBILE VARCHAR2(14) UNIQUE,
		AGE NUMBER(3) DEFAULT 18
);

② 암시적 NULL → DEFAULT 값 치환

INSERT INTO CUSTOMER(ID,PWD,NAME,MOBILE, AGE) VALUES('xman','ok','kang', '011-3333',21);

• 성별 컬럼에 암시적으로 NULL이 지정되지만 DEFAULT에 의해서 'M' 값이 저장된다.

③

INSERT INTO CUSTOMER(ID,PWD,NAME, MOBILE,AGE) VALUES('XMAN','yes','kim','011-3334',33);

• 데이터는 대소문자를 구분하기 때문에 xman 과 XMAN은 다른 데이터

④

INSERT INTO CUSTOMER(ID,PWD,NAME, MOBILE,AGE) VALUES('xman','yes','lee', '011-3335',-21);

• ID중복으로 Unique 제약 사항 위반 에러 발생 INSERT 실패(회원가입 실패)

⑤

INSERT INTO CUSTOMER(ID,PWD,NAME, MOBILE,AGE) VALUES('yman','yes','lee', '011-3333',28);

• 헨드폰 번호 중복으로 Unique 제약 사항 위반 에러 발생 INSERT 실패(회원가입 실패)

⑥

 INSERT INTO CUSTOMER(ID,PWD,NAME, MOBILE) VALUES('무명인','yes',NULL, NULL);

• ID컬럼의 NOT NULL 제약사항으로 NULL을 허용하지 않는다.

⑤

ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_NAME_SEX_UK UNIQUE(NAME,SEX); -- 조합의 고유값
ALTER TABLE CUSTOMER MODIFY(NAME NOT NULL);

• 테이블 생성후 제약사항 신규 추가

• 2개 컬럼을 조합하여 Unique제약 사항을 생성

⑥

INSERT INTO CUSTOMER(ID,PWD,NAME, SEX) VALUES('rman','yes','syo', 'M'); -- 1
INSERT INTO CUSTOMER(ID,PWD,NAME, SEX ) VALUES('Rman','yes','syo', 'F'); -- 2
INSERT INTO CUSTOMER(ID,PWD,NAME, SEX) VALUES('RmaN','yes','syo', 'M'); -- 3
SELECT * FROM CUSTOMER;

• ID 데이터 대소문자 - 의미적으로 같으므로 같은 의미

• NAME+SEX 조합의 Unique 제약사항 → 이름이 같지만 다른 데이터로 처리

⑦ INDEX 생성 여부 확인

SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES
WHERE TABLE_NAME = 'CUSTOMER';
SELECT INDEX_NAME,COLUMN_POSITION,COLUMN_NAME FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'CUSTOMER' ORDER BY INDEX_NAME,COLUMN_POSITION;

💡

UNIQUE 키를 정의하면 해당 컬럼에 Unique Index가 자동 생성된다. → Index : Quick Search 용도

[참고]

UNIQUE 제약사항 정의시 해당 컬럼에 INDEX 가 자동 생성된다. INDEX는 Data에 대한 빠른 접근(Quick Search)를 할수 있도록 하는 데이터베이스 Object로 Unique 제약사항 정의시 Index 자동 생성되는 이유는 데이터 입력,수정시 중복 여부를 빠르게 확인 하기 위해서 생성.

Primary Key

💡

PRIMARY는 주요한, 기본적인 이라는 사전적 의미를 가지며 데이터베이스에서 PRIMARY KEY는 주키 또는 기본키 라고 한다. 기본키는 테이블내의 각 레코드(행)을 고유하게 식별하는 값을 가진 컬럼(들)의 조합으로 컬럼 데이터의 고유성(Uniqueness) 과 존재성(Not Null)을 동시에 보장하는 제약사항.

Primary Key의 기본 특징

• 최소성

• 고유성 - Unique

• 존재성 - NOT NULL

• 불변성 - 값이 변하지 않아야 한다 (부모 키가 변경되면 자식 FK가 모두 변해야 하므로)

테이블내에 레코드(행)을 고유하게 식별 할수 있는 식별자 후보가 여러 개 있는 경우 대표성을 가진 후보가 PRIMARY KEY(기본키)가 되고 나머지 후보자들은 UNIQUE KEY가 된다. 기본키는 테이블에 1개만 정의할수 있고 UNIQUE KEY는 테이블에 N개 정의 가능.

①

ID는 필수 입력사항이면서 고유성을 가져야 해서 PRIMARY KEY로 제약사항 정의 MOBILE(핸드폰)은 헨드폰이 없는 고객도 있지만 핸드폰이 있는경우 고유한 번호를 가져야 하기 때문에 UNIQUE KEY제약사항 정의.

DROP TABLE CUSTOMER;

CREATE TABLE CUSTOMER(
    ID VARCHAR2(8) CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY, -- 제약 사항 이름 정의 
    PWD VARCHAR2(8) NOT NULL,
    NAME VARCHAR2(20),
    SEX CHAR(1) DEFAULT 'M'
    CONSTRAINT CUSTOMER_SEX_CK CHECK (SEX IN ('M','F')),
    MOBILE VARCHAR2(14) CONSTRAINT CUSTOMER_MOBILE_UK UNIQUE,
    AGE NUMBER(3) DEFAULT 18
);

② 성별(SEX),나이(AGE) 컬럼에 정의된 DEFAULT는 입력시 컬럼의 값이 지정되지 않는 암시적인 NULL이 지정되면 NULL 대신 저장하는 값으로 성별에는 'M', 나이는 18 입력

INSERT INTO CUSTOMER(ID,PWD,NAME,MOBILE) VALUES('zman','ok','한국', '011');

③ MOBILE 컬럼에는 UNIQUE KEY 제약사항이 정의되었지만 NULL 허용.

INSERT INTO CUSTOMER(ID,PWD,NAME) VALUES('xman','ok','king');

④ ID중복으로 에러가 발생한다. SQL 오류: ORA-00001: 무결성 제약 조건(SCOTT.CUSTOMER_ID_PK)에 위배됩니다

1. 00000 - "unique constraint (%s.%s) violated"

INSERT INTO CUSTOMER(ID,PWD,NAME) VALUES('xman','power','zzang');

⑤ 데이터는 대소문자를 구분한다. 'xman' 와 'Xman'은 다른 데이터로 중복 에러가 발생하지 않는다.

INSERT INTO CUSTOMER(ID,PWD,NAME) VALUES('Xman','korea','dbzzang');

→ 대소문자 중복 해결 방법?

⑥ VALUES절에 함수 사용 가능하다, ID 중복으로 무결성 위반 에러 발생

INSERT INTO CUSTOMER(ID,PWD,NAME) VALUES(lower('xMan'),'ok','zzang');

→ 사용자 입력한 모든 데이터 함수 처리 (DBMS level에서 쿼리 이용 데이터 변환 처리)

💡

어플리케이션 레벨, DBMS 레벨 에서의 처리?

⑦ INSERT시 컬럼 생략시 해당 컬럼에 암시적 NULL이 지정된다. PRIMARY KEY는 NULL을 허용하지 않는다. SQL 오류: ORA-01400: NULL을 ("SCOTT"."CUSTOMER"."ID") 안에 삽입할 수 없습니다.

INSERT INTO CUSTOMER(PWD,NAME) VALUES('ok','kim');

• 존재성 때문에 에러가 발생

⑧ PRIMARY KEY의 유일성(UNIQUENESS) 과 존재성(NOT NULL)을 위반 하는 경우 UPDATE 명령이 수행되지 않는다.

UPDATE CUSTOMER SET ID = NULL; -- 존재성(NOT NULL)
UPDATE CUSTOMER SET ID = 'XMAN'; -- 유일성(UNIQUENESS) / 고유성 위배

⑨

1) USER_CONSTRAINTS는 사용자 소유(Owner)의 모든 제약사항(CONSTRAINT)를 조회 할수 있다.

SELECT * FROM USER_CONSTRAINTS;

2) USER_CONS_COLUMNS는 제약사항에 관련된 컬럼(COLUMN)의 정보를 조회할수 있다.

SELECT * FROM USER_CONS_COLUMNS;

3) USER_INDEXES는 사용자 소유(Owner)의 모든 인덱스(INDEX)를 조회 할수 있다

SELECT * FROM USER_INDEXES;

4) USER_IND_COLUMNS는 인덱스에 관련된 컬럼(COLUMN)의 정보를 조회할수 있다.

SELECT * FROM USER_IND_COLUMNS;

개발이나 튜닝시 빈번하게 사용하게됨으로 실습을 통해 익혀두어야 한다.

Foreign Key

💡

Foreign Key

• 연관성 있는 테이블간(내) 참조 무결성(Referential Integrity) 보장.

• 참조 무결성이란 테이블 사이의 관계에서 발생한다. 이전 예제를 보면 부서 테이블과 사원 테이블은 부서번호라는 공통의 데이터 속성을 가지고 관계 형성.

• 부서번호 속성(P.K or U.K)은 원래 부서 개체(테이블)에서 정의 된후 사원 개체(테이블)와 관계를 형성하기 위해 사원 개체(테이블)에게 상속된(비식별관계) 속성. *각 테이블에 존재하는 공통된 데이터 속성간의 데이터 무결성을 보장하는 것이 참조 무결성 제약 사항.

①

CREATE TABLE 부서(부서번호 VARCHAR2(2) CONSTRAINT 부서_부서번호_PK PRIMARY KEY,
									부서명 VARCHAR2(10) CONSTRAINT 부서_부서명_NN NOT NULL
);


CREATE TABLE 사원(사번 VARCHAR2(8) PRIMARY KEY,
									이름 VARCHAR2(10),
									부서번호 VARCHAR2(2) , -- Table Level 제약사항
									CONSTRAINT 사원_부서_부서번호_FK FOREIGN KEY(부서번호)
											REFERENCES 부서(부서번호) [ON DELETE CASCADE | SET NULL]
);

② DESCRIBE 부서 // not null , 축약

desc 사원

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,
			 TABLE_NAME,SEARCH_CONDITION,R_CONSTRAINT_NAME,DELETE_RULE,STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('부서','사원');
-- CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME,DELETE_RULE 관찰

③ 부서 데이터가 존재 하지 않는 상황에서 사원 정보 입력시 참조 무결성 에러 발생

INSERT INTO 사원(사번,이름,부서번호) VALUES('XMAN', 'TUNER','10');

• SQL 오류: ORA-02291: 무결성 제약조건(SCOTT.사원_부서_부서번호_FK)이 위배되었습니다. 부모 키가 없습니다

INSERT INTO 부서(부서번호,부서명) VALUES('10','관리'); -- 부서(Parent data) 정보 입력
INSERT INTO 부서(부서번호,부서명) VALUES('20','전산');
INSERT INTO 부서(부서번호,부서명) VALUES('50','영업');

④ 10번,20번 부서에 근무하는 사원 // 사원(Child data) 정보 입력

INSERT INTO 사원(사번,이름,부서번호) VALUES('XMAN','TUNER',10);
INSERT INTO 사원(사번,이름,부서번호) VALUES('YMAN','DBA',20);

⑤ 30번 부서(존재하지 않는 부서)에 근무하는 사원 입력시 참조 무결성 에러 발생

INSERT INTO 사원(사번,이름,부서번호) VALUES('ZMAN', 'DEVELOPER',30);

• SQL 오류: ORA-02291: 무결성 제약조건(SCOTT.사원_부서_부서번호_FK)이 위배되었습니다. 부모 키가 없습니다

⑥ 근무자가 (Child data) 없는 50번부서 폐지

DELETE FROM 부서 WHERE 부서번호 = 50;

⑦ 근무자가 있는 10번부서 폐지시 참조 무결성으로 에러 발생.

-- on delete

ⓐrestrict (default)

ⓑ cascade(부모 데이터 삭제시 자식도 삭제)

ⓒ set null (자식 데이터 null 처리)

DELETE FROM 부서 WHERE 부서번호 = 10;

• SQL 오류: ORA-02292: 무결성 제약조건(SCOTT.사원_부서_부서번호_FK)이 위배되었습니다. 자식 레코드가 발견 되었습니다 SELECT * FROM 사원; // 데이터관찰

⑧ 근무자가 있는 10번부서 폐지 방법은? ⓐ on delete restrict ➔ 해당 부서 근무자를 퇴사(DELETE)처리 하거나 다른 부서로 이관(UPDATE)하고 폐지한다.

UPDATE 사원 SET 부서번호=20 WHERE 부서번호=10;
DELETE FROM 부서 WHERE 부서번호 = 10;

---

ⓑ on delete cascade DROP TABLE 사원; CREATE TABLE 사원 ~ on delete cascade 생성후 ② ~ ⑦ 재 실행 , ③ 생략

ⓒ set null DROP TABLE 사원; CREATE TABLE 사원 ~ on delete set null 생성후 ② ~ ⑦ 재 실행 , ③ 생략

---

Uploaded by N2T