While loading data into a table I am disabling all the integrity constraints and after once I finished loading I am enabling all of them. Now the problem is some data which is not at all valid(Referred data not exists in parent table). How can I perform the integrity check on the loaded data and remove the unwanted same ?
Scenario :
While loading data into a table I am disabling all the integrity constraints and after once I finished loading I am enabling all of them. Now the problem is some data which is not at all valid(Referred data not exists in parent table). How can I perform the integrity check on the loaded data and remove the unwanted same ?
The answer is YES.
In our following scenario, We have two tables named SQL_Set_Category(Parent) and SQL_Set_Statements(Child), the SQL_Set_Category table will have category of SET statements in SQL Server 2005.
The another table SQL_Set_Statements will have SET statements referenced with Category table.
Now we want to DELETE some category info., of SQL_Set_Category table which have been referred by some other table SQL_Set_Statements.
The following Err occurred...
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_SQL_Set_Statements". The conflict occurred in database "SNDMS_TEST_Local", table "dbo.SQL_Set_Statements", column 'CategoryID'.
The statement has been terminated.
How can we overcome with this ?( We have two sollutions )
1. We have to delete the CHILD tables record first and then delete the PARENT table records.
2. We have to DISABLE the constraints and We can delete the PARENT record directly without deleting the CHILD. (Is not Recommended)
Suppose, I will go for Second option. How ?
1. Creating Category Info ( PARENT Table )
CREATE TABLE SQL_Set_Category
(
CategoryID INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY,
CategoryName VARCHAR(MAX)
)
GO
2. Inserting Sample Categories:
INSERT SQL_Set_Category VALUES('Date and time statements')
INSERT SQL_Set_Category VALUES('Miscellaneous statements')
INSERT SQL_Set_Category VALUES('Query Execution Statements')
INSERT SQL_Set_Category VALUES('SQL-92 Settings statements')
INSERT SQL_Set_Category VALUES('Statistics statements')
INSERT SQL_Set_Category VALUES('Transactions statements')
INSERT SQL_Set_Category VALUES('Locking statements')
GO
3. Creating Statements Info ( CHILD Table ):
CREATE TABLE SQL_Set_Statements
(
CategoryID INT CONSTRAINT FK_SQL_Set_Statements FOREIGN KEY(CategoryID) REFERENCES SQL_Set_Category(CategoryID),
Statementame VARCHAR(MAX)
)
GO
4. Inserting Sample Statemts which refers PARENT Table :
4.1 CategoryID 1 will be the 'Date and time statements' on SQL_Set_Category Table
INSERT SQL_Set_Statements VALUES(1,'DATEFIRST')
INSERT SQL_Set_Statements VALUES(1,'DATEFORMAT')
GO
4.2 CategoryID 6 will be the 'Transactions statements' on SQL_Set_Category Table
INSERT SQL_Set_Statements VALUES(6,'IMPLICIT_TRANSACTIONS')
INSERT SQL_Set_Statements VALUES(6,'REMOTE_PROC_TRANSACTIONS')
INSERT SQL_Set_Statements VALUES(6,'TRANSACTION ISOLATION LEVEL')
INSERT SQL_Set_Statements VALUES(6,'XACT_ABORT')
GO
4.3 CategoryID 7 will be the 'Locking statements' on SQL_Set_Category Table
INSERT SQL_Set_Statements VALUES(7,'DEADLOCK_PRIORITY')
INSERT SQL_Set_Statements VALUES(7,'LOCK_TIMEOUT')
GO
5. Relationship between PARENT and CHILD tables :
ParentTable.CategoryID = ChildTable.CategoryID
ie:
SQL_Set_Category.CategoryID = SQL_Set_Statements.CategoryID
6. DELETing 1 record in Category(PARENT) Table : ( CategoryName = 'Date and time statements' )
DELETE SQL_Set_Category WHERE CATEGORYID = 1
It will raise an err
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_SQL_Set_Statements". The conflict occurred in database "SNDMS_TEST_Local", table "dbo.SQL_Set_Statements", column 'CategoryID'.
The statement has been terminated.
7. DISABLE the CHILD Table constraints :
ALTER TABLE SQL_Set_Statements NOCHECK CONSTRAINT FK_SQL_Set_Statements
8. DELETing 1 record in Category(PARENT) Table : ( CategoryName = 'Date and time statements' )
DELETE SQL_Set_Category WHERE CATEGORYID = 1
(1 row(s) affected)
9. ENABLE the CHILD Table constraints :
ALTER TABLE SQL_Set_Statements CHECK CONSTRAINT FK_SQL_Set_Statements
10. To identity the constraint integrity of 'SQL_Set_Statements'
OK. We just want to know what are all the CHILD Data, But not exists the in PARENT Table.
Solution:
DBCC CHECKCONSTRAINTS('SQL_Set_Statements') WITH ALL_CONSTRAINTS
- The option ALL_CONSTRAINTS is used to identity the all constraints ( ENABLED / DISABLED ).
Result:
Cheers