49

I'm using Rails 4.1 and Postgresql (with PG gem) as my database. I have a very stand many to many association from companies to provinces with a join table called regions. Now obviously the regions table has no primary key cause I used { :id => false }. But when I try to use depending destroy or just simply calling destroy on the region object it self I get this error:

 ERROR:  zero-length delimited identifier at or near """"
 LINE 1: DELETE FROM "regions" WHERE "regions"."" = $1

I know the problem is caused due to the lack of a primary key for the regions table. And oddly if I add the primary key back to the table destroy works fine and no error. However, if I remove the primary key from the table the error comes back. I know this has something to do with the postgres but I've no idea how to solve this without having to add a primary key column to my regions table.

Here is the actual query

[DEBUG] [AdminUser Load (0.4ms)  SELECT  "admin_users".* FROM "admin_users"  WHERE "admin_users"."id" = 1  ORDER BY "admin_users"."id" ASC LIMIT 1] (pid:29655)
[DEBUG] [Province Load (0.2ms)  SELECT  "provinces".* FROM "provinces"  WHERE "provinces"."id" = $1 LIMIT 1  [["id", 5]]] (pid:29655)
[DEBUG] [ (0.1ms)  BEGIN] (pid:29655)
[DEBUG] [Region Load (0.3ms)  SELECT "regions".* FROM "regions"  WHERE "regions"."province_id" = $1  [["province_id", 5]]] (pid:29655)
[ERROR] [PG::SyntaxError: ERROR:  zero-length delimited identifier at or near """"
LINE 1: DELETE FROM "regions" WHERE "regions"."" = $1
Steve
  • 561
  • 2
  • 5
  • 7
  • You'll need to show the actual SQL query that Rails generated; you can get it from PostgreSQL's log files or from the Rails logs. – Craig Ringer Apr 19 '14 at 06:38
  • 4
    This: `WHERE "regions"."" = $1` is invalid SQL and that is not Postgres' fault - it's Rails which creates the invalid SQL. My guess is that Rails generates the `where` condition based on the columns that make up the primary key. As there are not such columns, it generated invalid SQL (essentially using an empty column name) –  Apr 19 '14 at 17:25
  • 1
    Yes You're right. self.primary_key = :province_id solves the problem but it's not the cleanest.... – Steve Apr 21 '14 at 14:42
  • I'm experiencing the same thing but my join table does have a primary key so I don't think that is affecting the outcome – daslicious Jul 22 '15 at 22:49
  • your model might have custom primary key. add `self.primary_key = :custom_key` in model file. – Rahul Rajput Mar 16 '23 at 20:49

5 Answers5

50

You want single quotes not double quotes around empty strings, double quotes make delimited identifiers, and "" isn't a meaningful identifier.

try:

WHERE 'regions'.'' = $1

or at least:

WHERE "regions".'' = $1
A-S
  • 2,547
  • 2
  • 27
  • 37
23

Try setting dependent: :delete_all instead.

Example (Not entirely sure how you have your many-to-many relationships setup).

# models/region.rb
...
has_many :provinces_regions, dependent: :delete_all
has_many :provinces, through: :provinces_regions
...

:destroy/:destroy_all will remove associated objects by calling their destroy method, and thus callbacks (:before_destroy, :after_destroy, etc.)

:delete/:delete_all will remove associated objects without calling their destroy method.

Leonid Shevtsov
  • 14,024
  • 9
  • 51
  • 82
Alex Carroll
  • 231
  • 2
  • 3
  • with a `has_many [...] through` definition this works. Did not find reference ot this in the rails guides... – Jerome Jan 02 '15 at 09:54
  • Before resorting to this, please try my answer below. I feel not running callbacks only circumvents the problem. – RWDJ Jul 29 '19 at 15:57
12

Based on Alex Carol's answer, but completely different solution.

If someone did:

# models/region.rb
...
has_many :provinces_regions, dependent: :destroy_all
has_many :provinces, through: :provinces_regions
...

and are getting this issue, then I would guess you probably added id: false like this:

create_table :provinces_regions, id: false do |t|
  t.belongs_to :regions, index: true
  t.belongs_to :provinces, index: true
end

If the above is true, then Rails is trying to use the ids to run callbacks before destroying. So, give it an id:

create_table :provinces_regions do |t|
  t.belongs_to :regions, index: true
  t.belongs_to :provinces, index: true
end

And thus you can continue to use dependent: :destroy_all and use callbacks and other features that require the id.

At this stage, you need to make a new migration to correct it. So, as Hussein refers, you should make the migration:

add_column :provinces_regions, :id, :primary_key

See here for more details.

RWDJ
  • 734
  • 8
  • 13
  • 2
    This fixed my problem. I had to `add_column :table_name, :id, :primary_key` on the joint table to be able to use `destroy_all`. More details here: https://github.com/rails/rails/issues/25347 – Hussein El Motayam Oct 21 '19 at 14:31
  • Thanks, I feel stupid. I initially created a table for habtm and decided to go with `has many through` in the process thus forgetting to add an id – Dmitry Staver Jun 22 '21 at 11:20
2

Add to model file:

self.primary_key = :id
shilovk
  • 11,718
  • 17
  • 75
  • 74
1

As none of the answers here worked for me, I thought I'd include my custom fix. Not sure what the translation for your use case is, but I hope the gist of my strategy is clear.

I, too, am using a custom primary key (uuid). In order to delete the record on my join table, I skipped active record for the actual delete call and used SQL instead.

uid = BucketListPhoto.find_by(bucket_list_id: 12, photo_id: 4).uid

deleted_uids = ActiveRecord::Base.connection.execute(
    "DELETE FROM bucket_list_photos WHERE uid='#{uid}' RETURNING uid"
).to_a.map { |record| record['uid'] }

I overwrote BucketListPhoto#destroy to use this.

I tried to overwrite BuckerListPhoto::ActiveRecord::Query#destroy_all as well but wasn't able to successfully pass an array of uids (to delete many with a single query).

max pleaner
  • 26,189
  • 9
  • 66
  • 118