SQL정리 10장 제약조건 (Constraint)

제약조건 (Constraint)

- 틀린데이터는 못들어 오게 막고, 맞는 데이터만 들어오게 해주기 위해 사용.

- 데이터의 정확성과 일관성을 보장하기 위해 각 칼럼에 정의하는 규칙.

- 딕셔너리에 저장됨

- 테이블 생성시 무결성 제약조건을 정의하여 프로그래밍 과정을 줄여준다.

- 데이터베이스 서버에 의해 무결성 제약조건이 관리되어 데이터 오류 발생 가능성을 줄여준다.

- 일시적으로 활성화 또는 비활성화 할 수 있다.

종류 (5가지)

무결성 제약조건 (Constraint)	설   명
NOT NULL	Null값 포함할수 없음
유니크	중복되는 값 오면 안됨 ( 중복되는 것 없어야 하므로, 서버프로세스가 기존 데이터를 찾아야 한다.   오래걸림 - 해결 : 인덱스)
primary key	그 사람만이 가지고 있는 고유 값 (NOT NULL + 유니크)
참조	해당 칼럼 값은 참조되는 테이블의 칼럼 값 중 하나와 일치하거나 Null을 가짐 - 자식 테이블 : 다른 테이블의 값을 참조하는 테이블 - 외래키(foreign key): 부모테이블의 값을 참조하는 자식테이블의 칼럼 - 부모 테이블 : 다른 테이블에 의해 참조되는 테이블 - 참조키(reference : 자식 테이블에서 참조하는 부모 테이블의 칼럼
CHECK	해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정

제약조건 사용법 : Table 생성할때나, Table 생성후 추가 할 수 있다.

관리 : 조회 하거나, 안쓰는 제약조건은 지운다.

제야조건 생성

칼럼이름	데이터타입	KEY Type	NN/Unique	FK table	FK column	설명
SUBNO	NUMBER(5)	PK	NN,U			강좌번호
SUBNAME	VARCHCAR2(20)		NN			강좌이름
TERM	VARCHAR2(1)					학과
TYPE	VARCHAR2(1)					필수/선택구분

SQL> CREATE TABLE subject

         (subno      NUMBER(5)

            CONSTRAINT subject_no_pk PRIMARY KEY,

          subname    VARCHAR2(20)

            CONSTRAINT subject_name_nn NOT NULL

            CONSTRAINT subject_name_uk Unique ,

          term           VARCHAR2(1)

            CONSTRAINT subject_term_ck CHECK (term between 1 and 5),

          type           VARCHAR2(1));

제약조건 거는 방법 (정식,약식방법)

정식과 약식은 성능상 차이가 없으나 약식은 관리하기가 불편

정식

(subno NUMBER(5)

CONSTRAINT subject_no_pk PRIMARY KEY,

※ 인라인 표기방식 Emp_id number(6) primary key   ※ 아웃라인 표기방식 Emp-id number(6), Constraint emp_pk primary key(emp_id)

약식

이름을 오라클이 정해주기 때문에 관리하기 불편함

(subno NUMBER(5) PRIMARY KEY,

하나의 컬럼에 여러 개의 constraint가 걸릴수 있다.

P.K는 제외 : 1개의 Table당 1개만 가능

제약조건			unique
			check
	N/N	N/N	N/N
테 이 블	이 름	주 소	TEL

제약조건 추가

예제 : 부서테이블의 deptno에 기본키, dname에 NOT NULL 무결성 제약조건을

 추가하여라.

SQL> ALTER TABLE department

     ADD CONSTRAINT dept_no_pk PRIMARY KEY(deptno);

SQL> ALTER TABLE department

     MODIFY (dname CONSTRAINT dept_dname_nn NOT NULL);

※ NOT NULL 제약조건 추가는 NOT NULL 허용상태를 NOT NULL 불가로 변경하는 것이므로 MODIFY 명령문을 사용해야 한다.

제약조건 삭제

예제 : 강좌 테이블의 subject_term_ck 무결성 제약 조건을 삭제하여라.

SQL> ALTER TABLE subject

     DROP CONSTRAINT subject_term_ck

제약조건 비활성화

수강 테이블의 sugang_pk, sugang_studno_fk 무결성 제약조건을 비활성화 하세요.

SQL> ALTER TABLE sugang

     DISABLE CONSTRAINT sugang_pk

SQL> ALTER TABLE sugang

     DISABLE CONSTRAINT sugang_studno_fk

제약조건 활성화

예제 : 수강 테이블의 sugang_pk, sugang_studno_fk 무결성 제약조건을

       활성화 하세요.

SQL> ALTER TABLE sugang

     ENABLE CONSTRAINT sugang_pk

SQL> ALTER TABLE sugang

     ENABLE CONSTRAINT sugang_studno_fk

PK FK 설정 상태 보기

SQL> SELECT  *

     FROM  ALL_CONSTRAINTS

     WHERE  OWNER      = '계정'

     AND  TABLE_NAME = '테이블명';

14p===========================================

연습문제

회원(member) 테이블 명세서

칼럼이름	데이터 타입	KEY Type	NN/Unique	FK table	FK column	설명
userid	VARCHAR2(10)	PK	NN,U			사용자아이디
username	VARCHAR2(20)		NN			회원이름
passwd	VARCHAR2(10)		NN			비밀번호
idnum	VARCHAR2(13)		NN/U			주민등록번호
phone	VARCHAR2(13)		NN			전화번호
address	VARCHAR2(20)					주소
regdate	date					가입일
Email	VARCHAR2(15)		NN/U			이메일주소

1. 다음 회원 테이블 명세서를 참조해서 제약조건이 포함된 테이블을 생성하세요

SQL> create table member

     (userid varchar2(10) constraint userid_pk Primary key,

     username varchar2(20) constraint username_nn not null,

     passwd varchar2(10) constraint passwd_nn not null,

     idnum varchar(13) constraint idnum_nn not null

                        constraint idnum_uk unique,

     phone varchar2(13) constraint phone_nn not null,

     address varchar2(20),

     regdate date,

     email varchar2(15) constraint email_nn not null

                        constraint email_uk unique)

SQL> desc member

문제 : 아래의 조건으로 테이블을 생성 하시고 제약조건을 설정하세요

Sawon 테이블 Sabun : number(5) : PK Name : varchar2(20) : not null Tel : number (15) Deptno : number (3) Hiredate : date

Deptt 테이블 Deptno : number(3)  : PK Dname : varchar2(20) NN Upper : number(3)

테이블 생성

SQL> create table deptt

        (deptno number(3) constraint deptno_pk primary key,

        dname varchar2(20) constraint dname_nn not null,

        upper number(3));

        

SQL> create table sawon

        (sabun number(5) constraint sabun_pk primary key,

        name varchar2(20) constraint name_nn not null,

        tel number(15),

        deptno number(3),

        hiredate date);

1. Deptno컬럼은 dept 테이블의 deptno의 값을 참조하게 FK로 제약조건을 설정하세요.

SQL> alter table sawon

     add (constraint sawon_fk foreign key(deptno)

     references deptt(deptno));

SQL> create table sawon

     (sabun number(5) PRIMARY KEY,

     name varchar2(20) NOT NULL,

     tel number(15),

     hiredate date,

     deptno number(3),

     CONSTRAINT sawon_deptno_fk FOREIGN KEY(deptno) REFERENCES deptt(deptno));

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

SQL> alter table sawon

     modify (hiredate default sysdate);

1.2. (다른답)생성시 설정

SQL> create table sawon

     (sabun number(5) primary key,

     name varchar2(20) not null,

     tel number(15),

     hiredate date default sysdate,

     deptno number(3) constraint sawon_deptno_fk references deptt(deptno));