December 16, 2003 at 5:51 am
I have a view that I created from another database on the same server which contains Employee ID's. My problem is I want to create a constraint on one of my tables which ensures that the Employee ID exists in the view. How can I do this. I know you cannot create a Foreign Key to a View and I can't figure out how to code the check constraint.
Thanks
December 16, 2003 at 7:12 am
You can use an INSTEAD OF trigger.
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Co-author of Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)
Articles: http://www.sqlservercentral.com/columnists/chedgate/
December 17, 2003 at 1:48 am
Instead of insert trigger works only when inserting new records. I won't do anything about UPDATE.
Doing constraints with UDF is easy. Just make a scalar UDF with one or more parameters that returns 1 for True and 0 for False. Then create a check constraint like this: dbo.MyCheckUDF(field1, field2, ...) = 1
This way any record not containing data that cause UDF to return 1 will cause a check constraint violation.
December 17, 2003 at 9:27 am
Could also replicate the table that the FK would be referencing?
Unless the replication will not occur fast enough for any usability at all, this would at least allow you to make modifications to the table even when the server ref'd by a UDF was unavailable.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply