SQL Server Performance

Adding a constraint to a table fails.

Discussion in 'Getting Started' started by oliverville, Jun 23, 2007.

  1. oliverville New Member

    Hi All,

    First, apologies if this has been asked in the forums before, but I have been having
    trouble in adding a constraint to a table.

    I had previously dropped all the constraints on a database (SQL 2000) and then copied
    over my data, when I scripted out the constraints to add them back some of them were
    update some were not.

    Below is what I was running in Query Analyzer:

    ALTER TABLE [dbo].[Reason] ADD CONSTRAINT [FK_Reason_ReasonType] FOREIGN KEY
    (
    [ReasonTypeId]
    ) REFERENCES [ReasonType] (
    [ReasonTypeId]
    )
    GO

    Result:

    Server: Msg 547, Level 16, State 1, Line 1
    ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Reason_ReasonType'. The conflict occurred in database 'SecurityMaster3', table 'ReasonType', column 'ReasonTypeId'.

    Not sure if there was corrupted data on the ReasonType table but both databases were I am synchronizing to have the same data.

    I appreciate any help you can give.
  2. alzdba Member

    - are the column names correct ?
    - are the columns of the same datatype ? (and length)
  3. MohammedU New Member

    May be data might be missing in the FK table...

    If you don't care then try to create FK with NOCHECK option..

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. satya Moderator

Share This Page