컴퓨토피아

1. Primary key

(1) 제약조건 생성

CREATE TABLE sawon
(sano NUMBER(4)          constraint sawon_sano_nn     NOT NULL

,constraint sawon_sano_pk primary key(sano)
);

ALTER TABLE buseo
add constraint buseo_buno_pk primary key(buno);

2. Foreugn key

(1) 제약조건 생성

CREATE TABLE sawon
(,buno NUMBER(2)
,constraint sawon_buno_fk foreign key(buno) references buseo(buno)
 --on delete cascade 1:1에서만 쓴다. <on update cascade는 존재하지 않는다.>
);

ALTER TABLE sawon
add constraint sawon_buno_fk foreign key(buno) references buseo(buno);

(2) 제약조건 삭제

ALTER TABLE [테이블명]
drop constraint [제약조건명];

만약, 다른 테이블을 참조중이라 삭제가 안된다면

SELECT *
FROM user_constraints
WHERE constraint_type='R' AND r_constraint_name='BUSEO_BONO_PK';

우선 검색을 한 후 포린키 제약을 삭제 후 삭제 하면 지워진다.

아니면 casecade를 붙여 주면 자동으로 지워진다.

ALTER TABLE buseo
drop constraint buseo_buno_pk cascade;

3. Unique key

(1) 제약조건 생성

jubun 값이 유니크 속성을 주었지만 NOT NULL 조건을 안주었기 때문에
여러개의 NULL 값이 들어 갈 수 있다.
MS-SQL, MySQL은 2번째부터 NULL 값이 안들어 간다.

CREATE TABLE sawon
(jubun VARCHAR2(13)     constraint sawon_jubun_nn    NOT NULL
,constraint sawon_jubun_uk unique(jubun)   

);

4. Check 제약

(1) 제약조건 생성

CREATE TABLE sawon
(jik VARCHAR2(10)          DEFAULT '사원'
,constraint sawon_jik_ch CHECK(jik IN('사장','부장','과장','대리','사원'))
);

5. Not null 제약

(1) 제약조건 생성

MS-SQL에서는 룰제약 대신 Not null 제약이다.

그래서 일반적으로 테이블을 만들때 Not null 제약을 줄때 제약명을 적어줘야

유지보수하는데 도움이 된다.

CREATE TABLE sawon
(sano NUMBER(4)          constraint sawon_sano_nn     NOT NULL

);

ALTER TABLE sawon
modify constraint saname sawon_saname_nn NOT NULL; --보통은 add지만 NOT NULL 제약은 modify다.

ALTER TABLE sawon
add email VARCHAR(20) DEFAULT ' '
constraint sawon_email_nn NOT NULL;

6. 전체 예문

CREATE TABLE sawon
(sano NUMBER(4)    constraint sawon_sano_nn NOT NULL
,saname VARCHAR2(10) constraint sawon_saname_nn NOT NULL
,jubun VARCHAR2(13) constraint sawon_jubun_nn NOT NULL
,jik VARCHAR2(10) DEFAULT '사원'
,sal NUMBER(4)
,comm NUMBER(4)
,hiredate DATE DEFAULT SYSDATE
,mgr NUMBER(4)
,buno NUMBER(2)
,constraint sawon_sano_pk primary key(sano)
,constraint sawon_jubun_uk unique(jubun)
,constraint sawon_jubun_ch CHECK(TO_NUMBER(jubun)*0=0 AND LENGTH(jubun) =13)
,constraint sawon_jik_ch CHECK(jik IN('사장','부장','과장','대리','사원'))
,constraint sawon_salcomm_ck CHECK ((sal BETWEEN 1000 AND 9999) AND (comm >=0) AND sal > comm)
,constraint sawon_buno_fk foreign key(buno) references buseo(buno)
 --on delete cascade 1:1에서만 쓴다. <on update cascade는 존재하지 않는다.>
,constraint sawon_mgr_fk foreign key(mgr) references sawon(sano)
);

MS-SQL와는 달리 default는 제약조건이 아니고 컬럼이다.

ALTER TABLE sawon
modify jik DEFAULT '사원';

default 값 변경은 위와 같이 한다.

 7. 테이블의 제약조건 확인

SELECT *
FROM user_constraints
WHERE TABLE_name='SAWON';

8. 제약 조건의 삭제

ALTER TABLE [테이블명]
drop constraint [제약조건명]

9. DEFERRED : 제약조건을 바로 적용하지 않고 commit할때 적용한다.

위의 확인결과 중에서 DEFERRABLE 컬럼이 NOT  DEFERRABLE 라면 인서트시 제약 조건을 확인한다.

하지만 부서의 매니져 같이 순서대로 넣지 않으면 제약조건에 위배 될때

매니져 제약조건을 잠시 검사 안하게 하고 전부 삽입 후 체크를 하면 순서 상관 없이 넣을 수 있다.

단, 그때도 위배 되는 것은 rollback시킨다.

그 지정 방법은 아래와 같다.

ALTER TABLE sawon
add constraint sawon_mgr_fk
foreign key(mgr) references SAWON(sano)
deferrable initially DEFERRED;

select A.NAME, A.ID, A.PARENT_OBJ, A.XTYPE, B.NAME, B.ID, B.PARENT_OBJ, B.XTYPE, C.NAME INDEX이
름, D.NAME COLUMN이름
from (SELECT * FROM sysobjects WHERE xtype ='U') A
     LEFT OUTER JOIN (SELECT * FROM SYSOBJECTS WHERE xtype in ('PK','UQ','C','F')) B ON A.ID
=B.PARENT_OBJ
     INNER JOIN SYSINDEXES C ON A.ID = C.ID
     INNER JOIN SYSCOLUMNS D ON A.ID = D.ID
WHERE C.indid = D.colid
ORDER BY A.ID, A.PARENT_OBJ, A.XTYPE