Delete Record from Multitable has problem

Question

0.00/5 (No votes)

See more:

Hi all Visitors
I have problem with deleting the record from multi tables
I have 3 tables.Table Employee(empcode,empname,address)
Table userAccount(ucode,name,empcode) and table sale(scode,empcode,amount)

When i delete one record from table Employee,it will error.

Anybody know about this,Please help me?

Best Regards,

Posted 29-Mar-11 23:08pm

soeun tony

Add a Solution

Comments

walterhevedeich 30-Mar-11 5:12am

The question is unclear. What is the error you are getting?

3 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)

Dylan Morley · Answer 1 · 2011-03-29T23:22:00

Without further information, I'm guessing you're seeing a referential integrity error.

You're trying to delete an 'Employee' record and there could be records in the other tables that rely on 'Employee' and have Foreign Keys being enforced.

You can fix this in a couple of ways.

1) Delete all data from related tables first, before deleting from your Employee Table.

2) On the Index of the primary table (Employees), make sure it's set to 'Cascade Delete Related Records'

Personally, I prefer the first approach

Manfred Rudolf Bihy · Answer 2 · 2011-03-29T23:26:00

Most likely caused by a foreign key relationship. I guess that tables userAccount and sale both have a foreignkey relationship to table Employee. The entries in table userAccount and sales that have a reference to Employee via empcode would have to be deleted first.
Another option is to set CASCADE ON DELETE so that when an Employee gets deleted all entries in userAccount and Sale that have a reference to that employee will also get deleted.

This has to do with referential integrity.

Hope that helps you!

*ZeeroC00l* · Answer 3 · 2011-03-31T00:35:00

As Dylan said you have two ways of doing it.

No offcence, but i personally don't suggest you to use the 1st method. As it will require you to write more queries and you need to execute more number of commands on the database reducing the performance. And the job is tedious too. But The method is rock solid and will not cause any trouble as you will be executing the delete command on all the related tables.

But If you are using database like MySql, SQL server or MS Access, there is another way of doing it which make life easier. ;)

i.e. Using Relation. Like the option ON DELETE CASCADE in MySQL, SQL Server etc.

ON DELETE CASCADE[^]

And Create Relation[^]option provided by MS Access might help you to tacle with the prob.

Delete Record from Multitable has problem

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0