Post reply

defining table relationships

  • February 4, 2004 at 6:15 pm

    #83386

    Good evening!

    I'm not sure where this entry belongs.

    I had a relationship established between two tables: Appointment and ApptProcDetail. I recently noticed the relationship was absent and tried to re-create it. It got the error message:

    Unable to create relationship FK_Appointment_ApptProcDetail

    ODBC error. MS...Alter table statement conflicted with column foreign key constraint 'FK_Appointment_ApptProcDetail. THe conflict occurred in DB 'BDataNew', table ApptProcDetail, column AppointmentID.

    What do I need to do to find the problem?

    Thanks,

    SMK

  • February 5, 2004 at 11:38 pm

    #492999

    This means the data already has quality problem.

    You can still add constraint by

    alter table Appointment WITH NOCHECK add constraint FK_Appointment_ApptProcDetail foreign key (AppointmentID) references ApptProcDetail(AppointmentID)

     

  • February 6, 2004 at 6:45 am

    #493049

    If you're using Enterprise Manager you can do this as well.. there is a check-box "check existing data on creation".  Un-check it and you should be able to create the relationship.

    The other option is to clean up the data first (if it's possible)... run a few queries to see how big a task it might be.

  • February 6, 2004 at 7:08 am

    #493058

    I have checked for orphan records in the child table (ApptProcDetail) and there are none. Also, a month ago the relationship was intact. I just happened to be checking a diagram for something else when I noticed this relationship had been deleted. Not sure what else to do to find the problem.

    Thanks, guys!

    SMK

  • February 6, 2004 at 7:42 am

    #493067

    Have you tried checking to see if the constraint is actually missing?  Just because the relationship isn't showing on the diagram doesn't always mean that the constraint has been removed.  Try entering some bad data into the table and see if it get's taken.. if not then the constraint is still there.  There may just be a problem with the diagram.. I know in SQL 7 I've had this happen.  The other thing you can check is to go to the design view of one of the tables in Enterprise manager, right click on the key or fkey (depending on the table) and click properties.  On the relationships tab check to see if the relationship is showing..

  • February 6, 2004 at 8:16 am

    #493088

    Yes. I checked and the constraint is not there. I get the error everytime I try to create it.

    I am using identity columns as primary keys. Could that be involved?

    SMK

Viewing 6 posts - 1 through 5 (of 5 total)

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