One database to rule them all

How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.

Thanks

You would have to write a whole lot of TRIGGERS.

That 'could' be a substaintial performance penalty.

(And most likely a very unwieldy design.)

Let us know what you are attempting to accomplish, and perhaps we can guide you to a solution that will work AND be efficient.

|||I am attempting to update all the IDs throughout all of my databases. For example, imagine you had several databases for different applications. Maybe one application is for health information, another application is penitentary clients, and a third application is for penitentary client rehabilitation. A penitentary client would be assigned a unique number and the client shows up in the penitentary client program. If the client receives health or rehabilitation services, then their unique number is used in the other applications so you don't need to duplicate information such as their name. However, for some reason the clients number has to be changed. I want to be able to change the information in the penitentary clients db and have it automatically change in the other databases. If for some reason the ID changes at 3:00 PM, then it would be nice to have all the data updated in real time without having to wait to run a job that updates all of the data. If I get a list of new client IDs to be updated, then I would like a better way to update the tables in a database rather than running an update statement against several tables in different dbs. If a table gets added to a db, I don't want to have to remember to go and add another update statement to some job.

One database to rule them all

How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.

Thanks

You would have to write a whole lot of TRIGGERS.

That 'could' be a substaintial performance penalty.

(And most likely a very unwieldy design.)

Let us know what you are attempting to accomplish, and perhaps we can guide you to a solution that will work AND be efficient.

|||I am attempting to update all the IDs throughout all of my databases. For example, imagine you had several databases for different applications. Maybe one application is for health information, another application is penitentary clients, and a third application is for penitentary client rehabilitation. A penitentary client would be assigned a unique number and the client shows up in the penitentary client program. If the client receives health or rehabilitation services, then their unique number is used in the other applications so you don't need to duplicate information such as their name. However, for some reason the clients number has to be changed. I want to be able to change the information in the penitentary clients db and have it automatically change in the other databases. If for some reason the ID changes at 3:00 PM, then it would be nice to have all the data updated in real time without having to wait to run a job that updates all of the data. If I get a list of new client IDs to be updated, then I would like a better way to update the tables in a database rather than running an update statement against several tables in different dbs. If a table gets added to a db, I don't want to have to remember to go and add another update statement to some job.
sql

on update catch 22

How do you make an update cascade opperation if you want to set the PK to an excisting one?
I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post.
How can I do that using on update cascade/delete. I see a catch 22 coming up!

/Jonneponne

Hi

I would redirect all dependencies to a single post you want to keep and then delete a duplicate post.

ON UPDATE CASCADE works only if you are trying to update the KEY references by Foreign Key , not the other way round.

NB.

On DELETE On UPDATE Cascade syntax error

Hello

I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems.

I have already created the tables with primary keys on each table and foreign keys linking each table to the next.

I tried to delete a row from the parent table and was given this error:

DELETE FROM [dbo].[DomNam]
WHERE [DomNam]=N' football '

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.

I tried to insert an alter table query:

ALTER TABLE dbo.DomNam
ADD CONSTRAINT FK_Dom_ID

REFERENCES dbo.Dom_CatA (Dom_ID)

ON DELETE CASCADE ON UPDATE CASCADE

But on Execute I saw this error:

Error]
Incorrect syntax for definition of the 'TABLE' constraint

What is wrong with the above syntax?

Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?
If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables.

I would be grateful for any advice. Thanks.

Hi

Have you missed something like this:

ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_IDFOREIGN KEY (Dom_ID)REFERENCES dbo.Dom_CatA (Dom_ID)ON DELETE CASCADE ON UPDATE CASCADE

Trigger could result such an error( DELETE statement conflicted with COLUMN REFERENCE constraint ..... ) either.

|||

(I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.)

The above means what you are looking for require a trigger because Cascade On DELETE and UPDATE is DRI(declarative referential integrity), that means if A references B then B must exist, it is very clean simple relational Algebra, anything outside that you need a trigger. But you may have some columns that qualify for it, try the threads below for how to enable it. Hope this helps.

http://forums.asp.net/thread/1315554.aspx

http://forums.asp.net/thread/1120122.aspx

|||

Hi

Thanks guys for your reply and help.

I think that my problem is that I have created a many to many relationship without foreign key restraints.

On the parent table I have used the primary key from the first child table "A" as the foreign key.

The child tables are from A to Z.

With the first child table "A" I have used the Primary key from the parent as the foreign key.

With the child table "B" I have used the Primary key from the previous "A" table as the foreign key in "B" table. I have used the primary key as the foreign key in all subsequent tables without declaring referential integrity.

I know that in the very near future I will need to update and delete much of the records in these tables.

How do I remove the foreign keys so that I might be able to create a junction table, which I have since found that I need for a many to many table relationship. Or is there another easier way to do this?

Thanks

Lynn

|||

Hi guys

I have since learned how to delete the foreign keys.

I am viewing my SQL 2000 database using Aqua Data,

* Click on the last sibbling table.

* Click Alter table

* Select constrains tab

* Select FK + delete + OK

I have kept my Primary Keys in tact.

Now I need to relink my tables and ensure that I am able to cascade update or delete. All tables contain similar content, which will require updating or deleting often. Tables are from A to Z.. What is the best method to do this?

Thanks

Lynn

|||I have told you that all you can do is one to one referencing for DRI(declarative referential integrity) to work so if you have figured out the tables that qualify for if A references B then B must exist at the top of Enterprise Manager you will see enable relationship. And those that did not qualify you need a trigger. I hope I am very clear it is not DRI for A to Z but A to B. Hope this helps.|||

I just remembered I had a conversation with a database person who did not understand DRI(declarative referential integrity) so let me explain you have tables A to Z meaning 26 tables it means you can have only 13 DRIs that is 13 Cascade DELETE and Cascade UPDATE because it is a location based way to delete and update related data. So although foreign key is required to enable DRI not all foreign keys can be DRI.

You said the data is related that is DDL(data definition language) where relationship is determined by Upper and Lower bound Cardinality while DRI is DML(data manipulation language) keeping track of child data through one to one mapping if A references B then B must exist. I hope this makes it clear. You have to be careful with the ALTER TABLE ADD or DROP CONSTRAINT in 2000 because most ALTER must be done with Enterprise Manager or SQL Server 2000 will tell you it is wrong. One more thing you can use trigger to do it the way you want but triggers are resource intensive and may not fire all the time. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

|||

Hi Caddre

Thanks for the reply, as you have gathered I am not a sql database expert, I am trying to understand DRI.

The whole think would be easier if I had everything in the one table (but it would be too wide to manage) because the same column of data is repeated on every table A to Z., therefore data row of "John Smith" exists in all tables. But in the child tables "A" table for example "John Smith" has extra columns e.g. art, apples, auto, etc.,

Consequently if I remove or insert a row in parent table, the children need the same edit.

Would it be just as complicated if I created a new table "juntion table" and include all the primary key's from the parent and children tables as foreign keys in the junction table. Or would that not work?

Thanks

Lynn

|||

I am sorry I did not come to this thread to confuse you but what you are saying now is not relational, so I have found you two solutions you can add to valid DRI. I also think it is Microsoft documentation that confused you because in 2005 multiple Cascade is allowed but one table cannot be repeated twice that is the same thing I am saying. Hope this helps.

http://support.microsoft.com/kb/142480

http://msdn2.microsoft.com/en-us/library/aa224818(sql.80).aspx

ON DELETE CASCADE problem for same table column constraint

Hello,
I would like to have table with foreign key referencing to the column of the
same table. And I want to specify ON DELETE CASCADE to this column, e.g.:
create table category (
ID INTEGER IDENTITY(1,1) NOT NULL,
PARENT_ID INTEGER NULL,
NAME VARCHAR(100) NOT NULL,
CONSTRAINT CAT_PK PRIMARY KEY (ID),
CONSTRAINT CAT_FK FOREIGN KEY (PARENT_ID)
REFERENCES CAT(ID) ON DELETE CASCADE
)
But I receive error:
--
Error: java.sql.SQLException: Introducing FOREIGN KEY constraint 'CAT_FK'
on table 'category' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints., SQL State: S1000, Error Code: 1785

It means I cannot specify ON DELETE CASCADE clause.
HOW CAN I ENSURE CASCADE DELETING FOR ALL RECORDS (ALL CATEGORIES WITH BELONGED SUBCATEGORIES)?
IS THERE POSSIBILITY TO USE STORED PROCEDURE FOR THIS?
CAN YOU POST AN EXAMPLE PLEASE?

Thank you in advance
best regards,
Julian LegenyThis is a restriction in all versions of SQL Server that supports cascading constraints. You will have to implement the cascade action using triggers or in your SPs that perform the data modifications.

on delete cascade on the same table - is it possible?

Hi !
as u should know this code doesn't work ..
CREATE TABLE tempo (
cid int,
parent_id int,
PRIMARY KEY (cid),
FOREIGN KEY (parent_id) REFERENCES tempo ON DELETE CASCADE
)
any suggestion to achieve this ?
thx!Create a trigger to delete all the records with the same criteria. When a record is deleted in that Table.

This Code may help

create trigger delcascadetrig
on tempo
for delete
as
delete parent_id
from tempo, deleted
where tempo.cid = deleted.cid

on delete cascade & hierarchical table

for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE

CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE

but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under

thank you for helpingI tried running your code on SQL Server 2005, and got the following message:

Introducing FOREIGN KEY constraint 'FK_Users_Sup' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In my opinion, this error message tells pretty well why you are not allowed to use on delete cascade on a self-join.|||You will need to implement this cascading referential integrity using a trigger.

ON DELETE CASCADE

hello guys!

Well im using MS SQL 2000 and i have over 250 Tables in one of my Database the problem is all the Foreign Key in the Table is not turn ON in ON DELETE CASCADE.

The question is, is there anyway to write a script to turn the ON DELETE CASCADE on all the Tables?

Thanks for the reply!

Novelle

Nope, not really. You will just have to script your keys out, then change to ON DELETE CASCADE manually. Unless there is some tool to do it, but I don't know of it.

A trick I would use is to take your database scripts (or if you don't have any, use a comparison tool, compare my full database to an empty one) and just replace all ON DELETE NO ACTION values with ON DELETE CASCADE. Then use your script to build a database that matched your database, except for the CASCADE constraints.

Then just do the comparison again, and let the tool do the work. (I use RedGate personally, but there are others)

sql

On delete cascade

Hi,
1. To my knowledge from sql 2k, there is an option "On delete cascade" and
"On update cascade". But in almost all databases which I have seen till date
,
user defined SP's have been created for deleting table content instead of
checking this "On delete cascade" option. Is there any specific reason for
this? i.e., will there be any performance or anyother reason behind this?
2. Lets assume that I have enabled "On delete cascade" in my DB. If I delete
a record in master table it should delete corresponding details in 4 to 5
tables. What would happen if it fails for some reasons while deleting the 3r
d
table content. Will it rollback?
Regards
Pradeep1. "will there be any performance or anyother reason behind this?"
-No, some guys really want to have their own hand on the deleting of
the data, due to extra issues with logging business rules checking etc.
2. "Will it rollback?"
Sure, thats what a transactional database is for.
HTH, Jens Suessmeyer.|||If can be a safety issue too.
For example, if you use Enterprise Manager to delete a parent row,
referential integrity will prevent you from deleting it if there are child
rows. With cascade delete turned on, the parent and all related children ar
e
deleted. If it was deleted by mistake, it can be quite a complex task to
restore the parent and all the children.
Joe
"Jens" wrote:

> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>|||There are technical reasons behind this as well. Cascading referential
actions complicate deadlock minimization because there isn't any way to
determine with certainty the order in which locks will be obtained. If you
issue the DELETE or UPDATE statements in a procedure, you have control over
the order in which locks are obtained and thus can prevent most deadlocks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133783057.518482.200520@.g47g2000cwa.googlegroups.com...
> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>