CONSTRAINT 생성연습

create table member
(
userid varchar2(10)
    CONSTRAINT member_id_pk PRIMARY KEY,
username varchar2(20)
    CONSTRAINT member_name_nn NOT NULL,
passwd varchar2(10)
    CONSTRAINT member_pwd_nn NOT NULL,
idnum varchar2(13)
    CONSTRAINT member_idnum_nn NOT NULL
    CONSTRAINT member_idnum_uk UNIQUE,
phone varchar(13)
    CONSTRAINT member_phone_nn NOT NULL,
address varchar(20),
regdate date,
email varchar(15)
    CONSTRAINT member_email_nn NOT NULL
        CONSTRAINT member_email_uk UNIQUE); 

 create table sawon
(sabun number(5)
    CONSTRAINT sawon_number_pk PRIMARY KEY,
name varchar2(20)
    CONSTRAINT sawon_name_nn NOT NULL,
tel number(15),
deptno number(3),
hiredate date);

create table dept_temp
(deptno number(3)
    CONSTRAINT dept_number_pk PRIMARY KEY,
dname varchar2(20)
    CONSTRAINT dept_dname_nn NOT NULL,
upper number(3)); 

1.sawon의 deptno컬럼은 dept 테이블의 deptno의

값을 참조하게 FK로 제약조건을 설정하세요

2.  sawon의 hiredate 는 오늘 날짜가 기본값이 되도록 설정하세요




답 :
1번

 
alter table sawon
add CONSTRAINT sawon_deptno_fk
FOREIGN KEY(deptno)
REFERENCES dept_temp(deptno)

2번

 alter table sawon
modify(hiredate DEFAULT sysdate);

 

--------------- --------------- --------------- --------------- --------------- --------------- --------------- ------
dept_temp에 CHECK constraint 추가하기

alter table dept_temp
add CONSTRAINT dept_upper_check CHECK(upper between 1 and 100)
 

---------------------------------------------------------------------------
생성한 constraint 확인방법

select constraint_name, constraint_type, r_constraint_name from user_constraints
where table_name IN('SAWON','DEPT_TEMP') 

CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
DEPT_DNAME_NN                  C
DEPT_NUMBER_PK                 P
DEPT_UPPER_CHECK               C
SAWON_NAME_NN                  C
SAWON_NUMBER_PK                P
SAWON_DEPTNO_FK                R DEPT_NUMBER_PK

6 rows selected.

딕셔너리 테이블에서 원하는 정보만 빼오면 된다.
C : CHECK, NOT NULL 조건
P : PRIMARY KEY
R : FOREIGN KEY(REFERENCE한 컬럼)