I am trying to add a new column into an existing table, which has to be NOT NULL. It is a foreign key referring to ID of other table and I can't use default value. I did it in couple of steps:

1. add the new column to the table with NULL constraint
2. run update scripts to update the new column with correct values for all rows
3. add foreign key constraint
4. change the new column to NOT NULL

The issue is that I want to follow a rule we have, that the DDL and DML scripts are separated (for couple of reasons). I can't figure out how to do this. Is there a way how to add a NOT NULL column into an existing table in a way that the DDL and DML would be separated?

CodePudding user response：

Do step 1 and 3 together (if you do not then you risk adding values in step 2 that will not match the constraint).

ALTER TABLE table_name
  ADD column_name NUMBER(8,0)
      CONSTRAINT table_name__column_name__fk REFERENCES other_table(other_column);

You can also do step 4 at the same time if you make the NOT NULL constraint DEFERRABLE:

ALTER TABLE table_name
  ADD column_name NUMBER(8,0)
      CONSTRAINT table_name__column_name__nn NOT NULL DEFERRABLE
      CONSTRAINT table_name__column_name__fk REFERENCES other_table(other_column);

Then update the data. For example, you can use a MERGE statement to update from a sub-query of pre-generated values:

MERGE INTO table_name dst
USING (
  SELECT 1 AS id, 2 AS new_column_name FROM DUAL UNION ALL
  SELECT 2, 2 FROM DUAL UNION ALL
  SELECT 3, 1 FROM DUAL UNION ALL
  SELECT 4, 4 FROM DUAL
) src
ON (dst.id = src.id)
WHEN MATCHED THEN
  UPDATE
  SET column_name = src.new_column_name;

Then:

COMMIT;

Which will enforce any DEFERRABLE NOT NULL constraints.

If you did not make the constraint DEFERRABLE then change the column to NOT NULL:

ALTER TABLE table_name
  MODIFY column_name NUMBER(8,0) NOT NULL;

db<>fiddle (not deferrable) (deferrable)