Rails 4 relationships and Postgres foreign key constraints

Question

I am working on an inventory tracking app in Rails 4 with the intent of keeping a log of the transactions made by each user. I have set up three models with the following relationships:

Item: has_many :transactions

User: has_many :transactions

Transaction: belongs_to :user, belongs_to :item

The User ID and Item ID are foreign keys in the Transaction table to associate a user and item with each transaction.

The issue arises when I try to delete an item, or user. I want the transactions to remain, so I have not enabled dependent destroy, but Postgres foreign key constraints do not allow the deletion, with the following error:

ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR: update or delete on table "items" violates foreign key constraint "fk_rails_37b3ea4e18" on table "transactions"

Is there a better way to set up this relationship that will allow transactions to be associated to a user and item, but still exist after the user or item has been deleted?

The logic you're trying to impose on the database structure is fishy. Let's just say you'v deleted both the item and the user of the same transaction, but still somehow maintain the transaction record holding both their foreign keys. How would you know which item and which user are defined by this transaction now that they are deleted. I'd suggest you have a "active" boolean column in both your items and users table, and just flag them whenever you delete. This will keep all historical information in your database for future references. — moeabdol
– moeabdol, Commented Oct 1, 2015 at 14:25
on what would you want the transaction to point after you've deleted the user or the item? — davidrac
– davidrac, Commented Oct 1, 2015 at 14:25

Tom Fast · Accepted Answer · 2015-10-01 14:43:37Z

2

You need to remove the foreign key constraint.

You could also keep the foreign key constraints and use "soft deletes" which could set a Boolean field to true once a user is set as deleted.

The fact remains: if you're using the foreign key constraints, then you'll need to do cascading deletes.

answered Oct 1, 2015 at 14:43

Tom Fast

1,15810 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails 4 relationships and Postgres foreign key constraints

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related