Why is parent_object_id Actually the Child Object in a Foreign Key Relationship ?

Let’s look at this query, which lists the columns involved in the foreign key relationship of the child table SpecialOfferProduct and the parent table, SpecialOffer (from the AdventureWorks database):

SELECT 
    f.parent_object_id, 
    SCHEMA_NAME(f.schema_id) SchemaName, 
    OBJECT_NAME(f.parent_object_id) TableName, 
    COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName, 
    SCHEMA_NAME(f.schema_id) ReferencedSchemaName, 
    OBJECT_NAME(f.referenced_object_id) ReferencedTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName 
FROM sys.foreign_keys AS f 
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
    INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id 
WHERE 
    OBJECT_NAME (f.parent_object_id) = 'SpecialOfferProduct'

Why are we qualifying the child table name by the parent object?

The reason is in the definition of parent_object_id in sys.objects:

ID of the object to which this object belongs.
0 = Not a child object.

(see TechNet)

meaning that parent_object_id does not reference the parent in the foreign key relationship, it references the owner of the foreign key definition, which is the child table in the relationship.

The moral of the story is, look at the documentation to understand the purpose of the field rather than make assumptions because it uses terminology that just so happens to coincide with a completely different concept.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s