[SQL] 제약 조건

Notice

Recent Posts

Recent Comments

Link

« 2025/04 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Tags more

Archives

Today

Total

관리 메뉴

나의 개발일지

[SQL] 제약 조건 본문

Oracle SQL

[SQL] 제약 조건

YoonJuHan 2023. 6. 2. 17:59

제약 조건의 개념

테이블의 무결성 유지를 위해 행이 삽입, 갱신 또는 삭제될 때 마다 테이블의 데이터에 규칙을 적용하는 것
1. 제약 조건을 만족하는 작업만 DML이 수행되므로 테이블에 잘못된 데이터가 입력되는 것을 방지
2. 하나의 열에 하나 이상의 제약 조건이 지정 가능
Oracle 데이터베이스 레벨에서 선언 가능한 제약 조건 유형
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

NOT NULL 제약 조건

해당 열에 NULL 값 생성 방지
열의 NOT NULL 여부는 DESCRIBE 명령의 결과에서 확인 가능

UNIQUE 제약 조건 (고유 키)

UNIQUE 제약 조건이 정의된 열 또는 조합 열의 값이 고유하게 유지
1. 입력 또는 변경되는 값이 고유한지 여부를 체크하므로 지정된 열 또는 조합 열에서 중복된 값을 가질 수 없음
고려사항
1. UNIQUE 제약 조건을 지정한 열에 NULL 값 허용
2. UNIQUE 제약 조건을 지정했지만 NOT NULL 속성도 필요한 열이라면 두 가지 제약 조건을 모두 정의
3. UNIQUE 인덱스 자동 생성

PRIMARY KEY 제약 조건 (기본 키)

개체 무결성 제약 조건
1. 테이블의 각 행을 고유하게 식별하는 열 또는 조합 열
2. 테이블의 각 행을 고유하게 식별할 수 있도록 하기 위하여 사용
3. 제약 조건의 속성 자체는 NOT NULL과 UNIQUE
기본키 고려사항
1. 테이블 당 한 번만 정의 가능
2. (참고) 다른 제약 조건은 하나의 테이블에 여러 번 정의 가능
3. UNIQUE 인덱스 자동 생성

FOREIGN KEY 제약 조건 (외래 키)

참조 무결성 제약 조건
1. 동일한 테이블이나 다른 테이블에 있는 기본 키 또는 고유 키와의 관계를 설정하는 것
2. 외래 키 값은 부모 테이블의 기존 값 가운데 하나와 일치하거나 또는 NULL이 올 수 있음
관계
1. 부모 테이블 : 참조되는 열을 포함하는 테이블
2. 자식 테이블 : 외래 키가 정의된 테이블
관계의 예시
1. 키 정의 예시
  1. y_dept 테이블의 dept_id 열을 PRIMARY KEY 로 정의
  2. y_emp 테이블의 dept_id 열을 참조 외래키로 정의
2. 관계
  1. y_emp 테이블에 새 사원 정보를 입력하거나 기존 사원 정보를 변경할 때마다 y_emp 테이블의 dept_id 는 y_dept 테이블의 dept_id 열의 값을 참조해야 함

CHECK 제약 조건

각 행이 만족 시켜야 하는 조건을 정의하는 것
1. 조건을 지정하는 구문은 SELECT 문장에서의 WHERE 절과 동일한 구조를 사용
2. 데이터 입력, 수정 시 사용자의 실수로 유효하지 않은 값이 입력되는 것을 방지
예시
1. CHECK (salary BETWEEN 190 AND 990)
2. 회사에서 최저 급여가 190만원이고 최대 급여가 990만원인 경우 급여담당자의 실수로 99만원 또는 1900만원이 입력되는것을 방지

제약 조건과 인덱스

PRIMARY KEY와 UNIQUE 제약 조건 정의 시
1. DBMS가 데이터 중복 체크 수행을 위해 UNIQUE INDEX 자동 생성
2. 제약 조건 정의로 인해 자동 생성된 인덱스는 DROP INDEX 명령으로 삭제 불가

제약 조건의 생성 및 관리

테이블이 생성될 때 함께 정의
1. CREATE TABLE 명령에 포함
테이블이 생성된 후에 추가 정의
1. ALTER TABLE 명령으로 정의
제약 조건 정보는 데이터 딕셔너리에 저장
1. 사용자는 USER_CONSTRAINTS 데이터 딕셔너리 쿼리를 통해 특정 테이블에 정의된 제약 조건 검토 가능

테이블 생성 시 제약 조건 정의

테이블을 생성하면서 제약 조건 정의
1. 열 레벨 정의
2. 테이블 레벨 정의
3. NOT NULL 제약 조건은 열 레벨에서만 정의
구문

고객 등급별 등급 이름과 할인율을 저장하고 있는 cust_grade 테이블의 내용을 확인, 이 테이블은 cust_level 열에 PRIMARY KEY가 정의되어 있다.

SELECT * FROM cust_grade;

cust_grade 테이블의 기본키를 참조하는 외래키를 포함하고 있는 customers 테이블을 제약 조건을 정의하며 생성

CREATE TABLE customers (
cust_id NUMBER(6) CONSTRAINT cust_custid_pk PRIMARY KEY,
cust_name VARCHAR2(40) CONSTRAINT cust_name_nn NOT NULL,
phone_no VARCHAR2(15) NOT NULL,
email VARCHAR2(30),
birth_date DATE,
join_date DATE DEFAULT SYSDATE,
cust_level NUMBER(1) DEFAULT 9,
CONSTRAINT cust_level_fk FOREIGN KEY (cust_level) REFERENCES cust_grade(cust_level)
);

DESC customers;

제약 조건의 이름

제약 조건에 의미 있는 이름을 지정 권장
1. 사용자가 데이터 딕셔너리로부터 참조할 때 유용
2. 제약 조건의 이름 지정 형식으로 "테이블이름_열이름_제약 조건 유형"을 함축적으로 사용하는 것이 일반적
  1. 예) emp_empid_pk, emp_deptid_fk
제약 조건에 이름 지정을 생략한 경우
1. 오라클 서버가 SYS_Cn 형식의 이름을 자동으로 지정

데이터 딕셔너리 USER_CONSTRAINTS 로부터 CUSTOMERS 테이블 관련 제약 조건을 참조

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'CUSTOMERS';

기존 테이블에 제약 조건 추가

ALTER TABLE 명령문을 ADD 절과 함께 사용하여 기존 테이블에 제약 조건을 추가
기존 열에 NOT NULL 제약 조건을 추가하고자 한다면 ALTER TABLE 문의 MODIFY 절을 사용
구문

ALTER TABLE table명
ADD [CONSTRAINT constraint_name] type (column [,...]);

y_emp 테이블의 emp_id 열과 y_dept 테이블의 dept_id 열에 각각 PRIMARY KEY를 정의

ALTER TABLE y_emp
ADD CONSTRAINT emp_empid_pk PRIMARY KEY (emp_id);

ALTER TABLE y_dept
ADD CONSTRAINT dept_deptid_pk PRIMARY KEY (dept_id);

FOREIGN KEY의 추가 옵션

제약 조건에 의해 수행되는 작업을 제한하기 위해 추가하는 특별한 옵션
1. ON DELETE CASCADE
  1. 부모 테이블의 행이 삭제될 경우 자식 테이블의 종속 행도 삭제
2. ON DELETE SET NULL
  1. 부모 값이 제거되는 경우 외래 키 값을 NULL로 변환
ON DELETE ~ 절 생략 시 기본 값
1. 자식 테이블에서 참조되는 부모 테이블의 행 삭제 불가

y_emp 테이블의 dept_id 열에 y_dept 테이블의 dept_id 열을 참조하는 FOREIGN KEY를 생성하고, 근무하던 부서가 삭제되는 경우 해당 부서 사원의 부서번호가 NULL 값으로 변경되도록 옵션을 추가

ALTER TABLE y_emp
ADD CONSTRAINT emp_deptid_fk FOREIGN KEY (dept_id)
REFERENCES y_dept(dept_id)
ON DELETE SET NULL;

SELF REFERENCE FOREIGN KEY

하나의 테이블에 두 가지 제약 조건이 함께 정의되는 것으로 자체 참조 외래키라고도 함
FOREIGN KEY 가 동일한 테이블의 PRIMARY KEY 를 참조하는 것

y_emp 테이블에는 mgr_id 열이 있다. 특정 사원들의 관리자도 회사의 직원이어야 하므로 동일 테이블의 PRIMARY KEY 인 emp_id 열을 참조하는 외래키를 정의할 수 있다.

ALTER TABLE y_emp
ADD CONSTRAINT emp_mgrid_fk FOREIGN KEY (mgr_id)
REFERENCES y_emp(emp_id);

y_emp 테이블의 salary 열에 130보다 작거나 1500보다 많은 급여가 저장될 수 없도록 CHECK 제약 조건을 추가

ALTER TABLE y_emp
ADD CONSTRAINT emp_sal_ck CHECK (salary BETWEEN  130 AND 1500);

y_emp 테이블의 emali 열에 UNIQUE 제약 조건을 추가하여 중복이 일어나지 않도록 한다.

ALTER TABLE y_emp
ADD CONSTRAINT emp_email_uk UNIQUE (email);

제약 조건의 사용

제약 조건이 정의된 테이블은 테이블에 DML 또는 DDL이 수행될 때마다 오라클 서버에 의해 평가
제약 조건에 맞지 않은 업무는 오류를 반환
사용자가 수행하는 작업 가운데 제약 조건과 관련하여 자주 발생하는 오류
1. 부모 테이블 : child record found
2. 자식 테이블 : parent key not found

자식 테이블 y_emp 에서 참조 되는 부모 테이블의 y_dept 행에 UPDATE 를 수행

UPDATE y_dept
SET dept_id = 1300
WHERE dept_id = 200;

자식 테이블에서 참조 되는 부모 테이블의 행을 DELETE 한다.
- y_emp 테이블에 FOREIGN KEY 생성 시 ON DELETE SET NULL 옵션을 주었기 때문에 부모 키 행을 삭제 할 수 있다. 옵션을 주지 않았다면 부모 키 행은 삭제 불가

DELETE FROM y_dept
WHERE dept_id = 300;

y_emp 테이블에서 dept_id가 300인 행들은 모두 함께 NULL로 변경되었으며 트랜잭션을 ROLLBACK 하면 원래의 값으로 복원된다.

SELECT emp_id, emp_name, dept_id
FROM y_emp;

ROLLBACK;

SELECT emp_id, emp_name, dept_id
FROM y_emp;

자식 테이블에서 참조하는 행을 포함한 부모 테이블에 DROP을 수행하면 다음과 같은 오류가 발생

DROP TABLE y_dept;

자식 테이블에서 새 행을 입력하거나 FOREIGN KEY 제약 조건이 정의된 열의 데이터를 변경할 때 부모 테이블에서 참조할 데이터를 발견하지 못한면 다음과 같은 오류 발생

UPDATE y_emp
SET dept_id = 1300
WHERE emp_id = 2002;

제약 조건의 관리 업무

ALTER TABLE 명령 사용
제약 조건의 추가 또는 삭제
1. 기존 테이블에 제약 조건의 추가, 삭제 가능
상태 활성화 또는 비활성화
1. ENABLE 또는 DISABLE
제약 조건의 이름 변경

제약 조건의 활성화와 비활성화

ALTER TABLE 문을 DISABLE / ENABLE 절과 함께 사용
1. CASCADE 절을 추가하면 종속 무결성 제약 조건을 함께 비활성화
2. UNIQUE 또는 PRIMARY KEY 제약 조건을 비활성화하면 자동으로 생성되었던 관련 인덱스가 삭제
3. UNIQUE KEY 또는 PRIMARY KEY 제약 조건을 활성화하면 관련 인덱스 자동으로 다시 생성
구문

ALTER TABLE table명
[ENABLE | DISABLE] CONSTRAINT constraint_name [CASCADE];

데이터 딕셔너리 USER_CONSTRAINTS를 조회하여 기본키 - 외래키 관계에 있는 y_dept와 y_emp 테이블의 제약 조건 상태를 확인
- 모두 ENABLE 상태

SELECT table_name, constraint_name, status
FROM user_constraints
WHERE table_name IN ('Y_EMP', 'Y_DEPT');

y_dept 테이블의 PRIMARY KEY를 비활성화
- FOREIGN KEY가 참조 중인 PRIMARY KEY DISABLE 시 오류 발생

ALTER TABLE y_dept DISABLE CONSTRAINT dept_deptid_pk;

다시 y_dept 테이블의 PRIMARY KEY를 CASCADE 옵션을 사용하여 비활성화

ALTER TABLE y_dept DISABLE CONSTRAINT dept_deptid_pk CASCADE;

데이터 딕셔너리 USER_CONSTRAINTS를 조회하여 기본키 - 외래키 관계에 있는 y_dept와 y_emp 테이블의 제약 조건 상태를 확인
- 비활성화되는 PRIMARY KEY에 종속적인 FOREIGN KEY는 자동으로 함께 비활성화됨

SELECT table_name, constraint_name, status
FROM user_constraints
WHERE table_name IN ('Y_EMP', 'Y_DEPT');

PRIMARY KEY가 비활성화된 상태에서 y_dept 테이블은 잘못된 데이터가 아무 오류 없이 저장되는 것을 확인

INSERT INTO y_dept(dept_id, dept_name)
VALUES(200, ' 서비스');

COMMIT;

SELECT * FROM y_dept;

y_dept 테이블의 PRIMARY KEY를 활성화하려고 시도하면 중복되는 데이터가 발견되어 오류가 난다.

ALTER TABLE y_dept ENABLE PRIMARY KEY;

잘못된 데이터를 삭제하여 오류를 수정, 값이 중복되는 데이터를 삭제하는 경우 각 행의 고유 정보인 ROWID를 활용할 수 있다.

SELECT dept_id, dept_name, rowid
FROM y_dept;

DELETE FROM y_dept
WHERE rowid = 'AAAE5jAAEAAAAGGAAH';

COMMIT;

y_dept 테이블의 PRIMARY KEY를 다시 활성화

ALTER TABLE y_dept ENABLE PRIMARY KEY;

y_dept 테이블의 PRIMARY KEY 비활성화 시 함께 비활성화된 y_emp의 FOREIGN KEY의 상태는 여전히 비활성화 상태

SELECT table_name, constraint_name, status
FROM USER_CONSTRAINTS
WHERE table_name IN ('Y_EMP','Y_DEPT') ;

CASCADE 옵션으로 비활성화했던 기본키 제약 조건을 다시 활성화할 경우 함께 비활성화 되었던 종속된 외래 키는 자동으로 활성화되지 않으므로 별도로 활성화 해야 한다.

ALTER TABLE y_emp ENABLE CONSTRAINT emp_deptid_fk;

제약 조건의 삭제

ALTER TABLE 문으로 제약 조건 삭제
CASCADE 옵션을 사용
1. 종속 제약 조건을 모두 함께 삭제
구문

ALTER TABLE table명
DROP PRIMARY KEY | UNIQUE (column_name) |
CONSTRAINT constraint_name [CASCADE];

데이터 딕셔너리 USER_CONSTRAINTS에서 먼저 제약 조건의 이름을 확인하고 제약 조건을 삭제, y_emp 테이블의 PRIMARY KEY를 종속 제약 조건과 함께 삭제

SELECT constraint_name, r_constraint_name, status
FROM user_constraints
WHERE table_name = 'Y_EMP';

ALTER TABLE y_emp DROP PRIMARY KEY CASCADE;

제약 조건이 정의된 열의 삭제

ALTER TABLE 명령문에 DROP COLUMN 절을 사용 시 CASCADE CONSTRAINTS 옵션 필요
1. 삭제할 열이 참조 무결성 제약 조건에 의해 참조 되는 열인 경우
2. 조합 열에 대한 제약 조건이 정의되어 있는 경우
구문

ALTER TABLE table명
DROP COLUMN column명 CASCADE CONSTRAINTS;

dept400 테이블의 구조와 제약 조건을 조회
- mgr_id가 id를 참조하고 있다.

DESC dept400;

SELECT constraint_name, r_constraint_name
FROM user_constraints
WHERE table_name = 'DEPT400';

CASCADE CONSTRAINTS 옵션을 사용해 dept400 테이블에서 id열을 삭제

ALTER TABLE dept400 DROP COLUMN id CASCADE CONSTRAINTS;

삭제된 열은 id 열 하나지만 테이블에 정의된 제약 조건은 id 열과 관련된 제약 조건들이 모두 함께 삭제

DESC dept400;

SELECT constraint_name, r_constraint_name FROM user_constraints
WHERE table_name = 'DEPT400';

제약 조건의 이름 변경

Oracle Database 11g 부터 가능
1. ALTER TABLE 문의 RENAME CONSTRAINT 절 사용
구문

ALTER TABLE table명
RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;

dept400 테이블에 정의되어 있는 시스템이 생성한 제약 조건의 이름을 변경

SELECT constraint_name, r_constraint_name
FROM user_constraints
WHERE table_name = 'DEPT400';

ALTER TABLE dept400
RENAME CONSTRAINT SYS_C007008
TO dept400_name_nn;

제약 조건 관련 정보 보기

DESCRIBE 명령
1. NOT NULL 제약 조건 확인
2. PRIMARY KEY 제약 조건이 정의된 열도 NOT NULL로 표시
USER_CONSTRAINTS 데이터 딕셔너리
1. 테이블의 모든 제약 조건을 보기 위하여 조회
USER_CONS_COLUMNS 데이터 딕셔너리
1. 제약 조건과 연관된 열의 이름 확인
2. 시스템이 할당한 이름을 사용하는 제약 조건에 유용하게 사용
3. USER_CONS_COLUMNS와 USER_CONSTRAINTS를 CONSTRAINT_NAME 열을 기준으로 조인

y_emp 테이블에 제약 조건을 추가

ALTER TABLE y_emp ADD PRIMARY KEY(emp_id);
ALTER TABLE y_emp ADD FOREIGN KEY(mgr_id) REFERENCES y_emp(emp_id);

정의된 제약 조건에 대하여USER_CONSTRAINTS를 조회

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'Y_EMP';

USER_CONS_COLUMNS를 조회하여 제약 조건이 정의된 열 이름을 조회

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'Y_EMP';

USER_CONSTRAINTS와 USER_CONS_COLUMNS를 조인하여 제약조건 관련 정보를 한 꺼번에 조회

SELECT c.constraint_name, cc.column_name, c.constraint_type
FROM user_constraints c JOIN user_cons_columns cc
ON (c.constraint_name = cc.constraint_name)
WHERE c.table_name = 'Y_EMP';

저작자표시

'Oracle SQL' 카테고리의 다른 글

[SQL] 뷰 (0)	2023.06.05
[SQL] 인덱스 (0)	2023.06.02
[SQL] 테이블 삭제 (0)	2023.06.02
[SQL] 테이블 수정 (0)	2023.06.02
[SQL] 테이블의 생성과 관리 (0)	2023.05.31

'Oracle SQL' Related Articles

Comments

나의 개발일지

[SQL] 제약 조건 본문

[SQL] 제약 조건

제약 조건의 개념

NOT NULL 제약 조건

UNIQUE 제약 조건 (고유 키)

PRIMARY KEY 제약 조건 (기본 키)

FOREIGN KEY 제약 조건 (외래 키)

CHECK 제약 조건

제약 조건과 인덱스

제약 조건의 생성 및 관리

테이블 생성 시 제약 조건 정의

제약 조건의 이름

기존 테이블에 제약 조건 추가

FOREIGN KEY의 추가 옵션

SELF REFERENCE FOREIGN KEY

제약 조건의 사용

제약 조건의 관리 업무

제약 조건의 활성화와 비활성화

제약 조건의 삭제

제약 조건이 정의된 열의 삭제

제약 조건의 이름 변경

제약 조건 관련 정보 보기

'Oracle SQL' 카테고리의 다른 글

티스토리툴바