Can I return foreign key columns from table valued functions?

Question

I want to use a table valued function and call it from within multiple stored procedures rather than repeat the same query across all the stored procedures, but for compatibility with a legacy VB6/UltraGrid app I need to preserve the foreign key references from the original tables.

Within the table valued function I can specify a PRIMARY KEY, but is there a way to specify a 'FOREIGN KEY'? I tried to modify the return table like this, but I get a syntax error:

ALTER TABLE @ReturnTable  
WITH CHECK ADD  CONSTRAINT [FK_ReturnTable_OtherTable]
FOREIGN KEY([OtherTableID])
REFERENCES [dbo].[OtherTable] ([OtherTableID])

Answer 1

no you can't. the function returns a "virtual" table that exists only at run time and there is no way to FK to such a imaginary/transient set of rows. Without knowing your schema and exactly what you are trying to accomplish, my best guess it to try to use a trigger to enforce this.

Answer 2

Will your legacy grid see the FK relationships through a view? Or is the query unable to be stored as a view?