I have 2 tables with association has_one (certification has_one bio)
when I call @object.destroy (@object is certification object) I get the
following error:
Mysql::Error: #23000Cannot delete or update a parent row: a foreign key
constraint fails (`project/certification`, CONSTRAINT
`FKD99554BA1D0B0B41` FOREIGN KEY (`_key`) REFERENCES `bio` (`_key`)):
DELETE FROM bio
WHERE _key = 2
But when I write:
ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 0"
@object.destroy
ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 1"
it works fine.
How can I delete associated row without disabling foreign key checks?
Thanks in advance
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
On Tue, Aug 29, 2006 at 01:50:19PM +0200, Dmitry Hazin wrote:> > I have 2 tables with association has_one (certification has_one bio) > when I call @object.destroy (@object is certification object) I get the > following error: > > Mysql::Error: #23000Cannot delete or update a parent row: a foreign key > constraint fails (`project/certification`, CONSTRAINT > `FKD99554BA1D0B0B41` FOREIGN KEY (`_key`) REFERENCES `bio` (`_key`)): > DELETE FROM bio > WHERE _key = 2 > > But when I write: > > ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 0" > @object.destroy > ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 1" > > it works fine. > > How can I delete associated row without disabling foreign key checks?uh oh, foreign key reference checking in MySQL, I can''t believe it man ;-) you could try the before_destroy hook in certification, to delete the associated object: class Certification before_destroy { |certification| certification.bio.destroy } Jens -- Jens Krämer jk-UayuY8ajoWPk1uMJSBkQmQ@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On Tue, Aug 29, 2006 at 02:09:55PM +0200, Jens Kraemer wrote:> > On Tue, Aug 29, 2006 at 01:50:19PM +0200, Dmitry Hazin wrote: > > > > I have 2 tables with association has_one (certification has_one bio) > > when I call @object.destroy (@object is certification object) I get the > > following error: > > > > Mysql::Error: #23000Cannot delete or update a parent row: a foreign key > > constraint fails (`project/certification`, CONSTRAINT > > `FKD99554BA1D0B0B41` FOREIGN KEY (`_key`) REFERENCES `bio` (`_key`)): > > DELETE FROM bio > > WHERE _key = 2 > > > > But when I write: > > > > ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 0" > > @object.destroy > > ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 1" > > > > it works fine. > > > > How can I delete associated row without disabling foreign key checks? > > uh oh, foreign key reference checking in MySQL, I can''t believe it man ;-) > > you could try the before_destroy hook in certification, to delete the > associated object: > > class Certification > before_destroy { |certification| certification.bio.destroy }I just re-read your mail - the above won''t work either. Seems your foreign key constraint is the other way around - it says certification depends on bio, so you should say that in Rails, too, i.e., place the :dependent setting at the other end of the relationship. That''s why one should just place the model logic just into *one* place... Jens -- Jens Krämer jk-UayuY8ajoWPk1uMJSBkQmQ@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Hrm.. can''t get it working :(
what d u mean "place the :dependent setting at the other end of the
relationship"?
My classes look like following:
class Certification < ActiveRecord::Base
# before_destroy { |certification| certification.bio.destroy }
has_one :bio,
:class_name => "Bio",
:foreign_key => "_key",
:dependent => :destroy
class Bio < ActiveRecord::Base
belongs_to :certification,
:class_name => "Certification",
:foreign_key => "_key"
do u mean addin :dependent => :destroy to the Bio class or what? ( I
tried it but without any success:) )
Thanks
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
Dmitry,
Just a thought ''convention over configuration''....
Can you not just do a straight forward rails relationship using
migrations?
class AddTables < ActiveRecord::Migration
def self.up
create_table :certifications, :force => true do |t|
t.column :name, :string
t.column :created_on, :date
t.column etc...
end
create_table :bios, :force => true do |t|
t.column :certification_id, :integer
t.column :name, :string
t.column :created_on, :date
t.column etc...
end
end
def self.down
drop_table :certifications
drop_table :bios
end
end
Then in your models you should only need;
class Certification < ActiveRecord::Base
has_one :bio, :dependent => :destroy
class Bio < ActiveRecord::Base
belongs_to :certification
And let rails take care of the rest?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
On Tue, Aug 29, 2006 at 02:37:45PM +0200, Dmitry Hazin wrote:> > Hrm.. can''t get it working :( > > what d u mean "place the :dependent setting at the other end of the > relationship"?I was confused by your naming of columns and the resulting mysql error.> My classes look like following: > > class Certification < ActiveRecord::Base > # before_destroy { |certification| certification.bio.destroy } > has_one :bio, > :class_name => "Bio", > :foreign_key => "_key", > :dependent => :destroy > > > class Bio < ActiveRecord::Base > belongs_to :certification, > :class_name => "Certification", > :foreign_key => "_key"what do your tables look like ? From these classes, you should have a ''_key'' column in the ''bio'' table, referencing ''certification._key''. where in certification it''s the primary key, and in bio it''s a foreign key pointing to the certification this bio belongs to. But from this message:> > Mysql::Error: #23000Cannot delete or update a parent row: a foreign > > key > > constraint fails (`project/certification`, CONSTRAINT > > `FKD99554BA1D0B0B41` FOREIGN KEY (`_key`) REFERENCES `bio` > > (`_key`)): > > DELETE FROM bio > > WHERE _key = 2it looks as if there''s a ''_key'' column in the certification table referencing the ''bio'' row that should get deleted. Is it possible you specified the constraint in the opposite direction as intended ? on a sidenote, that column naming doesn''t make sorting this out any easier ... Jens -- Jens Krämer jk-UayuY8ajoWPk1uMJSBkQmQ@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Actually my tables look like in the following dump below. I think maybe I get this error because bio has other foreign keys, but I just need to delete the bio entry without deleting records from person_profile and user_account (as I understand now) And no, I can''t accept that ''convention over configuration'', because I have ready database structure and can''t change it CREATE TABLE `bio` ( `_key` bigint(20) NOT NULL auto_increment, `timestamp` datetime NOT NULL, `approval_status` varchar(255) NOT NULL, `approval_time` datetime default NULL, `comment1` text, `comment2` text, `creation_time` datetime default NULL, `person_profile_key` bigint(20) NOT NULL, `user_account_key` bigint(20) default NULL, `tags` text, PRIMARY KEY (`_key`), KEY `FK17D08EF7EEE54` (`user_account_key`), KEY `FK17D08DB6B9ECC` (`person_profile_key`), CONSTRAINT `FK17D08DB6B9ECC` FOREIGN KEY (`person_profile_key`) REFERENCES `person_profile` (`_key`), CONSTRAINT `FK17D08EF7EEE54` FOREIGN KEY (`user_account_key`) REFERENCES `user_account` (`_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Foreign keys for table bio # ALTER TABLE `bio` ADD FOREIGN KEY (`person_profile_key`) REFERENCES `person_profile` (`_key`), ADD FOREIGN KEY (`user_account_key`) REFERENCES `user_account` (`_key`); CREATE TABLE `certification` ( `_key` bigint(20) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`_key`), KEY `FKD99554BA1D0B0B41` (`_key`), CONSTRAINT `FKD99554BA1D0B0B41` FOREIGN KEY (`_key`) REFERENCES `bio` (`_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Foreign keys for table certification # ALTER TABLE `certification` ADD FOREIGN KEY (`_key`) REFERENCES `bio` (`_key`); -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---