I have created a foreign key and the table properties list the column as an FK. My question is, if I do a SELECT statement on the table, shouldn't it reveal the data from the other table (where the primary key is)? To me, it means the FK is not linked properly, as the table dump should have data in that field (FK column).
That's not how SQL Server works. All the FK does is enforce data integrity. You can check for yourself by trying to insert a row in the child table which has no matching parent (based on the FK).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 7, 2020 at 5:58 pm
An FK is a constraint that enforces the values in the column(s) are present in the primary-key/unique-index of the related table.
May 7, 2020 at 6:44 pm
I see, so it just links the parent and child tables, and ensures the child can't modify that column?
May 7, 2020 at 9:42 pm
I see, so it just links the parent and child tables, and ensures the child can't modify that column?
It ensures that the FK column in the child table matches the PK (or unique) column in the parent table.
Making modifications to data in the child table does not affect anything in the parent table.
But it is possible to modify the FK column in the child table (as long as the new value matches one already present in the parent).
There is one more related concept, and that is 'CASCADING'. Optionally, when defining a constraint, you can allow CASCADING UPDATES and/or CASCADING DELETES. These work from parent to child. If cascading deletes is turned on, for example, and a row is deleted from the parent table, any matching rows in the child table are also deleted. As should be obvious, this should be used with great care.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 8, 2020 at 6:52 pm
Doing the insert statements revealed it was working just fine. Also, seeing it work in real time with the inserts and attempted inserts made it crystal clear. Added in the cascade on the FK also to see that in action -- great stuff. Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply