A small overview of the constraints in RDBMS systems:
1. Entity Integrity: Makes sure that each row is uniquely identified.
2. Referential Integrity: Preserve the relationship between 2 tables and tying using foreign key constraints.
3. Domain Integrity: Check for default, unique and check constraints.
Sometimes when we are doing bulk ELT (yes..it is not ETL) and not doing proper cleansing while loading the data into staging tables, we would like to load the raw data by disabling the constraints as below:
[cc lang=”sql”]
CREATE TABLE T5 (
id int identity(1, 1) not null,
productQty varchar(10) null
)
— Add a check constraint
ALTER TABLE t5 WITH CHECK ADD constraint UK_t5_product CHECK(productQty > 100)
— Succeeds
INSERT into t5
SELECT 200
— Fails as it violates
INSERT into t5
SELECT 10
— Ignore the constraint by adding NOCHECK
ALTER TABLE t5 NOCHECK constraint UK_t5_product
— Now it succeeds
INSERT into t5
SELECT 10
— now check constraint by adding CHECK again after inserting the violation
ALTER TABLE t5 CHECK constraint UK_t5_product
— Catch the violators using DBCC command
dbcc checkconstraints(t5)
— clean up
DROP TABLE T5
[/cc]
0 Comments.