cascade

Nested SQL Server transaction performing cascade delete

Suppose I have a table called Companies that has a DepartmentID column. There's also a Departaments table that has as EmployeeID column. Of course I have an Employee table as well. The problem is that I want to delete a company, so first i have to delete all the employees for every departament and then all the departaments in the company...

When/Why to use Cascading in SQL Server?

When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it? This probably applies to other databases as well. I'm looking most of all for concrete examples of each scenario, preferably from someone who has used them successfully. ...

override constraint from no action to cascading at runtime

I feel like I have a verry basic/stupid question, yet I never saw/read/heard anything in this direction. Say I have a table users(userId, name) and a table preferences(id, userId, language). The example is trivial, but could be extended to a situation with multi-level relations and way more tables.. When my UI requests to delete a user ...

Does CASCADE Delete execute as transaction?

I want to perform cascade delete for some tables in my database, but I'm interested in what happens in case there's a failure when deleting something. Will everything rollback? ...

In SQL Server 2005, can I do a cascade delete without setting the property on my tables?

I have a database full of customer data. It's so big that it's really cumbersome to operate on, and I'd rather just slim it down to 10% of the customers, which is plenty for development. I have an awful lot of tables and I don't want to alter them all with "ON DELETE CASCADE", especially because this is a one-time deal. Can I do a del...

Hibernate Delete Cascade

I Have one entity [Project] that contains a collection of other entities [Questions]. I have mapped the relation with a cascade attribute of "all-delete-orphan". In my DB the relation is mapped with a project_id (FK) field on the questions table. this field cannot be null since I don't want a Question without a Project. When I do sess...

How to cascade rename PK on table, MS SQL 2005

I have successfully been able to rename a table and drop all constraints on that table with foreign key relationships and build they all back up. However, now I am at a point where the PK_tblFoo exists in more than one place (when I transfer the table to another DB). Renaming the table does not rename the primary key. How would I cascad...

Merge Primary Keys - Cascade Update

Is there a way to merge two primary keys into one and then cascade update all affected relationships? Here's the scenario: Customers (idCustomer int PK, Company varchar(50), etc) CustomerContacts (idCustomerContact int PK, idCustomer int FK, Name varchar(50), etc) CustomerNotes (idCustomerNote int PK, idCustomer int FK, Note Text, et...

How do the Postgres foreign key 'on update' and 'on delete' options work?

Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them? ...

Should I use the CASCADE DELETE rule?

I've always been too scared to use DELETE CASCADE, but as I get more confident (lazy :D), I'm thinking how bad can it be, is it best practise to use it or should I avoid it and clean up my foreign keys etc the old fashioned way (with stored procedures)? ...

How to retrieve the Id of an entity persisted via cascade with JPA

If one has a regular Foo entity and call persist(foo) the @Id is automatically set on the entity. However, if the said Foo entity has a collection of Bars bound to it via (...) @OneToMany(cascade=Cascade.ALL) Set<Bar> getBars() (...) and such instance is already persisted, if one creates a new Bar and add it to the Bars collection o...

Hibernate: How to make Hibernate delete records from child table when deleting parent if child is linked to parent with many-to-one?

Lets say I have two tables - "child" and "parent" with many-to-one relation. What I need is to delete child entries if parent record is deleted. It is not a problem if I link child table from parent by creating one-to-many association in parent.hbm and set cascade="all-delete-orphan". The problem is I don't want one-to-many relation o...

JPA cascade options at runtime

I'm trying to make an application that keeps an object model in sync with a database by observing all changes and then immediately persisting the objects in questions. Many of the object in the model have children in large lists or trees. When I load an object from the database, I rely on a one-way cascading relationship to also retrie...

How can we execute custom method while Cascade="Delete" is being executed ?

For example i have two entities. "RealEstate" and "Picture". RealEstate holds a collection of Picture. In the mapping file RealEstate has a bag in it for Pictures and cascade is Delete". If you delete a RealEstate all the related Pictures will be deleted. But is there any way to execute a custom method to delete the pictures from the we...

How do I use on delete cascade in mysql?

I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is dele...

Cascading Deletes/Updates using JPA or Inside of Database?

Performance is key: Is it better to cascade deletes/updates inside of the Database or let Hibernate/JPA take care of it? Will this effect the ability to query for the data if cascades are inside of the DBMS? I am using HSQLDB if that matters. ...

How do you determine if a database table relationship merits enforcing referential integrity?

I have an application where the majority of the database tables have a strong relationship to one other table. Currently I am enforcing referential integrity with foreign keys, but I'm wondering if this is really the best approach. Data in the primary table can be deleted from an admin interface by business users, which means having to d...

SQL: Delete rows from two tables

I have two tables. Those tables have two relation between them. Table 1 * ID_XPTO (PK) * Detail Table 2 * ID_XPTO (FK) (PK) * ID_XPTO2 (FK) (PK) Those two relations exists. Table 1 -< Table2 Table 1 -< Table2 My question is that I need to delete some row in table 1. I'm doing on that way declare @table Table (xptoTabl...

SQL Server 2005 - Foreigh Keys with Cascaded Delete

Is there a way around this in SQL Server 2005? (It bugs me, and every time I encounter it I get in to a strop. But this is the first time I've had to deal with AND been on Stack Overflow. Please, save what little sanity I posess!) DimensionTable: id INT IDENTITY(1,1) FactTable: source_id INT NOT NULL, target_id INT NOT NULL I...

Introducing FOREIGN KEY constraint 'c_name' on table 't_name' may cause cycles or multiple cascade paths.

I have a database table called Lesson: columns: [LessonID, LessonNumber, Description] ...plus some other columns I have another table called Lesson_ScoreBasedSelection: columns: [LessonID,NextLessonID_1,NextLessonID_2,NextLessonID_3] When a lesson is completed, its LessonID is looked up in the Lesson_ScoreBasedSelection table to get th...