Search code examples
sql-serverforeign-keyssysobjects

Create a foreign key to system tables

I want to create table with to columns:

IdRole
IdProcedure

the idea is that IdProcedure is a FK to sys.objects.

When I create this query:

SELECT * 
FROM sys.objects 
WHERE type='p'

it works fine, but this one:

ALTER TABLE dbo.CORE_ProcedureXRole
ADD CONSTRAINT FK_SysProcedure
FOREIGN KEY (IdProcedure)
REFERENCES sys.objects(object_id)

tells me:

Foreign key 'FK_SysProcedure' references invalid table 'sys.objects'.

Solution

  • sys.objects isn't a table. It's a system view backed by data stored in proprietary SQL Server format. If you want to make sure that the stored name is correct, add a TRIGGER for update and insert to handle the checking.