ALTER TABLE CHECK CONSTRAINT not working for Foreign Key
-
I've been trying to set a Foreign Key contraint to CHECK for a table that was previously set to NOCHECK (I'm trying to reload Test from production with dts).
The following worked and allowed the delete needed by dts:
ALTER TABLE at_plPlatted_Lot NOCHECK CONSTRAINT all
When I try to reset the checking with the following, nothing happens:
ALTER TABLE at_plPlatted_Lot CHECK CONSTRAINT all
If I go in through Enterprise Manager to set the checking, it works but the code generated to do it drops and re-adds the foreign key:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.at_plPlatted_Lot
DROP CONSTRAINT Lot_at_plPlatted_Lot_FK1
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.at_plPlatted_Lot ADD CONSTRAINT
Lot_at_plPlatted_Lot_FK1 FOREIGN KEY
(
LotID
 
REFERENCES dbo.Lot(
LotID
 
ON UPDATE CASCADEGO
COMMIT
I would'nt mind using EM but I have many tables to do this to.
Am I missing something here?
Steve
-
ALTER TABLE at_plPlatted_Lot NOCHECK CONSTRAINT all
and
ALTER TABLE at_plPlatted_Lot CHECK CONSTRAINT all
Only deactivate and reactivates the constraints. It doesn't revalidate anything.
Do you have to do this on many tables??
Have you thaught of simply doing a backup production/restore on test (would me sure that both the ddl and the data is correct before trying to apply any changes to production)?
-
I understand that
ALTER TABLE at_plPlatted_Lot CHECK CONSTRAINT all
should be reactivating the contraint. The problem is that it is not reactivating it.
I am reloading test, not production. I do run SQL Compare from Red Gate before moving any data. In fact, this is how I know that the constraint is not being re-activated.
The number of tables that have Foreign Keys is more than 20. Not bad to use EM if this were a one time occurance but would like to run this more frequently. Also wish to use the same technique for reloading another database that exist on the web on a nightly basis.
I forgot to say, I am using SQL Server 2000, SP3a. Was this fixed with SP4 maybe?
Steve
-
Don't know about that fix in SP4.
What error message are you getting when trying to reactivate the constraints?
-
I don't get any error messages! It just says
"The command(s) completed successfully."
but when I compare the structure of production to test, the test shows
ALTER TABLE [dbo].[at_plPlatted_Lot] WITH NOCHECK ADD CONSTRAINT [Lot_at_plPlatted_Lot_FK1] FOREIGN KEY ([LotID]) REFERENCES [dbo].[Lot] ([LotID]) ON UPDATE CASCADE
where the production shows
ALTER TABLE [dbo].[at_plPlatted_Lot] ADD CONSTRAINT [Lot_at_plPlatted_Lot_FK1] FOREIGN KEY ([LotID]) REFERENCES [dbo].[Lot] ([LotID]) ON UPDATE CASCADE
It is frustrating that a documented SQL-Server feature doesn't work as advertised.
(By the way, it works this way on all the foreign keys, not just the CASCADING ones.)
Steve
-
There must be a message somewhere... But I can't think were it could be.
-
I gave up trying to get the CHECK CONSTRAINT to work and decided to drop all Foreign Keys before reloading my test system, run the dts and add back the Foreign Keys when I'm done. To help with that, I wrote two scripts that will generate the proper drops and adds. (I also disable and enable triggers.)
-----------------------------------------------------------------
SET NOCOUNT ON
print 'USE ' + DB_NAME()
print ''
-- Generate Drops for All Foreign Keys in Database
print '-- Drop Foreign Keys'
print ''
select distinct 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
from sysforeignkeys
go
-----------------------------------------------------------------
SET NOCOUNT ON
print 'USE ' + DB_NAME()
print ''
-- Generate Adds for All Foreign Keys in Database
print '-- Add Foreign Keys'
print ''
DECLARE @fkName varchar(800), @tabName varchar(800), @refName varchar(800)
DECLARE @stat int, @fkCol varchar(8000), @refCol varchar(8000)
DECLARE @pline varchar(8000)
DECLARE fkCursor CURSOR FOR
select distinct object_name(constid), object_name(fkeyid),
object_name(rkeyid), status
from sysforeignkeys k
JOIN sysobjects c ON k.constid = c.id
order by object_name(fkeyid)
OPEN fkCursor
FETCH NEXT FROM fkCursor
INTO @fkName, @tabName, @refName, @stat
WHILE @@FETCH_STATUS = 0
BEGIN
select @fkCol = NULL
SELECT @fkCol = ISNULL(@fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys
where object_name(constid) = @fkName
order by keyno
select @refCol = NULL
SELECT @refCol = ISNULL(@refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys
where object_name(constid) = @fkName
order by keyno
select @pline = 'ALTER TABLE [dbo].[' + @tabName +
'] ADD CONSTRAINT [' + @fkName + ']' +
CHAR(13) + CHAR(10) +
' FOREIGN KEY (' + @fkCol + ') REFERENCES [dbo].[' + @refName +
'] (' + @refCol + ')'
if @stat & 4096 = 4096
select @pline = @pline + CHAR(13) + CHAR(10) +
' ON DELETE CASCADE'
if @stat & 8192 = 8192
select @pline = @pline + CHAR(13) + CHAR(10) +
' ON UPDATE CASCADE'
select @pline = @pline + CHAR(13) + CHAR(10) + 'go'
print @pline
FETCH NEXT FROM fkCursor
INTO @fkName, @tabName, @refName, @stat
END
CLOSE fkCursor
DEALLOCATE fkCursor
GO
-----------------------------------------------------------------
I did get some help from other SQLServerCentral material and the Synchronize text generated by SQL Compare.
Thanks.
Steve
-
If you want to actually check the validity of data against the constraints use DBCC CHECKCONSTRAINTS ; This will check the constraints and will report the violations.
Check BOL for usage.
Gabriela
-
DBCC CHECKCONSTRAINTS does sound like it can be useful, especially when you are adding a new contraint to a table, but I would prefer to keep the data clean to begin with by having the contraints enabled.
Thanks anyway.
Steve
-
I am not able to reproduce your problem I think SQL IS reactivating the constraints and maybe the problem is how are you checking that!
Ex:
In QA you
1. run the ALTER TABLE tblname NOCHECK CONSTRAINT ALL
2. refresh Object Browser and script out the table and it shows the table script plus sepaterated alter statements disabling the individual constraints
3. run ALTER TABLE tblname CHECK CONSTRAINT ALL
4.refresh Object Browser and script out the table and it shows the table script without the disabling counterparts
hth
* Noel -
If the constraints were disabled with NOCHECK, you have no clue if the data are still clean. While the constraint were disabled, dirty data could have been inserted in the table. Once you alter the constraints with CHECK option, it is going to validate the data from that point forward, but the dirty data question for the NOCHECK duration will persist.
So you can either drop and recrate with CHECK the constraint, or alter it with CHECK and run DBCC CHECKCONSTRAINTS afterwards. Both will check the existing data against the constraint definiton. The difference is that CREATE ... with CHECK will not create the constraint if offending data are found. The DBCC will just report the problem, but the constraint will exist so will take care of the future data. If something will be updated in the rows with data that violates the constraint the update will fail, as at that moment all the (active) constraints will be checked.
Gabriela
-
noeld,
You are correct that the script generated from Query Analyser drops the WITH NOCHECK syntax. When I run a script generation from Enterprise Manager, it uses WITH NOCHECK on the primary key and does not include it for the foreign key. When I run Red Gates SQL-Compare Utility, it uses WITH NOCHECK on the foreign key and not on the primary key.
Is there a system table or a stored procedure that will give me what is actually going on? I suppose I could use DMO if I wanted to take the time but who knows if that is accurate or not.
Gabriela,
Since I am loading the test system from production which does not ever have the contraints disabled and there is only one other person besides myself that uses the test system, I am satisfied that there will not be any invalid data after the data transfer.
Steve
-
Well Lets step back from the begining:
You can use the ENABLE/ DISABLE for ALL constraints Except PRIMARY KEYS.
To prove my point this is the query you are after:
select TABLE_NAME
,CONSTRAINT_NAME
, OBJECTPROPERTY(OBJECT_ID( CONSTRAINT_NAME), 'CnstIsEnabled') as Enabled
FROM
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
order by TABLE_NAME
and From BOL under ALTER TABLE:
{ CHECK | NOCHECK} CONSTRAINT
Specifies that constraint_name is enabled or disabled. When disabled, future inserts or updates to the column are not validated against the constraint conditions. This option can only be used with FOREIGN KEY and CHECK constraints.
hth
* Noel -
noeld,
I believe you. It just purplexing to get this script generated from Enterprise Manager after executing the statements:
ALTER TABLE plat_name NOCHECK CONSTRAINT ALL
ALTER TABLE plat_name CHECK CONSTRAINT ALL
---------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[plat_name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[plat_name]
GO
CREATE TABLE [dbo].[plat_name] (
[platnum] [int] NOT NULL ,
[name_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[num] [tinyint] NOT NULL ,
[plat_name] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mod_date] [int] NULL ,
[mod_user] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[plat_name] WITH NOCHECK ADD
CONSTRAINT [PK_platname_nametype_num] PRIMARY KEY CLUSTERED
(
[platnum],
[name_type],
[num]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
CREATE INDEX [IX_platname_name] ON [dbo].[plat_name]([plat_name]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[plat_name] ADD
CONSTRAINT [FK_plat_name_plat] FOREIGN KEY
(
[platnum]
 
REFERENCES [dbo].[plat] ([platnum]
 
GO
---------------------------------------------------------------
I did run the following query after NOCHECK and CHECK statements.
select distinct object_name(constid), object_name(fkeyid),
object_name(rkeyid),
OBJECTPROPERTY ( constid , 'CnstIsDisabled' )
from sysforeignkeys k
where object_name(fkeyid) = 'plat_name'
order by object_name(fkeyid)
The 'Contraint Is Disabled' did reflect the correct NOCHECK and CHECK status.
I guess I'll have to talk to Red Gate about the problem of why SQL-Compare generates the statement:
ALTER TABLE [dbo].[plat_name] WITH NOCHECK ADD CONSTRAINT [FK_plat_name_plat] FOREIGN KEY ([platnum]) REFERENCES [dbo].[plat] ([platnum])
GO
-
Please,
And when you're done can you reply back?
Cheers!
* Noel
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply