Cascade

Mar 6, 2000

Hi! I'm new to SQL server. I need help.

I have 2 tables:

Table A (A_ID, X) where A_ID is the primary key.
Table B (B_ID, A_ID, Y) where B_ID is the primary key, and A_ID is the foreign key.

I want to change the value of A_ID of Table A, say A1 into A2. How can I automatically change the value of A_ID in Table B if the record exists?

How can I delete a record in Table A and the corresponding record in Table B is deleted automatically?

Thanks in advance!

Chung

View 1 Replies

Cascade Update

Jul 8, 2006

Does anyone know how to do a cascade update in SQL 2005 using the studio manager?Basically, I have a project table with a status column. If the status is set to 2, then I need to update another table that references the project id.

View 1 Replies View Related

Can't Cascade Delete

Feb 14, 2007

I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative. Thanks

View 2 Replies View Related

Cascade Deletes

Jun 11, 2002

Hi,

We are developing a new oltp application using SQL Server 2000 and are debating whether to use "cascade delets" or not. In earlier apps, we had issues using cascade deletes like someone deleted parent by mistake and all child records got deleted OR SQL Server crashed in middle of cascade delete and records were lost or performance became an issue when there were huge # of child records to be deleted,etc.

Are there any recommendations for/against using Cascade deletes and when to use and when NOT to use cascade deletes ?

Thanks

Satish

View 2 Replies View Related

On Update Cascade

Aug 27, 2006

Hello all,
I am new to SQL and I was hoping someone could explain something to me about 'on update cascade'.
I understand what 'on update cascade' does (i think) - it updates the child table when the parent table is updated.
What i do not understand is that the 'on update cascade' works on the primary key of the parent table, but i was on the understanding that the primary key doesn't change often if at all so why would the 'on update cascade' be of use?
Sorry for my ignorance and i realise i must be missing something simple but would be grateful for any help.
Thanks

View 5 Replies View Related

CASCADE Question

Nov 6, 2006

OK, so now I'm just being lazy, but after seeing a question about this, I wonder which is actually better?

Assuming that you have SPROC only access to your data, which is better/more effeicient.

Having the stored procedure actually do the deletes/updates (which is the way I always do this) or enabling CASCADE DELETE/UPADTE in the database?

Just curious, what do most people do.

Logically to me, the "UPDATE" is actually a logical DELETE and INSERT anyway because it's a new "thing"

Unless of course you buy into surrogates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View 12 Replies View Related

How To Use Cascade Trigger

Apr 23, 2007

i just got a job and i have to learn triggers to pass my test , however,i'mfinding the cascade trigger a bit difficult, does it update other tables automatically when a column is updated?

View 2 Replies View Related

Cascade Delete

Nov 13, 2007

I am a C# programmer also acting as the dba for a SQL Server project. I have a table called Folder that has child records in a table called FolderItems. When a Folder is deleted I want the FolderItems to be deleted. I know how to accomplish via code, but I would prefer to have SQL Server perform the delete just in case a folder is deleted via SQL Management Studio or other method.

Is a trigger the way to go about this? I know how to create triggers For Delete, but I just wondering is this the best method to create a Cascade delete or does SQL have any built in "Cascade" delete features.

I am using SQL Server 2005 if this matters.

Thank you,

Corby Nichols
Flying Elephant Software

View 7 Replies View Related

ON DELETE CASCADE

Mar 7, 2008

Helle people. The question is:
I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks

View 1 Replies View Related

Delete Cascade

Mar 12, 2008

Hi,

I want to know more about delete cascade feature of SQL Server 2005.

View 6 Replies View Related

How Can I Use Cascade Delete For The For The Following Situation.

Jan 25, 2008

Hi, I have the following tables:
Categories {Category_ID, Column2, ...}
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?

View 2 Replies View Related

Cascade Deletes In SQL 2005

Jun 14, 2006

I have a logins table, a loginroles table (intermediate), and a roles table
When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.
Any help would be appreciated.
Thank You,Jason

View 3 Replies View Related

Performing A Cascade Delete

Sep 3, 2002

How to delete a record from a parent table, all relating records in the child tables should also delete.
How to do it by MSSQL 7.0

ANB

View 1 Replies View Related

Cascade Delete And Update In SQL 7.0

Jul 23, 1999

Is it possible to cascade update and delete while DRI remains there ? That
is without deleteing refrential integrity. If anyone have example of cascade delete and Update using Pubs or Northwind Example , i'll be really obliged.

View 1 Replies View Related

Cascade Updates And Deletes

Feb 27, 2001

It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in

View 4 Replies View Related

Cascade Delete,any Limits

Jun 30, 2004

Hi ,
I have 4 GB db with 6 tables,

table A parent of table B with cascade
update,delete

table B parent of C,D,E,F with cascade
update,delete

Today I deleted range of values(7000 rows) from table A, but NOT ALL data

deleted from B,C,D,E,F

Is any limitation for cascade delete

Db set to simple recovery, no errors deleting data

View 1 Replies View Related

Is There On Delete Cascade In Sql Server?????

Aug 4, 1999

Hi everyone,

I would to make a cascade deletion. I dont know how to do it in SQLServer.

Thanks very much.

View 2 Replies View Related

Cascade Delete Question

May 3, 2006

I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table.

tblUsers
---------------
ID_PK
Fname
Lname
...

tblUser_Phone
----------------
ID_FK
Phone
...

tbUser_Email
----------------
ID_FK
Email
...

If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?

Thanks in advance.

View 2 Replies View Related

Cascade Combo Boxes

Nov 2, 2006

I follow sql coding for cascading combo boxes that populates them
if the first one populates the cdname the second one should populate the cd group and the third one the composers with the songs or the samthing with music hymnals. I am trying the steps they aren't populating. Where are simple books on this?

mikevds@optonline.net

View 1 Replies View Related

Cascade Delete Problem

Nov 2, 2006

Hi all, I've been reading for a couple of weeks but this is my first question so please be gentle with me.

I'm pretty new to SQL Server, though I've worked with Access for years. I've got four tables (I'm simplifying) and I need to set up cascade deletes between them. I understand how to create foreign keys and all that, but SQL Server is telling me it can't create my keys because it will create multiple cascade paths. I understand that too, I just need to find a way around it. Here are the tables...

dbo.JOBS
Job_Number

dbo.COLUMNS
JOBS_Job_Number
Column_Number

dbo.ROWS
JOBS_Job_Number
Row_Number

dbo.GRID_DATA
JOBS_Job_Number
Column_Number
Row_Number
Data

Currently I have keys set to cascade delete between JOBS>COLUMNS and JOBS>ROWS so that when the user deletes a job, the columns and rows for that job are deleted as well. No problem there.

If the user deletes a column, I have to delete all the grid data for that column and job. Same with rows. So I tried to establish cascade deletes between COLUMNS>GRID_DATA and ROWS>GRID_DATA and that's where I got in trouble. I assume the reason is that if the user deletes a job, it's going to delete the columns and rows, and I've got multiple cascade paths coming from COLUMNS and ROWS to GRID_DATA. I get it.

The question is, what can I do about it? I don't have a cascade delete between JOBS>GRID_DATA, although I do want that behavior, but I was assuming it just would flow through COLUMNS or ROWS. I haven't bothered to add that key as I'm guessing that would just make the situation worse. Anyone got any advice for me on this one?

Thanks!
Ron Moses
ConEst Software Systems

View 12 Replies View Related

Problem With Cascade Update

Apr 8, 2007

i want to reffer A1,A2 column from a Table A to B1 column of Table B. when i use the on update cascade option for the columns A1 & A2 i get an error for the 2nd one. sql:

alter table A
add constraint fk_key1 foreign key (a1) references b(b1) on update cascade;

alter table A
add constraint fk_key2 foreign key (a2) references b(b1) on update cascade;

after executing the sql i get the following error for the 2nd sql:

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

if i dont use cascade option then i dont get any error.

but i need to synchronize the data. now can anyone help me how to add the cascade option working for the 2nd column of Table A without seperating the column in a different table?

thnx

S ]-[ /- | ]-[ /- N

View 2 Replies View Related

Logging On Delete Cascade

Jul 23, 2005

Hi. I am trying to log (Not SQL Transaction log) all deleted rowsinto a custome log file after performing "cascade delete".I couldn't find any reference for this in SQL manual or News group.Byung Choi

View 3 Replies View Related

Cascade Update / Foreign Key

Jul 20, 2005

Hi!For the sake of simplicity, I have three tables, Employee, Department andWorkEmployee >---- Department / /^ ^WorkThe Work table have two columns, empno and depno and consists that theemployee has worked on another department.Here is my scripts:create table employee (empno int not null primary key, depno int not null)create table department (depno int not null primary key)create table work (empno int not null, depno int not null)alter table employee add constraint fk_employee_department foreign key(depno)references department (depno)on update cascadealter table work add constraint fk_work_employee foreign key (empno)references employee (empno)on update cascadealter table work add constraint fk_work_department foreign key (depno)references department (depno)on update cascadeMy problem is the last command. SQL Server responds:Server: Msg 1785, Level 16, State 1, Line 1Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' maycause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ONUPDATE NO ACTION, or modify other FOREIGN KEY constraints.But I want the depno in the work table to be updated when a department.depnochanges a value.Does anyone have a suggestion on how to overcome this problem?Thanks in advanceBest regards,Gunnar V�yenliEDB-konsulent asNORWAY

View 2 Replies View Related

Cascade Delete Problem..

Sep 6, 2007

Hi,
I have a Configuration Table tblConfig with 45 fields-
PK_Config_Id
ConfigCreatedOn
ConfigEditedOn
ConfigStartDate
ConfigEndDate
ConfigFor
��.
��.
��.

And an Employee table tblEmployee
PK_Emp_Id
EmpName
��.
��.
��.

I would create a Configuration and then apply this config to some Employees ��
So, I created a table tblEmployee_Config with 2 fields
FK_Config_Id
FK_Emp_Id

But I MAY need to change(Edit) few of the properties(fields) of the Configuration record associated to an emp. Therefore I need all the fields of tblConfig table along with employee key in tblEmployee_Config
So, I modified tblEmployee_Config to ��
FK_Emp_Id
FK_Config_Id
ConfigCreatedOn
ConfigEditedOn
ConfigStartDate
ConfigEndDate
ConfigFor
��.
��.
��.
i.e. putting all the fields of the tblConfig into tblEmployee_Config along with emp key

But instead of having 2 almost similar tables (tblConfig and tblEmployee_Config), I merged both the tables into 1 table having Configurations as well as Employees associated to Configs as��
PK_Config_Id
FK_Emp_Id
FK_Config_Id
ConfigCreatedOn
ConfigEditedOn
ConfigStartDate
ConfigEndDate
ConfigFor
��.
��.
��. Where FK_Emp_Id and FK_Config_Id would be NULL for Configurations.

Like----
PK_Config_Id FK_Emp_Id FK_Config_Id AllowBrks WorkHrs
1 NULL NULL Yes 9
2 100 1 Yes 9
3 101 1 No 8

Here record 1 is a Configuration and records 2 and 3 are Emps records that are associated to Config 1.

Now my question is ��
1. Is it correct if I put a relation like
Primary key Foreign Key
tblEmployee_Config tblEmployee_Config
PK_Config_Id FK_Config_Id

2. Can I perform cascade delete on the same table tblEmployee_Config such that��
When record 1 is deleted, records 2 and 3 should also get deleted because they refer to the first record?

View 1 Replies View Related

Relationships: Cascade Delete

Nov 4, 2006

Hi,

I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...)

I also have a Project invoice table, in which ProjectID is referred as Foreign key.

Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data.

Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program?

Thanks!

View 1 Replies View Related

Cascade Update/deletes

Apr 28, 2008

Hi guys,

A simple question of good practices: is it better to have a cascade update/delete on a table? Or is it better to do it "by hand" in a SP?

Thanks a lot.

View 1 Replies View Related

Trigger Cascade Update

Jul 29, 2007

i run this cmd to execute a cascade update to another database but did not work.

USE [testdb1]
GO
/****** Object: Trigger [dbo].[tgr_Upddb2] Script Date: 07/29/2007 08:34:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tgr_Upddb2]
ON [dbo].[Table1]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE U
SET u.fk_fld = i.pk_fld
FROM inserted AS I
JOIN testdb2.dbo.table2 AS U
ON U.fk_fld = I.pk_fld
END

heres the db structure

View 3 Replies View Related

Update And Delete Rule - Cascade

Jan 15, 2007

Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...).
So I created a table with three foreign key constraints (two for busstops and one for type).
When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting.
But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
How can I achieve my task? Why should it cause cycles or multiple cascade paths?

View 1 Replies View Related

How Can I Find Out Relationships With Cascade Delete

Sep 20, 2007

I'm using Management Studio Express, is there a way to find out what relationships of a database have cascade delete set to true?
Also is there a way to change the settings (properties) of a relattionship without having to delete it and add it back?

View 1 Replies View Related

When Updating Keys How Do Cascade The Changes To Other Table?

Dec 13, 2007

I'm using SqlDatasource when the update process is on a primary key, how do you cascade the changes to other tables?
Also when deleting does the cascade deletes the row from another table or only sets the primary key to NULL?

View 1 Replies View Related

SQL Server 2005 No Cascade On Delete

Apr 12, 2006

I am developing an application with ASP .NET 2.0 using SQL Server 2005. I have a very detailed set of relational tables I would like to use to store my data, however I would like to use the built in Membership functionality of .NET 2.0.What I've done is created MembershipProvider that on user creation links the aspnet_Users table to my custome User table.UserFK guid aspNetId int userIdI then use my own userId to link into other data which uses their PKs to link to other tables, etc, etc like any good relational DB should.My question is when I delete a user using the ASP .NET interface I get a contraint error because my User table references the aspnet_Userts.UserId. I initially wanted to toss Cascade On Delete on my tables which would take care of my problem. I was shocked to find out Sql Server 2005 doesnt support Cascades.. and its suggested that Triggers be used instead (ill durned if im gonna make triggers for all my tables)My question is, what is the best practice in deleting from tabled linked with PK/FK relationships? There MUST be a good way to handle this.thanks

View 5 Replies View Related

Cascade Deletions Of Children When FKs Present

Dec 8, 1999

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

View 1 Replies View Related

Find All Cascade Delete Constraints

Nov 2, 2006

I've been handed a database with over 100 tables and told to find everywhere a cascade delete constraint exists. I could just go through every table by hand and check, but I think there must be an easier way, perhaps an sql query on the master db. Any thoughts?

View 4 Replies View Related