DROP CONSTRAINT

Mickeman

Hello

Can somebody maybe explain for me why I receive this message then I try to syncronize 2 tables, update a field from char to varchar?

Thank you

Micke


The following error message was returned from the SQL Server:

[3725] The constraint 'PK_TblBokOrder' is being referenced by table 'TblBORader', foreign key constraint 'FK_TblBORader_TblBokOrder'.
Could not drop constraint. See previous errors.

The following SQL command caused the error:

ALTER TABLE [dbo].[TblBokOrder] DROP CONSTRAINT [PK_TblBokOrder][/b]

Michelle T

I can't tell you exactly what's happening without having a look at your database schema.

It looks like there's some reason that we have to drop the table's primary key to change the column (is the column part of the primary key? is there anything else special about the column?), and the primary key is referred to by another table's foreign key.

Usually at this point we would go to the other table, drop the foreign key, and complete the operation, then re-add the foreign key. If 'Include Dependancies' was disabled, we can't do that. There might also be other cases where we aren't picking up the foreign key relationship correctly, which would be a bug and we'd be interested in knowing about.

Mickeman

The column I want to update "BOStatus " is not a part of the Primay Key.
I don't really understand why I should need to drop constraint in this case.
I did create script of both tables to show you PK and indexes and so on.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TblBORader_TblBokOrder]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TblBORader] DROP CONSTRAINT FK_TblBORader_TblBokOrder
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblBokOrder]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblBokOrder]
GO

CREATE TABLE [dbo].[TblBokOrder] (
[BOId] [bigint] IDENTITY (1, 1) NOT NULL ,
[BOTyp] [char] (10) COLLATE Finnish_Swedish_CI_AS NULL ,
[BOText] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NULL ,
[BOStatus] [char] (10) COLLATE Finnish_Swedish_CI_AS NULL ,
[BODatum] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
[BOChangedDate] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
[BOChangedBy] [bigint] NULL ,
[BOCreatedBy] [bigint] NULL ,
[BOFelText] [varchar] (255) COLLATE Finnish_Swedish_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblBokOrder] ADD
CONSTRAINT [PK_TblBokOrder] PRIMARY KEY NONCLUSTERED
(
[BOId]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IDX1_TblBokOrder] ON [dbo].[TblBokOrder]([BOTyp], [BOStatus], [BODatum], [BOChangedBy], [BOCreatedBy]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_TblBokOrder] ON [dbo].[TblBokOrder]([BOId], [BOTyp]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

and on TblBORader:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TblLoggBO_TblBORader]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TblLoggBO] DROP CONSTRAINT FK_TblLoggBO_TblBORader
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblBORader]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblBORader]
GO

CREATE TABLE [dbo].[TblBORader] (
[BORadId] [bigint] IDENTITY (1, 1) NOT NULL ,
[BOId] [bigint] NULL ,
[BORadnr] [bigint] NULL ,
[VerRadId] [bigint] NULL ,
[UppdragId] [bigint] NULL ,
[Produkt] [int] NULL ,
[Projekt] [int] NULL ,
[Anlaggning] [int] NULL ,
[Konto] [int] NULL ,
[BORadBeloppKredit] [bigint] NULL ,
[BORadBeloppDebit] [bigint] NULL ,
[BORadPeriodBokF] [char] (10) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadDatumBokF] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadTranstext] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadStatus] [varchar] (10) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadChangedDate] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadChangedBy] [bigint] NULL ,
[BORadForAttestDate] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadForAttestBy] [bigint] NULL ,
[BORadAttestDate] [varchar] (20) COLLATE Finnish_Swedish_CI_AS NULL ,
[BORadAttestBy] [bigint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblBORader] ADD
CONSTRAINT [PK_TblBORader] PRIMARY KEY NONCLUSTERED
(
[BORadId]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IDX1_TblBORader] ON [dbo].[TblBORader]([BOId], [VerRadId], [UppdragId], [Konto], [BORadPeriodBokF], [BORadStatus], [BORadChangedBy], [BORadAttestBy], [BORadAttestDate], [BORadForAttestDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_TblBORader] ON [dbo].[TblBORader]([BORadPeriodBokF]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblBORader] ADD
CONSTRAINT [FK_TblBORader_TblBokOrder] FOREIGN KEY
(
[BOId]
) REFERENCES [dbo].[TblBokOrder] (
[BOId]
),
CONSTRAINT [FK_TblBORader_tblUppdrag] FOREIGN KEY
(
[UppdragId]
) REFERENCES [dbo].[tblUppdrag] (
[UppdragId]
)
GO

Michelle T

What size of varchar are you changing the column to? I ran a synchronization of the TblBORader table with the char(10) on BOStatus changed to varchar(10) (with a simple dummy table for tblUppdrag and all the other code as written in your post), which didn't need to rebuild the table and worked.

Is it definitely the column update that's causing the problem? (i.e. are there no other changes in the database which might be causing the table to be rebuilt?) You can find out if any other changes are affecting the table in any way by deselecting it and starting the synchronization wizard - if there are other changes affecting the table, it will be listed in the 'include dependancies' step.

Mickeman

Exactly, char 10 to varchar 10. Now I did the change directly in Enterprise Mangager without any problem and here is the script what SQL Server did:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
CREATE TABLE dbo.Tmp_TblBokOrder
(
BOId bigint NOT NULL IDENTITY (1, 1),
BOTyp char(10) NULL,
BOText varchar(50) NULL,
BOStatus varchar(10) NULL,
BODatum varchar(20) NULL,
BOChangedDate varchar(20) NULL,
BOChangedBy bigint NULL,
BOCreatedBy bigint NULL,
BOFelText varchar(255) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_TblBokOrder ON
GO
IF EXISTS(SELECT * FROM dbo.TblBokOrder)
EXEC('INSERT INTO dbo.Tmp_TblBokOrder (BOId, BOTyp, BOText, BOStatus, BODatum, BOChangedDate, BOChangedBy, BOCreatedBy, BOFelText)
SELECT BOId, BOTyp, BOText, CONVERT(varchar(10), BOStatus), BODatum, BOChangedDate, BOChangedBy, BOCreatedBy, BOFelText FROM dbo.TblBokOrder (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TblBokOrder OFF
GO
ALTER TABLE dbo.TblBORader
DROP CONSTRAINT FK_TblBORader_TblBokOrder
GO
DROP TABLE dbo.TblBokOrder
GO
EXECUTE sp_rename N'dbo.Tmp_TblBokOrder', N'TblBokOrder', 'OBJECT'
GO
ALTER TABLE dbo.TblBokOrder ADD CONSTRAINT
PK_TblBokOrder PRIMARY KEY NONCLUSTERED
(
BOId
) WITH FILLFACTOR = 90 ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IDX1_TblBokOrder ON dbo.TblBokOrder
(
BOTyp,
BOStatus,
BODatum,
BOChangedBy,
BOCreatedBy
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_TblBokOrder ON dbo.TblBokOrder
(
BOId,
BOTyp
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.TblBORader WITH NOCHECK ADD CONSTRAINT
FK_TblBORader_TblBokOrder FOREIGN KEY
(
BOId
) REFERENCES dbo.TblBokOrder
(
BOId
)
GO
COMMIT

Michelle T

Could you give me some more information so that I can investigate this further?

1) If you can send a SQL Compare snapshot of each database involved to michelle.taylor@red-gate.com this should help a lot with finding out what's wrong.

2) Which version of SQL Compare are you using? (The version number in the About box would be helpful.) It's possible this is something we've fixed internally and that's why the version I'm using isn't picking it up.

3) What options are you using in the comparison project?

4) What version(s) of SQL Server are on each side of the comparison?

Mickeman

1) Sorry, I'm not allowed to send away a snapshot of the db.
2) 6.2.0.271
3) Behavior: Nothing checked.
Ignore: Everything checked except: Comments
WITH NOCHECK
Extended proterties
SET QUOTED_IDENTIFIER...
The LOCK properties...
Identity property on columns.
4) SQL server 2000 Standard Edition, sp4
and
SQL server 2000 Enterprise Edition, sp4

Michelle T

I still can't replicate the problem. I suspect it's some other object affecting the table TblBokOrder which is causing the table to be dropped. If you select all objects in the database other than TblBokOrder, and attempt to synchronize, does TblBokOrder come up in the list of objects on the 'include dependancies' tab?

Mickeman

Yes, but only when choose either Views, Stored Procs or Functions.

I also did "EXEC sp_helpconstraint TblBokOrder" against the TblBokOrder in the receiving DB (the one that gonna be updated). There are only a PK, BOid and a FK : FK_TblBORader_TblBokOrderTblBORader

Michelle T

I suspect what is happening is that one of the other changes in the database (not the column change at all) is causing the PK constraint to need to be dropped and recreated. But without the whole database (or at least the objects which cause the table to appear on the 'include dependancies' list) I can't really make any progress on finding out why the PK is being dropped in the first place, let alone why it's failing to drop.