SQL Server keys and foreign keys

Question

If I have a primary key in table A and in table B of the same database (table B has its own primary key) I create a relationship with the primary key in table A so that a column in table B is the foreign key, does it mean that the primary key data created in the primary key column of table A will also be added to table B by virtue of it being a foreign key column or do I have to code that relationship, and if so how do I go about that?

Answer 1

I'm not entirely sure what you're asking, but if you want to insert one record into table A and a related record into table B, then you have to specify the id from the table A record as the value in the foreign key field in table B.

Here's an example: table author has fields id and name.

INSERT author (id, name) VALUES (5, 'James Joyce')

Now table book has fields id, author_id and title.

INSERT book (id, author_id, title) VALUES(99, 5, 'Ulysses')

If the author's id field is automatically generated, then you would not specify it in the insert statement, and you would retrieve its value using the @@IDENTITY property.

Answer 2

Your Q : does it mean that the primary key data created in the primary key column of table A will also be added to table B by virtue of it being a foreign key column

Nope, foriegn keys will not enter data into other tables. You will need a record in Table A before you insert a record referencing that foriegn key in Table B.

Q # 2 : or do I have to code that relationship, and if so how do I go about that?

insert into tableA, then insert into Table B. A trigger could be put on TableA to insert a record into TableB when data was entered into tableA had you wanted...

Answer 3

In response to your question:

...do I have to code that relationship, and if so how do I go about that?

You will need to define the relationships between the two tables. Example:

ALTER TABLE tableB
ADD CONSTRAINT FK_tableB_TableA FOREIGN KEY (tableAId)
    REFERENCES tableA (id) ;

When you insert a record into tableB you will still need to define tableAId is. SQL Server doesn't magically know what this should be.

So hypothetically if tableA looked like this:

1 | Some text | 1/1/2020
2 | blah blah | 6/1/2021

To insert a record in tableB that referenced record 2 you would need to do this:

INSERT INTO TableB (2,'My important information')

This assumes tableB has the following structure:

TableB
---------
Id --identity column/pk
tableAId --fk
SomeTextColumn