Post reply

Adding Foreign Key Constraint - Problem, probably syntax

  • September 21, 2006 at 11:48 am

    #113582

    Hi All:

    I must be overlooking something stupid on this one. I'm trying to create a Foreign Key Constraint from the Notes table to the Customer table.

    My Customer table looks like this:

    Customer

    Customer_ID (PK, not null)

    ....

    My Notes table looks like this:

    Notes

    Notes_ID (PK, Identity, not null)

    Customer_ID (no null)

    ...

    This is my statement:

    alter table Notes

    ADD CONSTRAINT FK_Notes FOREIGN KEY (Customer_ID)

    REFERENCES Customer(Customer_ID)

    This is the error message:

    Server: Msg 547, Level 16, State 1, Line 1

    ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Notes'. The conflict occurred in database 'QFII', table 'Customer', column 'Customer_ID'.

    Please help

    Thanks

    William

  • September 21, 2006 at 12:12 pm

    #661832

    Could you check the data types of customerID coulmn in both the tables.

  • September 21, 2006 at 1:17 pm

    #661847

    Both are varchar(15)

    William

  • September 21, 2006 at 2:33 pm

    #661859

    Some Customer_ID in Notesdon't have correspsonding recpord amongst Customer_ID in Customer.

    And they all should. Excluding NULL.

    _____________
    Code for TallyGenerator

  • September 21, 2006 at 2:47 pm

    #661863

    Great suggestion

    This is very possible now that I think of it. I'm converting a database that gets its feeds from another database. In the course of the conversion, I noticed that the master database has fewer records than the receiving database. This could also be the case with some of the rows in the Notes table. There was no foreign key relationships defined on either of these databases.

    Thanks, I'll look into this and give it a try

    William

  • September 26, 2006 at 4:42 am

    #662434

    This should give you your problem entries in Notes:

    SELECT

     n.Customer_ID

    FROM

     Notes n

    LEFT JOIN Customer c

     ON n.Customer_Id = c.Customer_Id

    WHERE

     c.Customer_Id IS NULL

  • September 26, 2006 at 8:49 am

    #662489

    Thanks Grasshopper.

    I'll give that a try too. What you have offered makes complete sense.

    Thanks

    William

  • September 28, 2006 at 10:36 am

    #663077

    Thanks All for your help.

    In Summary

    to locate orphaned records run:

    SELECT

    n.Customer_ID

    FROM

    Notes n

    LEFT JOIN Customer c

    ON n.Customer_Id = c.Customer_Id

    WHERE

    c.Customer_Id IS NULL

    To remove orphaned records run:

    DELETE FROM Notes

    WHERE NOT EXISTS (

    SELECT Customer_ID

    FROM Customer

    WHERE Notes.Customer_ID = Customer.Customer_ID

    )

    One again thanks, you all are wonderful!!

    William

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply