?
Solved

URGENT ...., Cross Reference

Posted on 2000-02-06
7
Medium Priority
?
724 Views
Last Modified: 2009-11-18
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 ?
0
Comment
Question by:thangaraj
7 Comments
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 100 total points
ID: 2496155

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

Author Comment

by:thangaraj
ID: 2496234
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2497350
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2497999
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
 

Author Comment

by:thangaraj
ID: 2498998
Is it possible to make Referencial Integrity during CREATE/ALTER Database ?
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2500995
No, It is not possible.
0
 
LVL 7

Expert Comment

by:XGIS
ID: 25854005
gpbuenrostro:

I am attempting to implement your solution in creating a trigger but am getting an error on your raiseerror line
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question