constarint

종 류	의미	형식
not null	널값 허용 안함	CREATE TABLE [schema.]table(column datatype not null[default expr],                                                  column datatype,..........);
unique	중복 허영 안함	CREATE TABLE [schema.]table(column datatype not null[default expr],                                                  column datatype,..........,                                                  constratint column_uk unique(column));
primary key	not null+unique	CREATE TABLE [schema.]table(column datatype not null[default expr],                                                  column datatype,..........,                                                  constratint column_pk primary key(column));
foreign key	지정 테이블 pk 허용	CREATE TABLE [schema.]table(column datatype not null[default expr],                                                  column datatype,..........,                                                  constratint column_fk foreign key(column)                                                  references table2(column) [delete on  cascade|set null] );
check	조건	CREATE TABLE [schema.]table(column datatype not null[default expr],                                                  column datatype,..........,                                                  constratint column_ck check(조건));

 

foreign   - on delete cascade : pk 삭제시 연계된 fk 까지 같이 삭제된다.

- on delete set null : pk 삭제시 연계된 fk는 null 값을 갖는다.

 

 

 ex.

 

 not null, unique, pk

 

SQL SCOTT>ed
Wrote file afiedt.buf

  1  create table xx(id number(4), data varchar2(20) not null, pid number(4) unique,
  2* constraint xx_id_pk primary key(id))
SQL SCOTT>/

Table created.

SQL SCOTT>insert into xx values(1,'aaa',101);

1 row created.

SQL SCOTT>insert into xx values(1,'bbb',102);
insert into xx values(1,'bbb',102)                                ---pk 중복으로 인한 에러
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.XX_ID_PK) violated

SQL SCOTT>insert into xx values(2,'aaa',102);

1 row created.

SQL SCOTT>insert into xx values(3,null,103);             --- 두번째 column은 not null 이라 null값을 허용 안함 
insert into xx values(3,null,103)
                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."XX"."DATA")

SQL SCOTT>insert into xx values(3,'bbb',102);
insert into xx values(3,'bbb',102)                               --- 세번째 컬럼은 unique 구문이라 중복을 허용안한다
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005426) violated

SQL SCOTT>insert into xx values (3,'bbb',103);

1 row created.

SQL SCOTT>select * from xx;

        ID DATA                        PID                                                                                  
---------- -------------------- ----------                                                                                  
         1 aaa                         101                                                                                  
         2 aaa                         102                                                                                  
         3 bbb                         103                                                                                

 

 fk ,ck

 SQL SCOTT>desc dept
 Name                                                                 Null?    Type
 -------------------------------------------------------------------- -------- ----------------------------------------------
 DEPTNO                                                               NOT NULL NUMBER(2)
 DNAME                                                                         VARCHAR2(14)
 LOC                                                                           VARCHAR2(13)

 SQL SCOTT>select * from dept;

    DEPTNO DNAME          LOC                                                                                               
---------- -------------- -------------                                                                                     
        10 ACCOUNTING     NEW YORK                                                                                          
        20 RESEARCH       DALLAS                                                                                            
        30 SALES          CHICAGO                                                                                           
        40 OPERATIONS     BOSTON                                                                                            

 

SQL SCOTT>create table yy(id number(4), sal number(6), deptno number(2),
  2  constraint yy_id_pk primary key(id),
  3  constraint yy_sal_ck check(sal > 2000),
  4  constraint yy_deptno_fk foreign key(deptno) references dept(deptno));

Table created.

 

SQL SCOTT>insert into yy values(1, 3000, 10);

1 row created.

SQL SCOTT>insert into yy values(2, 1500, 30);
insert into yy values(2, 1500, 30)                 ------  check 조건 sal >2000 조건에 맞지 않아 오류 발생
*
ERROR at line 1:                                 
ORA-02290: check constraint (SCOTT.YY_SAL_CK) violated

SQL SCOTT>insert into yy values(2, 3500, 11);
insert into yy values(2, 3500, 11)                 ------ fk 값이 dept 의 pk 값이 다르기 때문에 오류 발생

*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.YY_DEPTNO_FK) violated - parent key not found

SQL SCOTT>insert into yy values (2, 3500, 30);

1 row created.

SQL SCOTT>select * from yy;

        ID        SAL     DEPTNO                                                                                            
---------- ---------- ----------                                                                                             
         1       3000         10                                                                                            
         2       3500         30