How to add presence constraint to a table with already data in it?

[msdundar]

Hi,

I'm trying to do two things in a migration:

1. Add a new column to a table,
2. Then add a presence constraint to the newly added column.

class AddAdditionalFieldsToCountries < ActiveRecord::Migration[6.0]
  def change
    add_column :countries, :continent, :string
    add_presence_constraint :countries, :continent
  end
end

This table has already records in it. therefore my constraint throws an error:

PG::CheckViolation: ERROR:  check constraint "countries_continent_presence" is violated by some row

I believe that, there must be an option, such as, 'skip checking already existing data'. AFAIK there are some options in SQL such as WITH CHECK | WITH NOCHECK, however the documentation doesn't specify this case.

[nullobject]

Hi @msdundar, thanks for your comment.

What would be the use case for adding a constraint without enforcing it for existing data? Couldn't you run a migration to address the problems with your data before you add a constraint?

I don't think postgres even supports the NOCHECK option anymore (as of 9.4).

[msdundar]

I imagined to follow these steps:

Add column -> Add constraint -> Fix data issues

However, the correct order seems like this according to your comment:

Add column -> Fix data issues -> Add constraint

Which totally makes sense 🤦‍♂️. I got the point, thanks for your response 👍

[nullobject]

No worries 👍