URGENT ...., Cross Reference

I have the First Database like
Database : Inventory
Table    : Customer
Field    : Cust_Code Primary Key

Second Database ...,
Database : Accounts
Table    : Payments
Field    : Payment_No
           Cust_Code Foreign Key
Here I want to make Reference the Field Cust_Code in the Customer table for Inventory Database.

How Could do this Cross Reference ?
thangarajAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amitpagarwalCommented:

Alter table Payments
Add foreign key (cust_code)
references Inventory..Customer
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thangarajAuthor Commented:
I am getting the following Error

Server: Msg 1763, Level 16, State 1, Line 2
Cross-database foreign key references are not supported. Foreign key 'Inventory..Category'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
0
Gustavo Perez BuenrostroCommented:
thangaraj,
SS doesn't support cross-database foreign key references. However, you can implement cross-database referential integrity using a trigger object. To get more detail see "CREATE TABLE (T-SQL)" topic in BOL.

PD: Let me know if you need a sample.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Gustavo Perez BuenrostroCommented:
thangaraj,
Consider next triggers to implement cross-database referential integrity:

-- On Inventory database:

create trigger tgCDRI_Customer
on Customer
for delete
as
begin
  if (select count(1)
        from deleted
        join Accounts..Payments
          on deleted.Cust_code
            =Accounts..Payments.Cust_code)=0 return
    raiserror ("'DELETE' statement conflicted. The conflict occurred Because of cross-database foreign key reference.",16,1)
    rollback transaction
end


-- On Accounts database:

create trigger tgCDRI_Payments
on Payments
for update,insert
as
begin
  declare @rowcount int
  select @rowcount=@@rowcount
  if update(Cust_Code)
    begin
      if (select count(1)
           from inserted
           join Inventory..Customer
             on inserted.Cust_code
               =Inventory..Customer.Cust_code)
         =@rowcount return
      declare @sStatement char(6)
      select @sStatement='INSERT'
      if (select count(1) from deleted)>0 select @sStatement='UPDATE'
      raiserror ("'%s' statement conflicted. The conflict occurred Because of cross-database foreign key reference.",16,1,@sStatement)
      rollback transaction
    end
end

PD: Let me know your opinion.
0
thangarajAuthor Commented:
Is it possible to make Referencial Integrity during CREATE/ALTER Database ?
0
Gustavo Perez BuenrostroCommented:
No, It is not possible.
0
XGISCommented:
gpbuenrostro:

I am attempting to implement your solution in creating a trigger but am getting an error on your raiseerror line
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.