Referential Constraint Between Two Tables In Two Databases

Dec 7, 2007

Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.

View 4 Replies


ADVERTISEMENT

Referential Constraint Vs Trigger

Jan 18, 2007

I am using SQL server 2000. I have a parent table 'MimeTypes' with primary key 'mimetype'. This is referenced as foreign key in table 'AssetTypes' as column 'BaseMimeType'.
The referential constraint is simple :
alter table AssetTypes
add constraint FK_ASSETTYPES_REF_BASEMIMETYPE foreign key (BaseMimeType)
references MimeTypes (MimeType) on update no action on delete no action;

I have FOR Update trigger on parent table ('MimeTypes') which is as follows:

CREATE TRIGGER trg_MimeTypes_update ON MimeTypes FOR UPDATE
AS
IF UPDATE(MimeType)
BEGIN
UPDATE AssetTypes
SET BaseMimeType = (select MimeType from inserted)
FROM AssetTypes as A
JOIN deleted as d on A.BaseMimeType = d.MimeType
END;

********
The problem is simple. I want to execute a update query on parent table ('MimeTypes') which modifies 'mimetype' value referenced by child. I dont want to include cascade option in constraint.
When such query is executed error comes as : UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_ASSETTYPES_REF_BASEMIMETYPE'. The conflict occurred in database 'default.db', table 'AssetTypes', column 'BaseMimeType'.
*****
Is there any way to do this?

View 5 Replies View Related

Converting Multi-column Referential Constraint Into A Trigger

Feb 28, 2008

Hi there,

I am looking for a way to define a trigger that is a replacement for a multi-column foreign key.

I know how to a convert a single-column foreign key constraint into a trigger (i.e., to resolve diamond-structured references).

CREATE TABLE parent_tab
(
col_a INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(col_a)
);

CREATE TABLE child_tab
(
col_x INTEGER NOT NULL,
CONSTRAINT fk FOREIGN KEY (col_x) REFERENCES parent_tab(col_a) ON DELETE CASCADE
);

The conversion would remove the foreign key definition and add this trigger:

CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted))
END;

Unfortunately, now I need to resolve a situation where there is involved a multi-column foreign key.

CREATE TABLE parent_tab
(
col_a INTEGER NOT NULL,
col_b INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(col_a, col_b)
);

CREATE TABLE child_tab
(
col_x INTEGER NOT NULL,
col_y INTEGER NOT NULL,
CONSTRAINT fk FOREIGN KEY (col_x, col_y) REFERENCES parent_tab(col_a, col_b) ON DELETE CASCADE
);

This does not work, because the temporary table "deleted" might contain more than one row. How do I make sure that the values belong to the same row?

-- incorrect trigger, might delete too many rows
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted) AND child_tab.col_y IN (SELECT col_b FROM deleted))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted) AND parent_tab.col_b IN (SELECT col_b FROM deleted))
END;

-- some magic needed :-)
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted AS t1) AND child_tab.col_y IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted AS t1) AND parent_tab.col_b IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2))
END;

I know the trigger definition above is ***... but I hope that it helps to make clear what I need.

Btw., I use SQL Server 2005.

Thanks in advance,

slowjoe

View 3 Replies View Related

Referential Integrity (FK) Between Differents Databases

Aug 11, 2000

How I do, in SQL SERVER 7.0, referential integrity (Foreign Key constraints) between tables storaged in differents databases?.

Thank for any help.

View 1 Replies View Related

Referential Integrity In Mutiple Tables. Need Help.

Oct 12, 2004

I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.

Example:

Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|

Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|

Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|

Table 4
======|====|
StateID Name
======|====|

How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?

View 7 Replies View Related

Referential Integrity - Linking Multiple Tables To Transaction Table

Mar 3, 2006

I have transaction table where the rows entered into the transactioncan come a result of changes that take place if four different tables.So the situation is as follows:Transaction Table-TranId-Calc AmountTable 1 (the amount is inserted into the transaction table)- Tb1Id- Tb1AmtTable 2 (an amount is calculated based on the percentage and insertedinto the transaction table)-Tbl2Id-Tb2PercentageTable 3 (the amount is inserted into the transaction table)-Tbl3Id-Tbl3AmutTable 4 (an amount is calculated based on the percentage and insertedinto the transaction table. )-Tbl2Id-Tb2PercentageHow do I create referential integrity between the Transaction table andthe rest of the tables. When I make changes to the values in Table 1 -4, I need to be able to reflect this in the Transaction table.Thanks.

View 6 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

Constraint Between Two Tables

Jan 22, 2014

Is it possible to have a constarint between two tables based on a FK relationship and a value based on another column. For example to have a valid record in table b the TableA_ID value needs to exist in tableA and the charge Value can't be null. So row number 3 would be invalid in table B in this example.

TableA

IDCharge_Value
1100
2Null
34
4Null

TableB

TableB_IDTableA_IDSome other data
1 1 A
2 3 B
3 4 C

View 5 Replies View Related

FK Constraint From One Column To Two Different Tables

Apr 13, 2012

I have a requirement where one column of a table is supposed to have two foreign key constraints i.e. it can be referring to two different columns in two tables.

View 1 Replies View Related

How To Disable ALL Constraint In ALL Tables

Jul 23, 2005

I know to disable all constraint in a table just :ALTER TABLE table_A CHECK CONSTRAINT ALLbut how to disable ALL tables at the same time?Thanks anyway

View 7 Replies View Related

How To Get Tables Involved In Constraint

Jan 18, 2006

Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you

View 2 Replies View Related

Unique Constraint On Two Tables?

May 5, 2007

Is it possible to create a unique constraint on two tables?In mssql2000?

View 7 Replies View Related

Foreign Key Constraint- Reference 1 Of 2 Tables

Nov 2, 2007

Hi,
I don't know if this is possible, i believe not, so I'm here to ask the experts if is possible to have a foreign key constraint that references the key of one of two tables.
Like this:
I have 3 tables: TABLE X, TABLE A and TABLE B
Is it possible to the FK on TABLE X refernce the PK of TABLE A OR TABLE B?
If yes, how can I do this?
If not, I need to have a fourth table, so TABLE X references TABLE A and TABLE Y references TABLE B.
Thanks!

View 4 Replies View Related

Enforcing Foreign Key Constraint On Tables

Mar 5, 2008

Greetings all!

How can I enforce a foreign key constraint when I have two tables each one residing on a different database?

Thanks for your help in advance.

View 1 Replies View Related

Referential Integrity Constraints W/o Referential Integrity In The Db?

Mar 16, 2007

Using the new referential integrity constraints that will be made available, will it allow us to manually define the relationships between entities even if there is no true foreign key constraints setup in the database?

Lets say we deleted the FK_Orders_Customers in Northwind between orders and customers.

Or is this ability available now?

Thank for your time.

View 2 Replies View Related

Child Tables Accessing UNIQUE Constraint.

May 8, 2008

What query gives me the list of child tables accessing a unique key (But Not PK) for a given table?

For. E.g. In the folloiwng scenario I should get o/p as t3

create table t1 (id1 int primary key , id2 int unique )

create table t2 (id1 int references t1(id1))
create table t3 (id2 int references t1(id2))


------------------------
I think, therefore I am - Rene Descartes

View 2 Replies View Related

Check Constraint Across Parent-child Tables

Jul 20, 2005

Hi,DDLs and DMLs:create table #job (jobID int identity(1,1) primary key, jobNamevarchar(25) unique not null, jobEndDate dateTime, jobComplete bitdefault(0), check (([JobEndDate] is null and [JobComplete] = 0) OR([JobEndDate] is not null and [JobComplete] = 1)));Q1 with check constraint:sample dagtainsert into #job (jobName)values('first job');transaction Aupdate #jobset jobEndDate = '12/19/2003', JOBCOMPLETE=1where jobID = 3;RESULTSET/STATUS = Successupdate #jobset jobEndDate = NULL, JOBCOMPLETE=0where jobID = 3;RESULTSET/STATUS = Successtransaction Cupdate #jobset jobEndDate = '12/19/2003'where jobID = 3;RESULTSET/STATUS = Failurehow come check constraint can't set a value which is preset in thecheck constraint? If it's the way how it works with MS SQL Server2000, well, IMHO, it's limiting because the above transaction C is avalid one. Or maybe check constraint is not fit for this purpose?Maybe, it doesn't make much sense for me to go into Q2 but I'll try-- create job's child table, taskcreate table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique not null, taskEndDate dateTime, taskComplete bitdefault(0), jobID int not null references #job (jobID));-- skip check constraint for taskEndDate and taskComplete for nowNow, the Business Rule says,1) if all tasks are complete then automatically set jobComplete forthe #job table to yes;2) the jobEndDate in the #job table must be >= the last/MaxtaskEndDateI tend to think trigger would slow down data update quite a bit, so,try to stay away for this purpose if possible.Always appreciate your idea.

View 2 Replies View Related

How To Create A Constraint On A Header And Detail Tables?

Feb 5, 2008



Hello,

I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable.

Many thanks in advance.

View 3 Replies View Related

Many Databases Vs. Many Tables

Apr 14, 1999

Hi,
I'm writing an intranet application - a web survey engine.

I'd like to create a new database for each user that creates a survey form...
versus creating new tables for each survey.

Memory and disk space is not exactly the problem... just user permissions. If I dump all the results onto one database, doesn't it mean that this user can have access to all other tables(including the results of other surveys??)

Question is, does SQL Server reserve a bunch of memory for each database that I create, thereby hogging up a lot of space if I create more than one database.

Thank you.

View 1 Replies View Related

All Databases' Name With Their Tables' Name

Apr 25, 2008

hi all,

i want to write SQL query that create table and insert in this table all databases' name and their tables' name that exist in my server.

e.g

DB name Table name
------- ----------

Northwind product
Northwind customers
Northwind Employees
Pubs authors
Pubs discounts
Pubs employee
msdb log_shipping_databases
msdb log_shipping_monitor

View 3 Replies View Related

All Tables In All Databases

Mar 11, 2002

Credit for this script really goes to ToddV (see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737)

The following script issues a DBREINDEX command against every table in every database on your server. It can be modified to issue other commands, but this one in particular is helpful as we are migrating about 30 databases from SQL 7 to SQL 2000 (new server) and re-indexing is recommended. Here's the script:


DECLARE @SQL NVarchar(4000)
SET @SQL = ''

SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 6 -- skip the 6 built-in databases. Remove to process ALL

PRINT @SQL -- Only if you want to see the code you're about to execute.
EXEC (@SQL)


Notes: There is a limit (nvarchar 4000) to how big your command can be, so too many databases will halt this.

------------------------
GENERAL-ly speaking...

View 2 Replies View Related

Join 2 Tables From 2 Different Databases

Feb 6, 2007

Hi All,
 Is it possible to join 2 tables from 2 different databases in Stored Procedure.
Thanks in advance.

View 2 Replies View Related

How Can I Join Two Tables From Two Different Databases?

Aug 3, 2007

hi,
i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes...
is there any trick in the SQL statment? please help me
thank you

View 10 Replies View Related

Sending Databases/tables ?

Dec 21, 2007

Hello,
Let's say I created some tables in SQL server using the designer.  Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table?  Or how would I send somebody else the tables?
Thanks.

View 3 Replies View Related

Acess Tables From Different Databases

Jan 23, 2008

I am using Vista Business with Visual Web Developer and Sql Server Express 2005. I want to be able to access the data from two tables in a query. Both tables are attached to the application. 
To refer to the two databases I have used the following
DATABASE.OWNER.TABLE (specifically TaskMgr.dbo.TaskTable)
For both databases I get the error  "Invalid object name"
What am I missing?
Thanks

View 2 Replies View Related

Sharing Tables Between .MDF Databases

Jan 2, 2006

Hi :)

I would like to know if it is possible to link two databases together
(in my case ASPNETDB, and another mdf database) so that I can run
queries on those shared tables. For example, I would like to use the
uniqueidentifiers from the ASPNETDB tables in my own tables.

Thank you!

(I do use the latest version of Visual Web Developer).

View 3 Replies View Related

Updating Of The Tables In Two Different Databases

Dec 16, 2001

I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.
Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me

View 1 Replies View Related

Connecting Tables From Different Databases

Apr 23, 2007

Hey everyone. I am somewhat of a newbie to the database world.
I have been given the task of connecting two different SQL databases. Both are Microsoft SQL.

I want some of the tables of one of the databases to be linked to the tables of the other.

Is this possible?


Thanks

RoadHired

View 1 Replies View Related

Combining Two Tables From Different Databases

Jul 2, 2004

I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.

To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.

To better illustrate:

database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58

The overlapping cusno's are not the same customer.

How can I get all the cusno from the arcus file in database two to the arcus file in database one?

Is this even possible?

View 4 Replies View Related

Relating Tables From Different Databases

Nov 29, 2006

Hi,

I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.

select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey

Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help???
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ...........
Thanks

View 1 Replies View Related

Join Between Tables On Different Databases

Jan 22, 2004

Is it possible to make a join between two tables from different databases on same server? if yes, how can we do that?

And also if I want to make a join between tables on different databases on different server?how to do this. Please advise.

View 2 Replies View Related

Should I Seperate Tables Into New Databases??

May 22, 2008

My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.

View 5 Replies View Related

Synchronize 2 Tables In 2 Different Databases

Apr 24, 2007

i have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.

is this possible?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved