Menu
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'.
409
To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Follow that with the name of the referenced table and the name of the referenced column in parentheses.
Select one or more tables or queries and then click Add. After you have finished adding tables and queries to the Relationships document tab, click Close. Drag a field (typically the primary key) from one table to the common field (the foreign key) in the other table.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
If you mean "can foreign key 'refer' to a primary key in the same table?", the answer is a firm yes as some replied.
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.
127
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
