foreign key

i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B
column name u can pretain as C1, C2

'Normally', related tables live within the same database.

The Foreign Key constraint declaration doesn't go outside the scope of the database, so in this case you can't declare a FK constraint.

AFAIK, the option you have to enforce cross-database FK relationships, is by using triggers.

/Kenneth

|||

You can’t create a constraint across the database. But there is a workaround available to fix your issue. Using Instead of trigger / for after trigger. But I recommend to use the Instead of Trigger rather than after trigger..

Code Snippet

Use DB1

Go

Create table A

(

ID int Primary Key,

Name varchar(100)

)

Go

Code Snippet

Use DB2

Go

Create table BB

(

Id int,

[Desc] varchar(100)

)

Go

CreateTrigger BB_Triger

on BBInstead of Insert

as

Begin

Insert Into BB

Select * from Inserted as ins Where Exists (Select 1 From DB1..A a Where a.id = ins.id)

End

/*

--use any one

Create Trigger BB_Triger

on BBAfter Insert

as

Begin

Delete from BB

Where NOT EXISTS (Select 1 From DB1..A a Where a.id = BB.id)

End

*/

GO

Code Snippet

Insert Into DB1..A values(1,'One')

Insert Into DB2..A values(2,'Two')

Code Snippet

Insert Into DB2..BB values(1,'Valid')

select * from BB

Insert Into DB2..BB values(4,'In Valid')

select * from BB

|||Thanks good idea

For SQL Buffs

Hi,

I have MyTable (ID, BUSINESS_UNIT_ID, CUSTOMER_NUMBER). I need a list
of customer numbers that exist in more than one BUSINESS_UNIT_ID.

Thanks!SELECT CUSTOMER_NUMBER
FROM MyTable
GROUP BY CUSTOMER_NUMBER
HAVING COUNT(DISTINCT BUSINESS_UNIT_ID)>1|||Awesomeness.

Didn't know you could throw that DISTINCT in there like that.|||> I have MyTable (ID, BUSINESS_UNIT_ID, CUSTOMER_NUMBER). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

My guess is that "id" is a totally redundant, non-relational rowing
numbers that newbies who never read anything on RDBMS use. Did you
mean

CREATE TABLE CustomerAssignments
(business_unit INTEGER NOT NULL
REFERENCES OrgChart(business_unit_id)
ON UPDATE CASCADE,
customer_id NTEGER NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE ,
PRIMARY KEY (business_unit_id, customer_id));

Quote:

Originally Posted by

Quote:

Originally Posted by

> I need a list of customer numbers that exist in more than one BUSINESS_UNIT_ID.<<

SELECT customer_id
FROM CustomerAssignments
GROUP BY customer_id
HAVING COUNT(*) 1;

Without the key, you would have to use COUNT(DISTINCT business_unit_id)