While working on a portion of my book, I found myself in
./script/console and was seeing some weird issues when I would use
has_many and belongs_to.
Letâs take two simple models.
<pre>
class Order < ActiveRecord::Base
belongs_to :customer
end
class Customer < ActiveRecord::Base
has_many :orders
end
</pre>
</code>
After a few test records...
<code>
<pre>
test_dev=# SELECT * FROM customers;
id | name
----+----------------
1 | Robby
2 | Nigel
3 | Linus
(3 rows)
test_dev=# SELECT * FROM orders;
id | customer_id | amount
----+-------------+--------
1 | 1 | 12.00
2 | 3 | 12.00
(2 rows)
</pre>
</code>
Nothing completely crazy going on, right?
<typo:code lang="ruby">
Loading development environment.
>> Customer.destroy(3)
=> {"name"=>"Linus", "id"=>"3"}
>>
=# SELECT * FROM orders;
id | customer_id | amount
----+-------------+--------
1 | 1 | 12.00
3 | 3 | 12.00
(2 rows)
Wait a minute! I just deleted a customer with an id of 3!
So, what is wrong with this scenario? Can you think of any potential
problems that could occur from data like this? The record has a
customer_id for a customer that does not exist. This is why we have
relational databases in the first place, right? :-)
Here is something that I learned today that I was unaware of. Active
Record allows you to pass the has_method declaration the option
:dependent.
class Customer < ActiveRecord::Base
has_many :orders, :dependent => true
end
What is this option? Well, according to the AR documentation,
â:dependent - if set to true all the associated object are destroyed
alongside this object. May not be set if :exclusively_dependent is
also set.â
In a nutshell, this works like ON DELETE CASCADE does in PostgreSQL.
So, it will through and delete the orders associated with the
customer that I was attempting to destroy.
Up until today, I hadnât broken myself out of the habit of using the
built-in constraints/triggers of PostgreSQL. So, as soon as I did, this
issue came up and I learned about :dependent.
test_dev# \d orders
Table "public.orders"
Column | Type | Modifiers
-------------+---------------+--------------------------------------------------------
id | integer | not null default nextval('public.orders_id_seq'::text)
customer_id | integer |
amount | numeric(10,2) |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
test_dev=# ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE
RobbyOnRails:~/Programming/footest robbyrussell$ ./script/console
Loading development environment.
>> cust = Customer.create(:name => 'Jim')
=> #<Customer:0x275373c @new_record_before_save=true, @new_record=false, @attributes={"name"=>"Jim", "id"=>5}, @errors=#<ActiveRecord::Errors:0x274fa88 @base=#<Customer:0x275373c ...>, @errors={}>>
>> cust.orders.create(:amount => '25.00')
=> #<Order:0x274991c @new_record=false, @attributes={"id"=>4, "amount"=>"25.00", "customer_id"=>5}, @errors=#<ActiveRecord::Errors:0x2746dfc @base=#<Order:0x274991c ...>, @errors={}>>
>>
test_dev=# SELECT * FROM orders;
id | customer_id | amount
----+-------------+--------
1 | 1 | 12.00
3 | 4 | 29.00
4 | 5 | 25.00
(3 rows)
As you can see, I put myself into the hands of Active Record when I ran
the DROP CONSTRAINT. Then I tried running the code at the top of the
post⌠and it didnât work.
According to the docs, if you use :dependent => true, it should delete
the foreign table records. If not, it should set the value of the
foriegn key field to NULL in the foreign table.
Basically, perform these two SQL queries:
UPDATE orders SET customer_id = NULL WHERE customer_id = 17;
DELETE FROM customers WHERE id = 17;
Then, the records are still in the database for those orders, but the
customer is deleted. There are arguments for and against doing this sort
of thing⌠but the ability to have the option is always nice. In any
event, Active Record would not run the first query,it was just deleting
from the customers table. Without my constraint, no error would be
returned from PostgreSQL and I started to get some bad data.
Imagine showing a list of orders and trying to display the customer name
associated with an order that has no linking customer. Doh! If Active
Record sets the customer_id to NULL we can at least have some logic
to work with this without having to run some fun SQL queries to figure
out which orders do and dont have customers. (we want our applications
to have clean data!)
Anyhow⌠Was this a bug? Should Active Record know to update the
records to NULL in this case? I figured that is should be handling
this task, especially since it was handling cascading deletes when you
passed :dependent => true.
However, I didnât want to prematurely post a bug report, so I began
asking around on #rubyonrails (irc.freenode.net). People made a
bunch of suggestions as to how to work around it. I could add a
before_destroy method in my model, track the bug down, (re)add an
ON DELETE trigger to my table (hah), etc. So, I decided that I would
see if I could track down what happens when has_many is used for a model
upon #destroy.
After a while of digging and making some tests, I posted a
patch and a bug
report. (please disregard my
first patch⌠it did not work! heh)
Now that I figured this out, I am going to happy add my constraints back
to my tables and go back to playing around. This reminded me of a post I
had a few months ago when I mentioned that I thought it was best to put
some constraints and logic in the database. I also agree that
constraints should be put in the abstraction layer, but we cannot always
put all faith in our code either. A few levels of checks doesnât hurt.
:-)
This was a fun little riddle that I took on today. The moral of the
story? If you have the ability to use the builtin referential integrity
features of PostgreSQL and those other databases, it might be a good
idea to do so. Things get overlooked, people login to the database in
many ways, and from different programs.
UPDATE: DHH responded to this post and provided a
link which discusses
Application
Database versus
Integrated
Database
It should be noted that there is an important distinction between the
two methods. When I said, âThings get overlooked, people login to the
database in many ways, and from different programsâ I was basically
describing Integration Database. However, I was also thinking of the
possibility of someone opening up their MySQL or PostgreSQL GUI and
manually removing a record in plain SQL. According to Application
Database, the moment that you do that, you basically break this model
and cannot expect your application to be fully responsible for the
problems that may or may not occur. At this point, you would need to
look at your application in terms of Integration Database. Please do
correct me if I am wrong on this. :-)
However, with this scenario, my first attempt to move to relying on AR
had a minor hiccup, but it was an easy enough fix.
By performing the following command, I am moving towards an Integration
Database pattern and that should be recognized when taking this into
consideration.
ALTER TABLE orders ADD CONSTRAINT orders_customers_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id) MATCH FULL;
Okay, back to work!
Once again. Use constraints! (if you can)
⌠and thanks to DHH for providing the
link and motivating me to make a note of this in my entry.
