6. 참조키( Foreign Key ) <제약 조건 : 데이터 무결성 中>

http://blog.naver.com/ddowner?Redirect=Log&logNo=140047548652

제약 조건 : 데이터 무결성

1) Null 과 Not NUll 의 개념
2) Primary key ( Not Null 이 포함 )
3) Unique ( Null 이 단 1번 허용 )
4) default ( 기본값 )
5) Check / Rule
6) Foreign Key

제약 조건 : 참조키( Foreign Key )

참조키가 가능하게 하기위한 기본 사항
1) 기본키 테이블은 primary key , unique 제약조건을 가지고 있어야 한다.
2) 참조키(외래키) 테이블은 상관이 없다.
3) 각 테이블의 두 컬럼들은 컬럼명은 동일하지 않아도 되나, 데이타 타입은 일치해야 한다.

가) 기본키 테이블
CREATE TABLE 부서 (
 부서코드 CHAR(2) NOT NULL PRIMARY KEY -- 유일성(pk, unique)
, 부서명 VARCHAR(20) NOT NULL
) -- 부서 테이블 생성

나) 참조키 테이블
CREATE TABLE 사원
(
 사번 int primary key ,

 부서코드 char(2)  FOREIGN KEY  REFERENCES 부서 (부서코드)
  ON UPDATE CASCADE   --  UPDATE에 대해서는 연쇄수정
  ON DELETE NO ACTION  --  DELETE에 대해서는 롤백
)  -- 사원 테이블 생성

-------------------------------------------------------------------------------

가) 기본키 테이블 생성
CREATE TABLE dept (
 dept_code  char (5)   NOT NULL ,

 dept_name  char (30)  NOT NULL ,

 CONSTRAINT PK_dept_dept_code PRIMARY KEY  CLUSTERED
 (
  dept_code
 )
)

나)참조키 테이블 생성
CREATE TABLE employee (
 emp_id  int  IDENTITY (1, 1)  NOT NULL
  CONSTRAINT PK_employee_id PRIMARY KEY (emp_id)
, emp_name  char (10)  NOT NULL
, dept_code  char (5)  NOT NULL     --  foreign key references dept(dept_code)
)
GO

현재 dept 와 employee 테이블은 서로 관계(참조키)가 없는 상태에서 데이타 삽입
INSERT employee VALUES ('지순', '10001')
확인
select * from employee

제약조건 추가하기위한 테이블 수정 작업
ALTER TABLE employee
ADD CONSTRAINT  FK_dept_code FOREIGN KEY (dept_code)
 REFERENCES dept (dept_code)  -- 에러

이유 : employee 테이블에 dept 테이블에 존재하지 않는 데이타가 먼저 삽입 되었기 때문이다.
         제약 추가시 기존데이타 제약 검사가 이루어지므로, 기존데이타 제약개체의 성격에 맞지

         않을경우에는 제약이 추가되지 않는다.

select * from dept
select * from employee

기존 데이타 제약검사 무시하고, 제약 추가하는 명령어  : with nocheck

 

ALTER TABLE employee
WITH NOCHECK  -- 추가된 코드
ADD CONSTRAINT FK_dept_code2 FOREIGN KEY (dept_code)
REFERENCES dept (dept_code)
on update cascade  -- 기본키 테이블이 업데이트 되면, 자동으로 참조키 테이블도 업데이트 된다.

제약조건(참조키)이 추가된 상테에서 데이타 삽입
INSERT dept VALUES ('10001', '영업')

INSERT employee VALUES ('규창', '10002')  -- 제약 조건 검사에 의하여 에러

위의 구문이 제약 조건 에러가 나지 않기 위해서는 기본키 테이블에 데이타가 먼저 존재해야 한다.
INSERT dept VALUES ('10002', '기획')
INSERT employee VALUES ('규창', '10002')

확인
select * from dept
select * from employee

기본키 테이블의 데이타 변경 : on update cascade 기능을 생각하자.

UPDATE dept SET dept_code = '00002' WHERE dept_code = '10002'
확인
select * from dept
select * from employee

테이블 삭제하는 명령어
DROP TABLE dept

 

sp_help : 개체에 대한 정보를 전체적으로 보여주는 시스템 저장 프로시저
sp_helpconstraint : 테이블에 대한 제약조건 정보를 보여주는  시스템 저장 프로시저
sp_depends : 개체를 참조하는(종속되고) 있는 정보를 보여주는 시스템 저장 프로시저

sp_help dept

sp_helpconstraint dept
sp_helpconstraint employee

use pubs
go
sp_depends 'titles'

제약 조건 검사에 대한 제어

가) 테이블 생성

CREATE TABLE DeferTest (
id int CONSTRAINT PK_DeferTest_id PRIMARY KEY,   -- 컬럼 수준의 제약
, city varchar(10) null
)

나) 데이타를 삽입

INSERT DeferTest VALUES (1, '목포')
확인
SELECT * FROM DeferTest

다) 제약 조건 추가가 에러가 난다.

-- 기존데이타와 제약 추가내용이 충돌이 되어서 에러가 남(제약검사)
ALTER TABLE DeferTest
ADD
CONSTRAINT ck_DeferTest CHECK 
     (city in ('서울', '부산', '광주','제주') )

라) with nocheck 옵션을 사용하여, 제약 조건 추가( 기존데이타 무시 )

ALTER TABLE DeferTest
WITH NOCHECK
ADD
CONSTRAINT ck_DeferTest CHECK 
(city in ('서울', '부산', '광주', '제주') )  -- 정상 실행

SELECT * FROM DeferTest -- 목포가 있음에도 불구하고 제약은 만들어졌다.

                                          (with nocheck로 제약조건을 만들었기 때문에)
INSERT DeferTest VALUES (2, '목포')  -- 제약조건 에러

마) 테이블의 제약조건 검사 상태 확인하기
sp_helpconstraint DeferTest  -- 결과 중 status_enabled에 주목하라.

바)테이블에 제약조건을 잠시 중시하기 : nocheck
ALTER TABLE DeferTest
NOCHECK CONSTRAINT ck_DeferTest  -- Check 제약개체를 제약상태를 중지시킨다.

                                          (sp_helpconstraint DeferTest 결과 status_disabled 상태임)

사) 테이블의 제약조건 검사 상태 확인하기
sp_helpconstraint DeferTest -- 결과 중 status_enabled에 주목하라.

아) check 제약과 상관없는 데이타를 삽입 할수 가 있다.(check 제약이 잠시 중지되어 있는 상태에서)  INSERT DeferTest VALUES (3, '목포') -- CHECK 제약을 위배했는데도 입력된다.
확인
select * from DeferTest

자) sp_helpconstraint DeferTest    -- primary key 제약의 상태 enabled status 상태를 확인한다.
INSERT DeferTest VALUES (3, '서울') -- PK를 위배하므로 여전히 입력되지 않는다.

제약조건 명령어 중 제약을 중지가 불가능한 것은 primary key, unique, default 이다.

차) check 제약조건을 가능하게 한다.
ALTER TABLE DeferTest
CHECK CONSTRAINT ck_DeferTest

카)  sp_helpconstraint DeferTest -- check 제약조건 상태 확인 후
INSERT DeferTest VALUES (4, '목포')  -- 제약조건 검사에 의하여 데이타 삽입 에러발생