[Oracle] 제약 조건(Constraint) 추가, 변경, 조회, 삭제

 컬럼마다 정하는 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK의 제약 조건(Constraint)은 테이블을 생성할 때 지정 가능하며 테이블 생성 후에도 추가해서 지정할 수 있다. 제약 조건을 설정하면 데이터의 정확성과 무결성을 높여 신뢰할 수 있는 데이터를 만들기 때문에 적절하게 지정하는 것이 중요하다.

 이러한 제약 조건은 이름을 지정하여 따로 관리할 수 있다. 제약 조건의 이름을 지정하지 않더라도 자동으로 지정되기 때문에 제약 조건 관리가 필요하다면 이름을 지정하는 것이 좋다.

---

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

 테이블을 생성 시 컬럼 레벨, 테이블 레벨에서 각 제약 조건을 지정할 수 있다.

회원 테이블을 생성한다고 가정하고 아이디, 비밀번호, 이름, 전화번호, 이메일, 성별, 그룹 번호, 가입일의 컬럼을 가지는 MEMBER 테이블을 생성해봤다. 이때 그룹 번호는 CLUB 테이블의 CLUB_ID 컬럼을 참조한다고 가정했다.

- 테이블 생성 시 컬럼 레벨에서 제약 조건 지정하기

 하나의 컬럼에 대해 '[컬럼명] [데이터타입] CONSTRAINT [제약 조건 이름] [제약 조건]' 으로 구성된다.

CREATE TABLE member (
	id VARCHAR2(20) 
		CONSTRAINT member_id_pk PRIMARY KEY,
	password VARCHAR2(20) 
		CONSTRAINT member_pass_nn NOT NULL,
	name VARCHAR2(20) 
		CONSTRAINT member_name_nn NOT NULL,
	phone NUMBER(11)
		CONSTRAINT member_phone_nn NOT NULL 
		CONSTRAINT member_phone_uk UNIQUE,
	email VARCHAR2(30) 
		CONSTRAINT member_email_uk UNIQUE,
	gender CHAR(1) 
		CONSTRAINT member_gender_ck CHECK(gender IN ('m', 'f')),
	group_num NUMBER(4)
		CONSTRAINT member_group_fk REFERENCES club(club_id), -- club 테이블에서 club_id 컬럼 참조
	register_date DATE DEFAULT SYSDATE
);

- 테이블 생성 시 테이블 레벨에서 제약 조건 지정하기

 제약 조건 중에 NOT NULL을 제외한 다른 제약 조건은 테이블 레벨에서 지정 가능하다.

CREATE TABLE member (
	id VARCHAR2(20),
	password VARCHAR2(20)
		CONSTRAINT member_pass_nn NOT NULL,
	name VARCHAR2(20)
		CONSTRAINT member_name_nn NOT NULL,
	phone VARCHAR2(11)
		CONSTRAINT member_phone_nn NOT NULL,
	email VARCHAR2(30),
	gender CHAR(1),
	group_num NUMBER(4),
	register_date DATE DEFAULT SYSDATE,
	CONSTRAINT member_id_pk PRIMARY KEY(id),
	CONSTRAINT member_phone_uk UNIQUE(phone),
	CONSTRAINT member_email_uk UNIQUE(email),
	CONSTRAINT member_gender_ck CHECK(gender IN ('m', 'f')),
	CONSTRAINT member_group_fk FOREIGN KEY(group_num) REFERENCES club(club_id) -- club 테이블에서 club_id 컬럼 참조
);

- 테이블 생성 시 제약 조건 이름 없이 제약 조건 지정하기

 제약 조건 없이 테이블을 생성할 때는 CONSTRAINT 없이 아래와 같이 더욱 간단하게 테이블 생성이 가능하다.

/* 컬럼 레벨 */
CREATE TABLE member (
	id VARCHAR2(20) PRIMARY KEY,
	password VARCHAR2(20) NOT NULL,
	name VARCHAR2(20) NOT NULL,
	phone VARCHAR2(11) NOT NULL UNIQUE,
	email VARCHAR2(30) UNIQUE,
	gender CHAR(1) CHECK(gender IN ('m', 'f')),
	group_num NUMBER REFERENCES club(club_id), -- club 테이블에서 club_id 컬럼 참조
	register_date DATE DEFAULT SYSDATE
);

/* 테이블 레벨 */
CREATE TABLE member (
	id VARCHAR2(20),
	password VARCHAR2(20) NOT NULL,
	name VARCHAR2(20) NOT NULL,
	phone VARCHAR2(11) NOT NULL,
	email VARCHAR2(30),
	gender CHAR(1),
	group_num NUMBER,
	register_date DATE DEFAULT SYSDATE,
	PRIMARY KEY(id),
	UNIQUE(phone, email),
	CHECK(gender IN ('m', 'f')),
	FOREIGN KEY(group_num) REFERENCES club(club_id) -- club 테이블에서 club_id 컬럼 참조
);

---

테이블 생성 후 제약 조건 추가 및 삭제

 테이블 생성 후 필요에 의해 제약 조건을 추가해야 할 때는 아래와 같이 지정할 수 있다.

ALTER TABLE [테이블명] ADD CONSTRAINT [제약 조건 이름] [제약조건](컬럼명);

/* id 컬럼에 PRIMARY KEY 지정 */
ALTER TABLE member ADD CONSTRAINT member_id_pk PRIMARY KEY(id);
ALTER TABLE member ADD PRIMARY KEY(id); -- 제약 조건 이름 없이 추가

 그런데 NOT NULL을 지정하는 경우에는 이미 기본값으로 컬럼에 NULL로 지정되어 있기 때문에, 추가(ADD)하는 것이 아닌 NULL을 NOT NULL로 변경(MODIFY) 해야 한다.

ALTER TABLE [테이블명] MODIFY ( [컬럼명] CONSTRAINT [제약 조건 이름] NOT NULL );

/* password 컬럼에 NOT NULL 지정 */
ALTER TABLE member MODIFY (password CONSTRAINT member_pass_nn NOT NULL);
ALTER TABLE member MODIFY (password NOT NULL); -- 제약 조건 이름 없이 변경

 지정한 제약 조건을 삭제하는 것은 아래와 같다.

ALTER TABLE [테이블명] DROP CONSTRAINT [제약 조건 이름];

ALTER TABLE member DROP CONSTRAINT member_pass_nn;

---

제약 조건 조회하기

지정한 제약 조건은 USER_CONSTRAINTS,  USER_CONS_COLUMNS 딕셔너리에서 확인할 수 있다.

(데이터베이스 전체 제약 조건 조회는 DBA_CONSTRAINTS, DBA_CONS_COLUMNS)

SELECT constraint_name, constraint_type, table_name, search_condition 
FROM USER_CONSTRAINTS 
WHERE table_name = 'MEMBER';

위의 SQL문을 실행하면 아래와 같이 MEMBER 테이블을 생성할 때 지정한 제약 조건들을 확인할 수 있다.

MEMBER 테이블의 제약 조건 조회

참고로 CONSTRAINT_TYPE에서 P는 PRIMARY KEY, R은 FOREIGN KEY, U는 UNIQUE, C는 NOT NULL과 CHECK를 가리킨다.