7

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'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
davibq
  • 1,089
  • 11
  • 31
  • 9
    `sys.objects` is a view not a table. You can't create FKs to system objects anyway. – Martin Smith May 11 '12 at 22:05
  • ok thanks.. I wasnt sure if I could do it.. – davibq May 11 '12 at 22:08
  • 6
    Why would you want to do this? For one it would prevent drop/re-create since a new object_id is randomly assigned. Maybe you could tell us what problem you're trying to solve instead of telling us that you want to solve it with a foreign key to sys.objects. – Aaron Bertrand May 11 '12 at 23:23
  • If you're going to add this reference, I'd suggest cascading deletes, so that you don't get all hung up when an object gets removed from sys.objects which has a child record in your user table. – David T. Macknet Feb 14 '13 at 00:24

1 Answers1

2

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.

Code Different
  • 90,614
  • 16
  • 144
  • 163