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?

Autoclave

Do you have any before_destroy filter in your model? – Aggressive

no. I have belongs_to :account and validates_presence_of :account_id` – Autoclave

that is causing the issue, and mostly it is at the db level...you must have set constraint during migration, right? – Cordie

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. – Autoclave

Answer 1

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

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

Halverson