FK Constraints across database

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

  • If this is SQL Server 2000, you can use a UDF in a check constraint.

    --Jonathan



    --Jonathan

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

  • 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