DROP CONSTRAINT

MickemanMickeman Posts: 6
edited January 17, 2008 10:30AM in SQL Compare Previous Versions
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]

Comments

  • 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.
    Software Developer
    Redgate Software
  • 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
  • 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.
    Software Developer
    Redgate Software
  • 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
  • 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?
    Software Developer
    Redgate Software
  • 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
  • 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?
    Software Developer
    Redgate Software
  • 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
  • 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.
    Software Developer
    Redgate Software
Sign In or Register to comment.