Here is a silly little thing you can do to your columns for … some reason. Create a constraint on a table full of data that would not pass the check for the constraint. This is not something that should be done if it can be avoided. You want your constraints to define a rule and the data in your tables to enforce those rules. If you do this eventually, you should work it out so that you get the data up to data as soon as possible… before it slips from your attention.
One usage i can think of for this is to quickly enforce a constraint on a column without having to update existing values. One reason would be that the table has huge amount of data that would need to be updated for constraint to work and you want the constraint enabled as soon as possible so that some business rule is enforced. Table data can then be updated later during a batch job in a safe time windows (non working hours for example) or updated during longer period of time on data fetch through application that uses the data.
To do this Oracle gives us few neat keywords for constraint creation. First is “DISABLE” that allows us to create a disabled constraint. Second is “NOVALIDATE” that skips validation of existing data in the table when enabling the constraint. This will just enable it for all future data that is inserted or updated. Without “NOVALIDATE” Oracle would test each row against the constraint and return an error if any row fails the validation.
create table nullchk ( i number); insert into nullchk values (null); ALTER TABLE nullchk MODIFY i CONSTRAINT myconst NOT NULL DISABLE; ALTER TABLE nullchk MODIFY CONSTRAINT myconst ENABLE NOVALIDATE;
It can also be used with “RELY” keyword which does not enforce the constraint but only keeps it as a rule meaning that it will not do the actual check for the rule on the any incoming data. It will RELY on you and your application to provide already good data that would pass the validation. This is really useful for many reasons i don’t have time now to write but will in future :). Stay Tuned.