Make NOT NULL constraint apply only to new rows

Question

I will try to present the situation as a minimal example:

Say we have a table of tickets, defined as follows:

CREATE TABLE ticket_id_list (
   ticket_id  NUMBER,
   issued_to  VARCHAR2(100),
   CONSTRAINT ticket_id_list_pk PRIMARY KEY (ticket_id)
);

The system is used for some time and data is added to the table:

INSERT INTO ticket_id_list (ticket_id, issued_to)
   VALUES (1, 'Arthur');
INSERT INTO ticket_id_list (ticket_id, issued_to)
   VALUES (2, 'Ford');

Later, following requirement pops up:

We need to store a reference number to some other thing in the ticket table this point onwards. The reference needs to be non null. But old records must have NULL values.

(Stupid as though this sound, this is a real requirement.)

Now if we do this:

ALTER TABLE ticket_id_list
   ADD ref_no VARCHAR2(6) NOT NULL;

the constraint will be violated immediately and we will get: ORA-01758: table must be empty to add mandatory (NOT NULL) column.

We can of course add a check in the business logic, but that is cumbersome. And because of unexplainable reasons, we cannot use a default value. Is there a way to add a NOT NULL contraint that is only applicable to new records?

Answer 1

You could add a check constraint where values after a certain date cannot be null; something like:

alter table ticket_id_list
  add constraint nul_ref
  check (ticket_id < 123456 or ref is not null);

This does presume that ticket_id's only go up over time, if you happen to have a date field in your table it maybe clearer to use that.

Answer 2

I found a neat trick: NOVALIDATE.

Add a nullable column:

ALTER TABLE ticket_id_list
   ADD ref_no VARCHAR2(6);

And then add a table contraint, but make sure to specify NOVALIDATE:

ALTER TABLE ticket_id_list
   ADD CONSTRAINT new_ref_nonull CHECK(ref_no IS NOT NULL) NOVALIDATE;

Now let's check it:

INSERT INTO ticket_id_list (ticket_id, issued_to, ref_no)
   VALUES (3, 'Zaphod', '2A4252');
1 row inserted

INSERT INTO ticket_id_list (ticket_id, issued_to, ref_no)
   VALUES (4, 'Marvin', NULL);
ORA-02290: check constraint (NEW_REF_NONULL) violated

Just as expected.

Edit: NOVALIDATE will work only if you need not to update the old rows. If you updated the old rows the constraint will fail. For this instance though, this is not an issue.

Answer 3

You will have to consider this requirement as a business logic since in you case it is.

Then implement this business logic in a trigger.

CREATE TRIGGER t_ticket_id_list
    BEFORE INSERT ON ticket_id_list
    FOR EACH ROW
      BEGIN
         IF (:NEW is null) THEN
            RAISE_APPLICATION_ERROR(-20001, 'Cannot Insert 1 in this table');
         END IF;
      END;

Note - Same can be done for SQL Server as well MSDN Create trigger documentations