SQL 제약조건

제약조건 ( Constraint)

: 테이블에 저장할 데이터를 제한하는 규칙
: 조건에 맞지 않는 데이터는 저장하지 않음. ==> DB의 정확성 유지 ( 무결성 = 신뢰성)

==> 대상
: 도메인 ( 컬럼 값들의 유효한 범위 ) , 엔티티(개체), 릴레이션(테이블)

종류

• NOT NULL : 지정한 열에 NULL을 허용하지 않음. (NULL 을 제외한 데이터의 중복은 허용.)
• UNIQUE : 지정한 열이 유일한 값을 가져야 함 . (중복 허용 X)
• PRIMARY KEY : NOT NULL + UNIQUE (테이블 컬럼중 하나만 지정 할수 있음.)
• FOREIGN KEY : 다른 테이블의 열을참조하여 존재하는 값만 입력
• CHECK : 설정한 조건식을 만족한데이터만 출력.

종류별 사용 방법

1. NOT NULL

ex)

• 테이블생성 하면서 NOT NULL지정해주기

create table table_notnull(
login_id varchar(20)not null,
login_pw varchar(20)not null,
tel varchar(20));

• 자료형에 맞게 데이터 삽입

insert into table_notnull values ( 'TEST_ID_01',NULL,'010-1234-5678');     
 // login_pw 는 not null이어서 null 이 삽입안됨.
 
insert into table_notnull values ('TEST_ID_01',1234,NULL);                    

• 제약조건 타입 확인 및 조회하기 (데이터 사전 이용)

SELECT OWNER, constraint_name, constraint_type, table_name from USER_CONSTRAINTS;
- P = primary key
- R = reference 외래키
- C = check or not null
- U = unique

• 테이블 생성시 제약조건의 이름까지 지정해주기

create table table_notnull2(
login_id varchar(20) CONSTRAINT tblnn2_lgid_nn not null ,
login_pw varchar(20) CONSTRAINT tblnn2_lgpw_nn not null,
tel varchar(20));

• NOT NULL이 아닌 컬럼을 NOT NULL로 바꿔주기

ALTER table table_notnull
MODIFY (tel not null);     // 이미 NULL데이터 값이 있다면 modify로 수정 불가. 
                             ==> NULL값을 채워주거나 열 삭제후 수정해야함.
                                                                     

• 조건 수정하면서 제약조건에 이름 지정해주기.

ALTER table table_notnull2
MODIFY(tel CONSTRAINT tbnn2_nn not null);   //tel을 not null로 바꾸면서 tbnne_nn이라는 이름을붙여줌.

• 이미 생성된 제약조건의 이름 변경

ALTER table table_notnull2
RENAME  CONSTRAINT tbnn2_nn to tblnn2_tel;

• 제약조건 삭제

ALTER table talbe_notnull2
DROP CONSTRAINT tblnn2_tel_nn;

2. UNIQUE

: 열에 저장할 데이터의 중복을 허용하고 싶지 않을 때 사용.
: 여러개의 NULL이 존재할 수 있음.

ex)

• unique 조건의 컬럼을 포함한 테이블 생성

create table table_unique(
login_id varchar(20) unique,                                       
login_pw varchar(20)constraint tbu_logpw_nn not null,
tel varchar(20));

• 데이터 삽입

insert into table_unique values ('TEST_ID_01','PW01','010-1234-5678');
select* from table_unique;

insert into table_unique values ('TEST_ID_01','PW01','010-1234-5678');  //오류 : 'TEST_ID_01'은 중복 허용이 안됨.
insert into table_unique values ('TEST_ID_02','PW01','010-1234-5678');  // 다른 값들은UNIQUE 가 아니기 때문에 중복값 허용.
insert into table_unique values (NULL,'PW01','010-1234-5678');         //NOT NULL은 아니므로 NULL 가능.

3. PRIMARY KEY

: 값이 유일하게 하나만 있는 컬럼(UNIQUE + NOT NULL)
: 주민 번호,사번, 과목번호등의 컬럼을 식별할때 사용.

ex)

• PRIMARY KEY 컬럼이 있는 테이블 생성

create table table_pk(
login_id varchar(20) PRIMARY KEY,
login_pw varchar(20)not null,
tel varchar(20));

• 제약조건 조회

select owner, constraint_name, constraint_type, 
table_name from user_constraints
where table_name like 'TABLE_PK%';             // TABLE_PK 의 제약 조건만 조회 
                                              ==> 자동으로 인덱스 생성.( PRIMARY KEY만 )

• 자동으로 생성된 인덱스 조회

select index_name , table_owner, table_name from user_indexes
where table_name like 'TABLE_PK%';

• 조건이름 지어서 TABLE_PK2 생성
  ==> (인라인방식) : 컬럼명 적을때 제약조건의 이름과 종류도 함께 정해줌.

create table table_pk2(
login_id varchar(20) constraint tblpk2_lgnid_pr primary key,
login_pw varchar(20)constraint tblpk2_lgipw_nn not null,
tel varchar(20));

==> (아웃오브라인방식) : 컬럼명을 정의해준 후 뒤에 옵션을 적어줌. NOT NULL은 지정할 수 없음.

create table table_pk2(
login_id varchar(20),
login_pw varchar(20),
tel varchar(20),
primary key (login_id), constraint tblpk2_uk unique(tel));

• 데이터 삽입

insert into table_pk2 values ( null, '1234','010-1234-5678');                -- PRIMARY KEY는 NULL값 입력 불가.
insert into table_pk2 values ( 'TEST_ID_01', '1234','010-1234-5678');
insert into table_pk2 values ( 'TEST_ID_01', '1234','010-1234-5678');     -- 무결성 위배 : ID 값 중복

4. FOREIGN KEY

: 다른 테이블과 관계를 지을때 사용.
: 즉정 테이블에서 PRIMARY KEY 조건을 지정한 열을 다른 테이블의 열에서 참조한다는 의미.(reference)

ex)

select owner, constraint_name, constraint_type, table_name, 
R_owner, R_constraint_name from user_constraints             //R_○○○ : 외래키 컬럼 조회 
where table_name in('EMP','DEPT'); 

• 참조할 PRIMARY KEY 컬럼이 있는 테이블 생성

create table  dept_pk( 
deptno number(2) constraint deptpk_dno_pk primary key,
dname varchar2(14), loc varchar2(13));

• 참조할 테이블의 PRIMARY KEY값 넣어주기.

insert into dept_pk values (10,'test_dname', 'test_loc');   //PRIMARY KEY는 NOTNULL

• dept_pk의 deptno(primary key)를 참조하는 deptno 컬럼이 포함된 테이블 생성.

create table emp_fk (
empno number(4) constraint empfk_eno_pk primary key ,
ename varchar2(10), deptno number(2) constraint emfk_dno_fk REFERENCE dept_pk(deptno));  //데이터가 없는 테이블에서는 참조해올 수 없음.

• 참조할 부모키의 값이 존재하므로 자식키의 값도 삽입 가능.

insert into emp_fk values (9999,'test_name',10);

6.CHECK

: 입력되는 데이터값이 세부적인 조건을 제한해주는 기능.

ex)

• check 조건이 포함된 컬럼이 있는 테이블 생성

create table table_check(
login_id number(4) constraint emp_login_pk primary key,
login_pw varchar2(20) constraint emp_pw_ck CHECK(length(login_pw)>3 and length(login_pw)<15),
sal number(5) constraint emp_sal_ck CHECK( sal>0 and sal <8000),
gender char(1) constraint emp_en_ck CHECK( gender in('M','F')), 
ename varchar2(10), tel varchar2(20));