sql server - Is it equivalent to add a foreign key to an already existent table's already existent field with already existent records than recreation?

I have three tables: A, AB and B.

A.ID and B.ID are int primary keys. AB.AID and AB.BID are int and (AID, BID) is the primary key of AB.

I need to make sure that AB.AID will be a foreign key which will reference A(ID) and AB.BID will be a foreign key which will reference B(ID).

If I, using Microsoft SQL Server Management Studio right-click the table and click Design and after the table appears I right-click and click on Relationships and add the foreign key, will I have the very same result as if I would:

  1. Create a temporary table with the same structure

  2. Migrate all records from AB there

  3. Remove all records from AB

  4. Change the structure of AB to have two foreign keys

  5. Copy the data back

  6. Remove the temporary table

?

1 Answer

  1. Marks- Reply

    2019-11-14

    There is no difference. Note that when attempting to build a relationship between tables, it will not allow the constraint to be created if there is conflicting data like:

    Tbl1 (tbl1_id PK)
    -----
    1
    2
    
    Tbl2 (tbl2_id PK, tbl1_id)
    -----
    1
    2
    3
    

    if you tried to create the foreign key, it would face as 3 does not exist within your primary table.

    So yes, they are equivalent, just note that SQL server has built in safe guards to protect against invalid relational data.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>