Add Foreign Key to information_schema.tables

Question

I would like to have a table with a column that references another table (not reference a column in another table).

I'm using SQL Server 2008 R2

So i have tblA, tblB and tblC. tblA has a colum "OtherTable" of varchar. I want a Foreign Key Contraint that only allows the values "tblA", "tblB" or "tblC" as values in this column.

I tried it with a normal Foreign Key

ALTER TABLE dbo.tblA
ADD FOREIGN KEY (OtherTable)
REFERENCES information_schema.tables(TABLE_NAME);

And got the following Error:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'FK__tblA__Tabel__27CED166' references invalid table 'information_schema.tables'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

I also tried it with a Check Constraint

ALTER TABLE dbo.tblA
ADD CHECK (OtherTable IN (SELECT TABLE_NAME FROM information_schema.tables));

But this also gives me an Error:

Msg 1046, Level 15, State 1, Line 6

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Is it possible to have this kind of Constraint?

If nothing else works, try a trigger based solution. – jarlh Feb 12 '16 at 10:12 — jarlh, Feb 12 '16 at 10:12

score 0 · Answer 1 · answered Feb 12 '16 at 10:14

0

Information_schema.tables is a view, you can't reference a view in a foreign key.

answered Feb 12 '16 at 10:14

HoneyBadger

14,750
3
34
48

score 0 · Answer 2 · answered Feb 14 '16 at 07:35

Build your own list of allowed values (user-def table) and reference it. Better choice is to store readable value which says "why" you have to look for data in some other table e.g. record type. And check out other questions about polymorphic data structures - seems to me you are building something similar.

And you should beware of FK-referencing system tables. Those tables are not parts of your application.

Add Foreign Key to information_schema.tables

2 Answers2