FK Constraints across database

• sxg6023

  SSCrazy

  Points: 2918

  More actions

  December 24, 2003 at 9:47 am

  #76103

  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.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  December 26, 2003 at 5:26 am

  #487803

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

  --Jonathan

  
  
  --Jonathan

• sxg6023

  SSCrazy

  Points: 2918

  More actions

  December 29, 2003 at 3:50 pm

  #488084

  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.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  December 29, 2003 at 5:05 pm

  #488092

  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