Menu
Is it possible to use one of the attributes of a composite primary key as a foreign key? Yes, this is quite common and perfectly valid. The best example comes from the classic many-to-many relationship.
When you use the multiple-column constraint format, you can create a composite key. A composite key specifies multiple columns for a primary-key or foreign-key constraint. The next example creates two tables.
A foreign key is a column or a set of columns in one table that references the primary key columns in another table. The primary key is defined as a column (or set of columns) where each value is unique and identifies a single row of the table.
Since each foreign key value must exactly match the corresponding primary key value, the foreign key must contain the same number and data type of columns as the primary key, and these key columns must be in the same order. A foreign key can also have different column names than the primary key.
Since Table2 has a composite primary key (FileID, FileType), then any reference to it must also include both columns.
ALTER TABLE dbo.Table1
ADD CONSTRAINT FK_Table1_Table2
FOREIGN KEY(FileID, FileType) REFERENCES Table2(FileID, FileType)
Unless you have a unique constraint/index on the Table2.FileID field (but if so: why isn't this the PK??), you cannot create a FK relationship to only parts of the PK on the target table - just can't do it.
marc has already given a pretty good answer. If the rows in Table1 only ever relate to one type of File (e.g. FileType 'ABC'), then you can add FileType to Table1 as a computed column:
ALTER TABLE Table1 ADD FileType as 'ABC'
Which you can then use in the Foreign Key.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With