December 24, 2003 at 9:47 am
Can FK constraints go across database (I mean reference object in other database?)
If this is not possible are there any other work arounds? Can I create a view in the local database of the reference table and make my table refer to the view?) Or no FKs against views?
I would hate to use triggers , but is that the only way?
Thanks.
December 26, 2003 at 5:26 am
If this is SQL Server 2000, you can use a UDF in a check constraint.
--Jonathan
--Jonathan
December 29, 2003 at 3:50 pm
Is it something like,
Alter table Test add constraint Test_CK Check
Column1 in dbo.udf_GetParentTableKeys ()
Do you have an example? Does this perform better than a trigger?
Thanks.
December 29, 2003 at 5:05 pm
USE Northwind
go
CREATE FUNCTION dbo.f_CheckPub(@pubid char(4))
RETURNS bit BEGIN
DECLARE @ret bit
IF EXISTS
(SELECT *
FROM pubs.dbo.Publishers
WHERE Pub_Id = @pubid) SET @ret = 1
ELSE SET @ret = 0
RETURN @ret END
CREATE TABLE CustPubs(
CustomerId nchar(5) REFERENCES Customers,
Pub_Id char(4) CHECK (dbo.f_CheckPub(Pub_Id) = 1))
I can't guarantee that it will perform better, partly because it will behave differently from a trigger and more like a foreign key constraint, i.e. it will allow valid rows in a batch update, where a trigger would roll back the entire batch if there is one invalid value. I studiously avoid triggers if possible...
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply