Post reply

update tables that have primary key and foreign key

  • July 27, 2007 at 10:25 am

    #372984

    Hi,

    I have created two tables A and B in a database. I have a column say col1 in tableA and tableB. col1 in tableA is a primary key. col1 in tableB is also a foreign key that refers back to col1 in tableA. now i need to update a value in col1 in both tables it is failing saying that "the update statement conflicted with foreign key constraint".

    so how would i update the value? do I have to remove the constraints first?

    please let me know.

    Thanks,

    Sridhar.

  • July 27, 2007 at 11:37 am

    #722989

    I'm not sure why you would want to do this. Designating a value as the primary key is important to maintaining data integrity, and to have a need to Update a primary key may identify a design issue, or an inappropriate use of Primary key.

    You may want to change the primary key to something else including a Composite Primary Key, or creating a surrogate Primary key.

    Otherwise try modifying your table definitions to use Cascading updates.

    If not there, then you could possibly change the "Child" records within a trigger.

    I would not recommend dropping and recreating the Constraints, it will take forever for you transaction to commit, especially with big tables.

     

Viewing 2 posts - 1 through 1 (of 1 total)

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