Rails: delete_all raises PG::NotNullViolation

Question

I am importing a CSV with a few hundred rows into my rails database.

Occasionally the user wants to force overwrite the data so I figured that it would be best to destroy all the data and start fresh.

something like:

account.catalog_listings.delete_all if should_refresh

CSV.foreach(file, options) do |row|
  account.catalog_listings.create!({...rowstuff})

problem is that delete_all line is raising the PG error

ActiveRecord::StatementInvalid (PG::NotNullViolation: ERROR:  null value in column "account_id" violates not-null constraint
DETAIL:  Failing row contains (1, null, ... ... ).
: UPDATE "catalog_listings" SET "account_id" = NULL WHERE "catalog_listings"."account_id" = $1):
  app/models/catalog_listing.rb:41:in `import_catalog_listings'
  app/controllers/accounts_controller.rb:20:in `catalog'

I DO have a null: false on a couple foreign key fields but i cannot figure out why delete_all is trying to remove the foreign key instead of removing the whole record?

UPDATE - everything works when I change:

account.catalog_listings.delete_all if should_refresh

to:

account.catalog_listings.destroy_all if should_refresh

except that destroy goes through each item and deletes one-by-one:

  SQL (0.1ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 957]]
  SQL (0.1ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 958]]
  SQL (0.1ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 959]]
  SQL (0.1ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 960]]
  SQL (0.1ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 961]]
  SQL (0.1ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 962]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 963]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 964]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 965]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 966]]
  SQL (0.3ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 967]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 968]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 969]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 970]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 971]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 972]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 973]]
  SQL (0.3ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 974]]
  SQL (0.2ms)  DELETE FROM "catalog_listings" WHERE "catalog_listings"."id" = $1  [["id", 975]]

not cool... anyone know a better way to do this?

no. I have `belongs_to :account` and `validates_presence_of `:account_id` — Blair Anderson, Jun 20 '17 at 23:40
that is causing the issue, and mostly it is at the db level...you must have set constraint during migration, right? — Md. Farhan Memon, Jun 21 '17 at 04:19
migration was `t.integer account_id, null: false` and nothing else. Question really is why is rails removing the account_id instead of deleting the object. — Blair Anderson, Jun 21 '17 at 16:00

score 4 · Answer 1 · answered Sep 01 '17 at 00:01

4

Try adding dependent: :destroy in the catalog_listings association on your Account model.

https://apidock.com/rails/ActiveRecord/Associations/CollectionProxy/delete_all

answered Sep 01 '17 at 00:01

Alfonso Embid-Desmet

3,561
3
32
45

Rails: delete_all raises PG::NotNullViolation

1 Answers1