반응형

/**********************************************************************************************
-- Title : [2k] 제약 조건 재활성화 및 유효성 검사
-- Reference : dBRang.com
-- Key word : check, nocheck, alter table, constraint, dbcc check constraints
**********************************************************************************************/
use pubs;

-- Disable foreign key constraint
ALTER TABLE Orders
    NOCHECK CONSTRAINT
        FK_Orders_Customers

-- Add a new Order record for a non-existent customer
INSERT INTO Orders (CustomerID) VALUES ('BLAH')

-- Select all orders for the non-existent customer
SELECT * FROM Orders WHERE CustomerID = 'BLAH'

-- Enable foreign key constraint
ALTER TABLE Orders
    CHECK CONSTRAINT
        FK_Orders_Customers

-- Add a new Order record for a non-existent customer
INSERT INTO Orders (CustomerID) VALUES ('BLEH')
 

-- 제약조건 전체 재활성화
alter table titleauthor
 check constraint all;

-- 제약 무결성 검사
dbcc checkconstraints (titleauthor)

--제약 확인
exec sp_constraint0

-- 디폴트 적용
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

-- 디폴트 삭제
ALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULT

반응형

+ Recent posts