Description:
On sqlserver 2005 database, we have tables with "WITH NOCHECK ADD CONSTRAINT "
on columns.
When forward engineer the Model and get the syntax:
ALTER TABLE Tab1
WITH NOCHECK ADD CONSTRAINT Cons1
CHECK ( [New_Col1] IS NOT NULL )
When the model is compared with SQL Server 2005 database that does not have the column
"New_Col1" the following sql is generated:
ALTER TABLE Tab1
ADD New_Col1 varchar(30) NULL CONSTRAINT Cons1
CHECK ( [New_Col1] IS NOT NULL )
You can notice that "NOCHECK" has not come in the 2nd alter statement when doing CC between the model to
SQL Server DB. Need to know know what option to check to get the NoCheck when comparing the Models.
Solution:
This is because With Check/NoCheck syntax is only applicable when a table is being created or modified as
this is a table level constraint clause. It is not applicable at the column level.
when you add a new column this clause is missing, that is due to this clause of CHECK/NOCHECK is not at a
column level - it is at the table level. Notice the syntax for this clause:
ALTER TABLE Tab1
WITH NOCHECK ADD CONSTRAINT Cons1
CHECK ( [New_Col1] IS NOT NULL )
In this syntax above there is no reference to any specific column as only table name and constraint names
are mentioned besides the constraint value. Therefore, ERwin creates it when a table is being altered or
created, but not when a column is added or being altered.
Comments
0 comments
Please sign in to leave a comment.