Has_one and :dependent => :destroy

Rails
1

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

2

On Tue, Aug 29, 2006 at 01:50:19PM +0200, Dmitry H. wrote:

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
:wink:

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
[email protected]

3

On Tue, Aug 29, 2006 at 02:09:55PM +0200, Jens K. wrote:

DELETE FROM bio
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 :wink:

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
[email protected]

4

Hrm… can’t get it working :frowning:

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

5

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?

6

On Tue, Aug 29, 2006 at 02:37:45PM +0200, Dmitry H. wrote:

Hrm… can’t get it working :frowning:

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.

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 = 2

it 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
[email protected]

7

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