| Author |
Topic |
|
prettyjenny
Yak Posting Veteran
57 Posts |
 Posted - 2005-09-14 : 10:10:53
|
| I was trying to delete data from all tables in my database (about 50 tables). I used this stmt DELETE from tablename, and I received this error:Please help.Server: Msg 547, Level 16, State 1, Line 1DELETE statement conflicted with COLUMN REFERENCE constraint 'stu_hours_FK3'. The conflict occurred in database 'jenny', table 'INVOICE_HOURS', column stu_ID'.The statement has been terminated.There is no stupid question.www.single123.com |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-14 : 10:17:53
|
| First you need to delete Child table INVOICE_HOURS then parent tableMadhivananFailing to plan is Planning to fail |
 |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-14 : 10:19:52
|
| I like declared referential inttegrity but it can be a little bit of a pain to work with at times. You are trying to violate a foriegn key constraint on the table you are trying to delete from. This may result in some orphaned records. Drop the stu_hours_FK3 using ALTER TABLE and then do your delete.====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
|
|
|
prettyjenny
Yak Posting Veteran
57 Posts |
Posted - 2005-09-14 : 10:36:05
|
| THANKS, GUYS, IT WORKED.JENNYThere is no stupid question.www.single123.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-14 : 12:49:23
|
quote:
Originally posted by Thrasymachus
Drop the stu_hours_FK3 using ALTER TABLE and then do your delete.
Sean,You're scaring me.Pretty, make sure you put it back.Also, you'd be better served in knowing all of your relationships in the database.Using TRUNCATE TABLE instead of delete, and save the script.This will save the explosive growth you'll see in the Transaction Logs.BTW, you are making backups and dumpin the tranny logs, right?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-14 : 14:09:30
|
"Using TRUNCATE TABLE instead of delete, and save the script"Except on tables that have FKs - unless you drop them first - but that's where Brett came in  Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-14 : 14:50:05
|
| I don't ever TRUNCATE production data. I don't care how much space is needed for the transaction logTara |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-14 : 17:02:05
|
| Brett,hold on here. she said she was deleting data from all the tables in her database not all the data from all of the tables in her database. Semantics? maybe, but that is how I read the question and that is why I said used a delete. I warned her about orphaning the data and I guess I just assumed she would reapply the constraint. Whatever. Another happy customer.====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-14 : 18:03:04
|
| I think if someone is at the level where they have to ask that question, I would never tell them to drop a constraint, but would just explain that they have to delete the child rows first for the rows they want to delete. They might have trouble working it out, but they would have a better shot at getting it correct. As far as that goes, I would never drop the constraint myself in a production database.Gonna be a lot of unhappy people in the billing department over at www.single123.com if she didn't do that right.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-15 : 02:11:39
|
| "I would never drop the constraint myself in a production database"Never say never! Had to do it only yesterday. Application had let the user put spaces in a primary key field that then did not survive passing through a URL (with the %20 encoding thingie).So had to do a REPLACE to "_" on that column ... and all the child tables.I only had to drop/re-create one FK - which was particularly bad news as it turns out because I found 5 places where the code was used and should have been covered by an FK.So another public execution today - this time of myself! I may be gone some time chaps Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-15 : 22:23:05
|
You just illustrated one of the best arguments for using surrogate primary keys.Since I always use surrogate primary keys, my statement of "I would never drop the constraint myself in a production database" is a lot easier to stick with. The primary key in your scenario would just be a unique constraint on a column; change it once and you’re done.Of course, I realize that databases also get designed by other people, inherited from someone, or bought as part of an application package, so I may get a chance to eat my words tomorrow.quote:
Originally posted by Kristen
"I would never drop the constraint myself in a production database"Never say never! Had to do it only yesterday. Application had let the user put spaces in a primary key field that then did not survive passing through a URL (with the %20 encoding thingie).So had to do a REPLACE to "_" on that column ... and all the child tables.I only had to drop/re-create one FK - which was particularly bad news as it turns out because I found 5 places where the code was used and should have been covered by an FK.So another public execution today - this time of myself! I may be gone some time chaps Kristen
CODO ERGO SUM |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-09-15 : 23:01:08
|
| >> Application had let the user put spaces in a primary key fieldNo it didn't... the database did.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-16 : 01:02:24
|
| Good point. Very good point in fact. Darn it!Kristen |
|
|
|
|
DELETE foreign key conflickt