0

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

?

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175

1 Answers1

2

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.

Kritner
  • 13,557
  • 10
  • 46
  • 72
  • Thanks, I did not have the courage to experiment with this on a foreign database, nor the time to create an own database and fill it with data to experiment with. I agree that inconsistencies are the task of the programmer to deal with, but I wonder whether indexes will be automatically added to increase performance or not as well. I will accept your answer anyway, as it is a straightforward and clear one. – Lajos Arpad Oct 20 '16 at 16:49
  • 1
    See http://stackoverflow.com/a/836176/2312877 , for sql server indexes are not automatically created for foreign keys – Kritner Oct 20 '16 at 16:51
  • Thank you, makes perfect sense. – Lajos Arpad Oct 20 '16 at 16:56