sql - Make NOT NULL constraint apply only to new rows -

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

Say that We have a ticket table, defined as:

  Make ticket ticket_ID_list (ticket_ID number, issued VARCHAR2 (100), contract ticket_ID_list_pack primary key (ticket_ID));  

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

  ticket_id_list INSERT (ticket_id, issued_to) VALUES (1, 'Arthur'); INSERT (ticket_ID, iss_a) value (2, 'Ford') in ticket_ID_list;  

Later, the following requirements have been fashioned:

We need to place a reference number in the ticket table in the second part of this table. This reference should be zero tap but the old record should NULL value.

(As it sounds, this is a real need.)

Now if we do this:

  Alternate table Ticket_id_list ADD ref_no VARCHAR2 (6) null;  

This obstacle will be immediately violated and we will get: ORA-01758: The table must be empty to add the mandatory (not the null) column .

We can definitely add a check to business logic, but it is cumbersome and due to unexpected reasons, we can not use a default value. Does not contain a null that only applies to new records?

You can add a check barrier, where a certain date Later values ​​can not be empty; Something like this:

  Add changes to the Ticket_ID_list barrier nul_ref check (ticket_id <123456 or ref is not empty);  

It seems that ticket_id only goes up over time, if your table has a date field, it will become clear to use it.