Oracle SQL 강좌
테이블의 제약조건 46 32 99,999+

by 김정식 제약조건 CHECK UNIQUE FOREIGN KEY NOT NULL DEFAULT USER_CONS_COLUMNS USER_CONSTRAINTS [2002.01.20]


제약조건 (Constraint) 이란?

제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는거라 생각하면 된다. 간단하게 테이블안에서 테이터의 성격을 정의하는 것이 바로 제약조건 이다.

  • - 제약조건은 데이터의 무결성 유지를 위하여 사용자가 지정할 수 있는 성질 이다.
  • - 모든 제약조건은 데이터 사전(DICTIONARY)에 저장 된다.
  • - 의미있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있다.
  • - 표준 객체 명명법을 따르는 것이 좋다.
  • - 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER)명령어를 통해서도 추가가 가능하다.
  • - NOT NULL제약조건은 반드시 컬럼 레벨에서만 정의가 가능하다.

NOT NULL 조건

컬럼을 필수 필드화 시킬 때 사용 한다.

 
-- NOT NULL제약조건을 설정하면 ename 컬럼에는 꼭 데이터를 입력해야 한다.
-- emp_nn_ename을 제약조건 명으로 설정 하였다.
SQL> CREATE TABLE emp3(
     ename VARCHAR2(20)  CONSTRAINT emp_nn_ename NOT NULL );
        
-- 제약조건은 USER_CONSTRAINTS 뷰를 통해서 확인 할 수 있다.
SQL> SELECT CONSTRAINT_NAME
     FROM    USER_CONSTRAINTS
     WHERE  TABLE_NAME ='EMP3' ;

CONSTRAINT_NAME
-----------------------
emp_nn_ename 
    

UNIQUE 조건

데이터의 유일성을 보장(중복되는 데이터가 존재할수 없다) 되고, 자동으로 인덱스가 생성 된다.

  	
--  deptno 컬럼에 UNIQUE 제약조건 생성
SQL> ALTER TABLE emp2
     ADD CONSTRAINT emp2_uk_deptno UNIQUE (deptno);

-- 제약 조건의 삭제
SQL> ALTER TABLE emp2
     DROP CONSTRAINT emp2_uk_deptno;
	

CHECK 조건

컬럼의 값을 어떤 특정 범위로 제한할 수 있다.

 	
-- comm 컬럼에 1에서 100까지의 값만을 가질수 있는 체크조건 생성	
SQL> ALTER TABLE emp2
     ADD CONSTRAINT emp2_ck_comm
     CHECK (comm >= 1 AND comm <= 100);

-- 제약 조건의 삭제
SQL> ALTER TABLE emp2
     DROP CONSTRAINT emp2_ck_comm;

-- 10000,20000,30000,40000,50000의 값만을 가질수 있는 체크조건 생성
SQL> ALTER TABLE emp2
     ADD CONSTRAINT emp2_ck_comm
     CHECK comm IN (10000,20000,30000,40000,50000);    	
    

DEFAULT(컬럼 기본값) 지정

데이터를 입력 하지 않아도 지정된 값이 기본으로 입력 된다.

 
-- hiredate 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다.
SQL> CREATE TABLE emp4(
     ... (컬럼생략) ...,
     hiredate DATE DEFAULT SYSDATE );     
    

PRIMARY KEY 지정

  • - 기본키는 UNIQUE 와 NOT NULL의 결합과 같다.
  • - 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. 이를 참조 무결성이라 한다.
  • - UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 이름은 기본 키 제약 조건의 이름과 같다.
  • - INDEX: 검색 키로서 검색 속도를 향상 시킨다.(UNIQUE,PRIMARY KEY 생성시 자동적으로 생긴다.)
 
-- PRIMARY KEY 생성 예제
SQL> CREATE TABLE emp5(
     empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );     
    
 
-- ALTER TABLE 명령어로 PRIMARY KEY 생성 예제
ALTER TABLE emp2
ADD CONSTRAINT emp2_pk_empno  PRIMARY KEY (empno) ;
    

FOREIGN KEY(외래 키)지정

  • - 기본키를 참조하는 컬럼 또는 컬럼들의 집합이다.
  • - 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형과 일치해야 한다. 이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없다.
  • - 외래키에 의해 참조되고 있는 기본 키는 삭제 할 수 없다.
  • - ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제 된다.
 
-- emp 테이블의 deptno 컬럼이 dept 테이블에 deptno 컬럼을 참조하는 외래키 생성
SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_fk_deptno
     FOREIGN  KEY (deptno) REFERENCES dept(deptno);      
    

제약 조건의 확인

  • - USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건 조회.
  • - USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건 조회.
  
SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,  
            DECODE(B.CONSTRAINT_TYPE,
                'P','PRIMARY KEY',
                'U','UNIQUE KEY',
                'C','CHECK OR NOT NULL',
                'R','FOREIGN KEY') CONSTRAINT_TYPE,  
            A.CONSTRAINT_NAME  
     FROM  USER_CONS_COLUMNS A, USER_CONSTRAINTS B  
     WHERE A.TABLE_NAME = UPPER('&table_name') 
       AND A.TABLE_NAME = B.TABLE_NAME  
       AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME  
     ORDER BY 1;  

-- 테이블 명을 입력 하면 됩니다.
table_name의 값을 입력하십시오: emp2
 
COLUMN_NAME         CONSTRAINT_TYPE    CONSTRAINT_NAME
------------------- -----------------  --------------
DEPTNO              CHECK OR NOT NULL  SYS_C001362   
                    FOREIGN KEY        EMP2_FK_DEPTNO
EMPNO               PRIMARY KEY        EMP2_PK_EMPNO
ENAME               CHECK OR NOT NULL  EMP2_NN_ENAME
MGR                 UNIQUE KEY         EMP2_UP_MGR    
    

- 강좌 URL : http://www.gurubee.net/lecture/1013

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김정식 [2004.05.28 19:13:37]
Primary Key 생성시 인덱스 테이블스페이스를 따로 지정하고 싶으면..
USING INDEX TABLESPACE tablespace_name 을 추가한다.
ALTER TABLE emp
ADD CONSTRAINT emp_PK PRIMARY KEY (empno)
USING INDEX TABLESPACE tablespace_name

by 버들 [2004.10.18 11:15:09]
저기..앞에 주신 제약 조건들은 모두
emp 테이블이 아니라 emp2 테이블에 주신건가요?
emp 테이블에 제약 조건을 주려니 에러가 나고...
강의 마지막 부분을 보니 emp2 테이블의 제약조건을 조회하고 계셔서..

by ssicci [2004.11.22 07:03:57]
버들님 emp2가 맞습니다. 해당테이블에 제약조건을 걸어 줘야 하는데 여기선 emp는 없고 emp2 테이블 가지고 설명하고 있으니 emp2로 하셔야 적용이 될 것입니다.

by 아웅 [2005.02.10 13:38:45]
움~ 꾀어렵군 ㅎ

by 히보기 [2005.04.06 13:02:28]
내용이 좋아서 퍼갑니다. 감사합니다.

by BDER [2005.04.15 13:22:31]
CHECK (comm >= 10 AND comm <= 100000) ;
-> 1에서 100까지의 값만을 가질수 있습니다.
맞는건가요?

by 나석진 [2005.09.30 09:49:51]
COMM값은 10 에서 100000 까지 값만 들어가게됩니다

by smile [2005.11.09 20:41:54]
퍼갑니다. ^^

by 김동기 [2006.05.10 11:01:31]
퍼갈께요..^^

by MiNi [2006.05.17 10:12:00]
위에 제약조건 확인이라는 소스에서..
SQL*PLUS에서는 실행이 잘 되는데...
TOAD라는 프로그램에서는 실행이 안되더라구요..
제가 실행방법을 몰라서 못하는건가요??
도와주세요~

by 기쁜우리 [2006.05.24 11:47:58]
퍼갑니다.

by goodthink [2007.01.05 12:15:24]
그건 토드설정시에 주는 환경을 잘기억하였다가 토드초기실행시에 오라클 10g인경우에 xe선택후 system , dba이런식으로 선택하여사용하면 될듯..

by 왕초짜 [2008.11.17 14:21:55]
TOAD에서는 쿼리문을 안쓰고 Schema Browser에서 테이블 선택하고 Scripts에서 바로 확인가능합니다 ^^

by 고승진 [2008.11.25 16:36:31]
정말 감사합니다^^ 진짜 많이 도움 되었네요,
막막하게 데이터베이스 어떻게 공부할까,
참조무결성이란 무엇일까.
외래키나 기본키도 개념은 알아도 어떻게 SQLPLUS에서 사용할까
정말 고민많았는데 좋은 정보 잘 가지고 갑니다.

by 정동출 [2010.07.12 16:02:26]
감사합니다...^^ 즐거운 하루 되세요...^^

by SadSave [2010.12.22 17:23:15]
토드에서 제약조건의 확인 테스트 퀴리가 실행안되시는분들은
UPPER('&table_name') 이 문장을 UPPER(&table_name) 으로 바꿔주시고
창 하나 뜨시면은 앞뒤로 '를 붙여주시면됩니다.

by 김정식 [2011.01.17 15:19:22]
제약 조건 이름 변경
ALTER TABLE table_name RENAME CONSTRAINTS
old_constraint_name TO new_constraint_name;

by 후루루룹짭짭 [2011.01.27 09:51:15]
check constraint 에서 위 처럼 실행시키면 왼쪽 괄호가 누락되었다 나오네요
아래가 맞는것 같습니다.

alter table emp2
add constraint emp2_ck_comm
check (comm in (100,200,300));

by 할수있어 [2011.04.06 15:00:00]
헐 이 문서 오타 많네요 -_-;;; emp2 테이블 제약 조건인데 한 3시간 동안 emp 테이블로 계속 제약조건 UNIQUE 하려고 하니 에러가 나는수 밖에 ㅜ_ㅜ;

수정 좀 해주세요. 빠른 시간내에 .. .ㅜ_ㅜ;

by 김정식 [2011.04.06 18:21:02]
테이블명은 필요에 맞게 해당 테이블로 수정해서 테스트 하면 된다고 생각을 했는데요..
초보자분들에게는 혼돈이 올 수 있었네요..
수정하였습니다. 즐거운 하루 보내세요..^^

by 박준희 [2011.05.09 17:22:24]
오류인것 같은데요..
check 조건에서
SQL> ALTER TABLE emp2
ADD CONSTRAINT emp2_ck_comm
CHECK comm IN (10000,20000,30000,40000,50000); << 마지막부분에서
에러가 나서 봤는데
CHECK (comm IN (10000,20000,30000,40000,50000));
이렇게 comm IN 을 묶어줘야 되지 않나요?? 전 글케 해야 되던데..

by 바다™ [2011.06.30 14:40:42]
좋은 내용의 글이라 퍼갑니다.
http://blog.paran.com/devbada 입니다.

감사합니다!

by REX [2011.11.01 13:38:19]
알찬 내용 감사합니다. 퍼갑니다.

by 돌이맹이 [2012.02.23 16:57:33]
쉽게 설명 잘 되어 있네요^^

by Personal [2012.09.07 21:50:38]
마지막 제약조건의 확인 부분이 잘 이해가 안되서 그러는데 저 부분은 다른강의에 또 있나요?

by 남택봉 [2012.11.25 10:49:17]

잘 참고하고 갑니다. 수고하세요

by 까망소 [2012.12.14 10:53:39]

CHECK조건에서 값의 범위를 지정할 수 있다는 걸 알게되어 무척이나 행복합니다.
감사합니다 *^ㅡ^)/


by 손님 [2013.03.02 00:18:01]

데이터의 유일성을 보장(중복되는 데이터가 존재할수 없다) 되고, 자동으로 인덱스가 생성 된다.


제가 초짜라 인덱스가 무엇인지 잘이해가 안가서 그러는데 설명좀 부탁드려요 누구든지 ㅠㅠ


by Vampirejk [2013.05.20 09:26:33]
잘 보고갑니다 ^^~
select * from vampirejk 

by 귀여니^^ [2014.12.13 00:33:20]
SELECT CONSTRAINT_NAME
FROM    USER_CONSTRAINTS
WHERE  TABLE_NAME ='EMP3' ;

 

emp3 테이블을 생성한 후 commit; 하고..

위의 SQL 문을 실행했는데 "선택된 레코드가 없습니다." 라는 문구만 계속 나옵니다.

이유가 뭘까요...ㅜ.ㅠ

 


by 이은경 [2015.12.18 17:52:52]
- PRIMARY KEY 생성 예제
SQL> CREATE TABLE emp5(
     empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );     
    
 
-- ALTER TABLE 명령어로 PRIMARY KEY 생성 예제
ALTER TABLE emp2
ADD CONSTRAINT emp2_pk_empno  PRIMARY KEY (empno) ;
    

 

이것부터 막힙니다.

기본키가 2번은 지정되지 않는다는 오류가 떠서 

emp2 테이블을 보려고 하니 (select * from emp2)

지정된 레코드가 없다고 뜹니다. 

이유를 설명해주세요 ㅠㅠ 


by 준킴 [2015.12.25 20:02:08]

이미 테이블 크리에이트 때 키 로 설정해 주시고 그 후에 또 키로 설정하려고 해서 그런거같네요

아래쪽의 alter table 은 create때 아무 조건도 설정하지 않고 컬럼만 선언했거나

이미 선언한 조건을 수정할때 사용합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입