Cascading to delete

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I developing a new database,
The entity table contains [ID(Primary Key), Name, Date, Identity(Primary Key)].
The Child Tables as follows,
1. State [StateID(Primary Key & Foreign Key with Entity Table), Identity(Foreign Key with Entity Table)]
2. City [CityID(Primary Key & Foreign Key with Entity Table), Identity(Foreign Key with Entity Table)]
3. Street[StreetID(Primary Key & Foreign Key with Entity Table), Identity(Foreign Key with Entity Table)]

If i want to delete a particular ID in entity table, it should delete in entire child tables too. For me, The Entity Table contains more number of foreign key, so i can't able to use cascading function. Whether, while using more number of primary key and foreign key affected in the table. We can able to create an option of cascading? What may be the reason?

I having 1 Master Table and 5 Child table, the every child table among them and i gave "CASCADE" for delete function. If i connect all the child table to master table, and only 1 "CASCADE" only allowing. Kindly give me a solution to give more than one cascade

Posted 22-Dec-11 22:32pm

Member 7744835

Updated 23-Dec-11 18:10pm

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-12-22T22:37:00

Don't quite understand your question. A single table cannot have multiple primary keys. A primary key can be a composite key but that won't make foreign key constraints unusable.

If having a composite key is the problem, just define all the columns necessary to the primary key. However based on the field names they don't look like they would be composite.

Sarvesh Kumar Gupta · Answer 2 · 2011-12-22T22:45:00

Solution 2

Hi,

You are using referencing, when you define foreign key then there there is one property to be set, cascading deletion, set it to true. Your problem will be solve.

Posted 22-Dec-11 22:45pm

Sarvesh Kumar Gupta

Comments

Member 7744835 24-Dec-11 0:14am

I maintaining 1 MASTER and 5 CHILD, While all child table related to master table, it allowing only one CASCADE. I want to set CASCADE function for all Child table.

**AmitGajjar** · Answer 3 · 2011-12-23T02:03:00

Hi,

Cascading delete is possible in your scenario as well. if you are not able to do that then there can be other workaround,

1) Make your all operation on table should be through Stored procedure.
2) do not allow direct access to table.
3) create transaction inside stored procedure
4) within transaction call delete operation for your child table.

but use above scenario only if you are unable to create cascade delete on your foreign key.

Hope this will help you,

thanks
-amit.

ujjwal uniyal · Answer 4 · 2011-12-23T02:25:00

Solution 4

yeah Cascading delete is the solution that u want but u should use it carefully

Posted 23-Dec-11 2:25am

ujjwal uniyal

Cascading to delete

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0