how to create referential integrity between two tables in diff dat

How can we create referential integrity relationship between  tables in 
different databases.
I have databasea db1 and db2.
In db1->TableA,db2-->TableB
TableA-->EmpID,EmpName
TableB-->EmpID,DeptNo.
How to create integrity relationship between Empid of two tables in diff 
databases.

0
Utf
2/10/2010 6:08:01 AM
sqlserver.server 1327 articles. 0 followers. Follow

5 Replies
993 Views

Similar Articles

[PageSpeed] 24

Hi Ajay
You cannot create DRI between  databases, I mean create PK/FK...
However  , you can simple join the tables between databases  like

SELECT <columns> FROM db1.tb1 T1  JOIN db2.tb1 T2 ON T1.col=T2.col
WHERE....




"Ajay" <Ajay@discussions.microsoft.com> wrote in message 
news:CB995C69-09C8-470C-99F9-06015EDC3C4D@microsoft.com...
> How can we create referential integrity relationship between  tables in
> different databases.
> I have databasea db1 and db2.
> In db1->TableA,db2-->TableB
> TableA-->EmpID,EmpName
> TableB-->EmpID,DeptNo.
> How to create integrity relationship between Empid of two tables in diff
> databases.
> 


0
Uri
2/10/2010 6:38:58 AM
That cannot be done by foreign key relationships because they are not 
allowed between tables in different databases.  You can do this by doing all 
inserts/deletes/updates thru stored procedures that enforce the relationship 
or by using triggers on the tables that enforce the relationship.

Tom

"Ajay" <Ajay@discussions.microsoft.com> wrote in message 
news:CB995C69-09C8-470C-99F9-06015EDC3C4D@microsoft.com...
> How can we create referential integrity relationship between  tables in
> different databases.
> I have databasea db1 and db2.
> In db1->TableA,db2-->TableB
> TableA-->EmpID,EmpName
> TableB-->EmpID,DeptNo.
> How to create integrity relationship between Empid of two tables in diff
> databases.
> 

0
Tom
2/10/2010 7:25:05 AM
> You can do this by doing all inserts/deletes/updates thru stored 
> procedures that enforce the relationship or by using triggers on the 
> tables that enforce the relationship.


....however, one should consider operations as well. What happens for 
instance if one database need to be restored to an earlier point in time?

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Tom Cooper" <tomcooper@comcast.net> wrote in message 
news:e7GBCJiqKHA.3848@TK2MSFTNGP06.phx.gbl...
> That cannot be done by foreign key relationships because they are not 
> allowed between tables in different databases.  You can do this by doing 
> all inserts/deletes/updates thru stored procedures that enforce the 
> relationship or by using triggers on the tables that enforce the 
> relationship.
>
> Tom
>
> "Ajay" <Ajay@discussions.microsoft.com> wrote in message 
> news:CB995C69-09C8-470C-99F9-06015EDC3C4D@microsoft.com...
>> How can we create referential integrity relationship between  tables in
>> different databases.
>> I have databasea db1 and db2.
>> In db1->TableA,db2-->TableB
>> TableA-->EmpID,EmpName
>> TableB-->EmpID,DeptNo.
>> How to create integrity relationship between Empid of two tables in diff
>> databases.
>>
> 
0
Tibor
2/10/2010 7:57:23 AM
Hi,
Can we provide synchronisation between fields in two tables in different 
database

"Tom Cooper" wrote:

> That cannot be done by foreign key relationships because they are not 
> allowed between tables in different databases.  You can do this by doing all 
> inserts/deletes/updates thru stored procedures that enforce the relationship 
> or by using triggers on the tables that enforce the relationship.
> 
> Tom
> 
> "Ajay" <Ajay@discussions.microsoft.com> wrote in message 
> news:CB995C69-09C8-470C-99F9-06015EDC3C4D@microsoft.com...
> > How can we create referential integrity relationship between  tables in
> > different databases.
> > I have databasea db1 and db2.
> > In db1->TableA,db2-->TableB
> > TableA-->EmpID,EmpName
> > TableB-->EmpID,DeptNo.
> > How to create integrity relationship between Empid of two tables in diff
> > databases.
> > 
> 
> .
> 
0
Utf
2/10/2010 9:56:01 AM
I'm not sure what you mean by synchronisation.  If you mean that whenever 
one table changes, the other one changes also, there is no straight-forward 
declarative way to do that unless you use a view.  As I said, you can write 
code to do that in s stored proc or a trigger.  You may be able to use 
replication (but with replication, the two tables will not always be 
identical).  As Tibor pointed out, in either of these choices you have to 
worry about what happens when one or both of the databases are restored from 
a backup.  Another choice may be to make the table in the second database a 
view that references the table in the first database.  Then they will always 
be the same times. However, there are often reasons you can't use a view.

Generally speaking if you need referential integrity between two tables, you 
have three choices.
1) Put them in the same database.  This is by far the best choice if at all 
feasible.
2) Write code and jump thru many hoops to enforce yourself that they are 
always the same.
3) Settle for something like replication where they will mostly be the same, 
but there will be times when the two tables do not contain exactly the same 
ID's.

Tom

"Ajay" <Ajay@discussions.microsoft.com> wrote in message 
news:0C85718D-695E-43AA-8549-63D74B209051@microsoft.com...
> Hi,
> Can we provide synchronisation between fields in two tables in different
> database
>
> "Tom Cooper" wrote:
>
>> That cannot be done by foreign key relationships because they are not
>> allowed between tables in different databases.  You can do this by doing 
>> all
>> inserts/deletes/updates thru stored procedures that enforce the 
>> relationship
>> or by using triggers on the tables that enforce the relationship.
>>
>> Tom
>>
>> "Ajay" <Ajay@discussions.microsoft.com> wrote in message
>> news:CB995C69-09C8-470C-99F9-06015EDC3C4D@microsoft.com...
>> > How can we create referential integrity relationship between  tables in
>> > different databases.
>> > I have databasea db1 and db2.
>> > In db1->TableA,db2-->TableB
>> > TableA-->EmpID,EmpName
>> > TableB-->EmpID,DeptNo.
>> > How to create integrity relationship between Empid of two tables in 
>> > diff
>> > databases.
>> >
>>
>> .
>> 

0
Tom
2/10/2010 4:56:34 PM
Reply:

Similar Artilces:

Redeployment of CRM to create a DEV environment
We currently have a CRM Prod and CRM Dev environment. They were setup by Consultants/Integrators when we rolled out a year ago. Our Dev enviroment's data has stayed pretty static since we rolled out since we weren't really sure about how to do a restore of Prod to it. At this point, we'd really like to get a true up-to-date Dev environment in order to do a full systems test of a product configurator add-on that we purchased. My understanding is that updating a Dev enviroment is done using the Reployment Wizard. I'm reading through the "Redeploying Microsoft 3.0...

Form creates unwanted rows in table
I've created an asset database which keeps track of computers, laptops etc. There is one company which has many departments and every department has many rooms. (surprise, surprise) There are four tables: tables Department and Asset and two "middle" tables DepartmentRoom and AssetRoom. The table structures are as follows: DEPARTMENT id name dep_type dep_num etc... ASSET id name a_type ip_num vendorID serial_num etc... DEPARTMENTROOM id name depID (foreign key to department) ASSETROOM id assetID (foreign key to asset) roomID (foreign key to depa...

FRx Integration with Business Portal for GP 10?
With Business Portal for GP 10, is there some document that discusses what needst o be done to have FRx reports integrate with Business Portal. In Business Portal 2.5 and 3.0 have worked with the service and configurator mentioned in the Admin manual, however, I don't see anything like that for Business Portal for GP10. Any help would be appreciated. Thanks, KJ Hi KJ I had the same question and contact MBS support and they told you could use the BP 3 FRX integration install for BP 4. I haven't tried it yet, but if MS says it will work it must right? -- Fliehigh "KJ"...

"can not create " error message
We have been experiencing this error message on terminal server 2003. This is never being a problem on windows 2000 server but since we have upgrade the system this has started. microsoft has give a solution KB 305982. It works but problem reoccurs ever now and then. Please help to resolve this problem ...

New items with purchase order integration
I'm running a daily integration to bring purchase orders into GP using Integration Manager. The source is a text file. It works very well. The problem I have is with new items. When there is an item in the source file that does not exist in Great Plains, I would like the integration to fail or skip the line item. Currently, it brings the item into the Purchase Order as a non inventory item. The document will show the item number and the cost but no item description. Where can I change the behavior for this? Unfortunately, with the users that put non-inventory items on POs Wh...

How to change existing table record value by VBA and How to add new record in existing table by VBA
Hi all, I got table in my database with name "tbldata" and i have two fields in that table with the name "Ticket_No" and "Amount". In "Ticket_No" filed column i have value "SD001" and in same row of "Amount" column i have figure "50". With VBA how can i lookup for value "SD001" in "Ticket_No" field and change figure "50" to "30" which is appearing in same row of "Amount" column field. My other question is that how can i add new record in same table with VBA. I want...

How to create temp table ?
Can I create temp table by myself for report writer ? Thx ! Sorry, the answer is no. You need Dexterity to create a temporary table and a Dexterity script to read other tables and populate the temp table's contents. You may be able to use VBA with ADO to obtain the data you need for your report. David Musgrave Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@nospam-microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions polic...

Pivot Tables #17
Monthly dump info from Access into Excel and then do a pivot table. I am able to refresh the data in the pivot table from the raw data fine, but I can't group on the Date field. The raw data has each day in the month and I group it into a month. When I try to group the Date field I get the error "Cannot group that selection." Any help would be appreciated. Thanks The dates have to be numeric, that way you will get the option of grouping by month, week etc. If they are dates and look numeric as in 12/15/03 or similar depending on the region, select an empty cell, copy...

how could we create an e-mail account in Microsoft office outlook?
while using office outlook i am unable to send and receive e-mail because of the email account so want to know the procedures to open an e_mail account in MICROSOFT OFFICE OUTLOOK. Thanks You must have the information given to you by your ISP. Since you didn't state your version, you must open your Outlook program, then press the F1 key (it hasn't broken anything yet!) and search on "email accounts." Follow the instructions. "Dron Giri" <Dron Giri@discussions.microsoft.com> wrote in message news:F298D057-740C-4BB3-9C64-62146B90046D@microsoft.com... ...

How do I create a one variable data table? #2
...

Creating Complicated Charting
Ok, I know some one will be able to help me. I need to create a chart that is based off of 26 sheets give or take 3 or 4, this chart needs to be an advance skill level when mine is only intermediate. I need the chart to not only self expand, but I need it to also contain drop down menus, to make selection on what should show on the chart. I need to have at least 3 different drop downs, with up to 7-10 choices in each. I also need help on how to create a table that allows subcatagories in it. Such as a section named Falls needs to have 7 subsections numbered 1.1,1.2,1.3...etc...how do I ...

Manually creating a replica
I have followed the article on TechNet on How to Manually Create a Replica but I'm not sure if I did it wrong or if what I'm experiencing is normal. At one of my sites, I have about 500MB of data that I would like to protect. I have followed the steps to manually create the replica but when I start the consistency check, the consistency check transfered all 500MB of data that I already imported! Did I do something wrong? I thought the purpose of manually importing the data is so that the initial replica doesn't go over the WAN. Thanks, Mike Ok. I figured out wh...

Create folder and rule for many users in batch . . . ?
Hi, is there a way to automatically create a mail folder below the Inbox and create a rule for a large group of users with the least effort? -- Thanks in advance, Chiel Varkevisser The Hague The Netherlands Check out SpamMover app: http://www.ivasot.biz/spammover.shtml -- Regards, Victor Ivanidze, software developer "Chiel Varkevisser" <ChielVarkevisser@discussions.microsoft.com> wrote in message news:989DBA07-F869-4E3A-B715-D23BECBD40AE@microsoft.com... > Hi, is there a way to automatically create a mail folder below the Inbox > and > create a rule for a...

XmlSchema.Read not creating the SOM #2
I'm using the following code to create an XmlSchema from an XSD file The Read() method doesn't throw any errors and after I write the schem to a file, it looks as expected. However, after the Read() method is called, m_Schema still contains n items. What am I doing wrong? My .xsd file validates fine in XMLSpy, but it i quite complex, so maybe .Net can't handle it? // Code follows System.Xml.XmlTextReader m_XMLReader = ne System.Xml.XmlTextReader(m_SchemaLocation); System.Xml.Schema.XmlSchema m_Schema System.Xml.Schema.XmlSchema.Read(m_XMLReader, null); System.Xml.XmlTextWrit...

How do I create a drop box in Excel to show a calendar?
I'm trying to create a drop box within a cell in Excel so that when the user click on the drop box, it shows a calendar and that the calendar will default to today's date. You can try the Microsoft Date and Time Picker control. Add the Control Toolbox menu to your toolbars. Select the last button that looks like a hammer and wrench. Select the Microsoft Date and Time Picker Control. Add that to your sheet. -- HTH... Jim Thomlinson "V. Pham" wrote: > I'm trying to create a drop box within a cell in Excel so that when the user > click on...

Correction
Hello - My computer and my wife's computer are networked. I have WinXP and Excel 2000 9.0.3821 SR-1 on my computer. She has Win98 and Excel 2000 9.0.2720. When I print a spreadsheet created in Win98 with an earlier version of Excel (I'm not sure which version), I get "squiggilly" vertical lines and numbers. When I print the same file on the same printer using my wife's computer it looks fine. What is the problem? Is there a way I can convert the file so it will print correctly? Thanks Drew Have you tried copying the contents to a new workbook, and saving i...

Creating a drop down from a word or phrase
Sorry about the terminology here! I wanted to be able to create some sort of a drop down link from a word or a phrase. Basically, like the menu in the search results here on this page...When I click on one of the subjects it opens the answer posts below. Example, in a document I'm creating I wanted to have bullets from 1. to 10. On each of these bullets I wanted to be able to double click the bullet (1. Notifications) and then have the contents or sub-bullets come into view! Hopefully this makes sense you someone. Appreciate it! I guess it would...Inserting an expand...

Fulfillment Order in Integration Manager
We would like to option to be able to use Integration Manager to bring in Fulfillment Orders. There is not an option today to bring in Fulfillment Orders. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/...

Pivot table formats
I have created a pivot table that has three fields; Date, raingauge and rainfall. I want to export the output of this table into an Access database. However the table produced only lists date as a single entry instead of listing the date for each raingauge. Therefore I have to manually fill in the missing date data to export it to Access. Is there any way of formating the date field so that it is listed all the way down the table thank Alan What you can do is copy the pivot table and paste special > value over itself. Select the entire range with the gaps, press F5 > Special > Bl...

setting up tax tables in RMS
I have a client who has special tax needs. $0-$1600 is taxed at 9.25 $1601-$3200 is taxed at 9.75 $3201- and above is taxed at 7% so for example- for a $3,300.00 item- the tax would be $311.00. How would I set this up in RMS? Help! Thanks Andrea Andrea, Create 3 Sales Taxes. Sales Tax 1 Minimum Taxable Amount = None Maximum Taxable Amount = $1,600.99 Tax Rate = %9.25 Sales Tax 2 Minimum Taxable Amount = $1,601.00 Maximum Taxable Amount = $3,200.99 Tax Rate = %9.75 Sales Tax 3 Minimum Taxable Amount = $3,201.00 Maximum Taxable Amount = None Tax Rate = %7 Assign all of these taxes to one...

Create a marco to assign numbers
I have created a template for a purchase order. I would like to create a marco that will assign the Purchase Order number automatically whenever the used goes to create a new purchase order using the next available number. Does anyone know what steps are needed to create this macro. Anything I have tryed doesnt' work. I can create the button on the toolbar for the macro, but don't know how to create the marco. Thanks try range("a5")=range("a5")+1 -- Don Guillett SalesAid Software donaldb@281.com "Val Leonard" <vleon@telus.net> wrote in m...

Pivot Table to Ignore N/As
Hi, Does anyone know if it is possible to get a pivot table summary to ignore N/As within a table when summing the data? Nelson I don't. Maybe you could clean them up in the original data (if those cells are a formula): =if(iserror(yourformula),0,yourformula) Or even use another column: =if(iserror(b2),0,b2) Then use that other column in the pivottable. Nelson wrote: > > Hi, Does anyone know if it is possible to get a pivot table summary to > ignore N/As within a table when summing the data? > > Nelson -- Dave Peterson Thanks Dave, that's what we figure...

Creating Form, Need 1st 2 lines of field to indent, 3rd to wrap
I'm creating a form for myself to enter into. I have lines that are formatted in what they call "Indented Block" - I have a heading on the left margin, which I don't touch when filling in the form, the field starting at an indent of 3", the 2nd line of the field if it's long enough to wrap to also be at 3" and any lines after that to wrap back to the left margin. Kinda like this: heading: field 2nd line of field 3rd line of field continued BUT, I need to be able to indent the 3rd line also to 3" if it's too short &...

Kits and Kit Components and SQL Tables
In the past few weeks I have been working on pulling data into an Excel Workbook from various GP Dynamics tables. The purpose of this workbook is to compile data to be used in a cash flow analysis. I have posted several questions on here and responders have been very helpful in answering my questions. I have a new one that hopefully someone can answer... I am pulling in sales line item data from tables SOP30300, SOP030200 as well as IV00101 (for item type). I am using IV00101 because some of our sales include items set up with an item type of "service" so the costs that ar...

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...