1. Type of constraint
C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)
2.Statu of constraint
1) Enable Validate:
a) check current data. If invalidate, the constraint cannot be created or enabled. b) the constraint is enabled c) check new inserted or updated data. It guarantee that all data is and will comply with the constraint.
2)Enable Novalidate:
a) do NOT check current data. b) the constraint is enabled c) check new inserted or updated data. It ensures thata all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock.
3)Disalbe Validate:
a) check the current data. b) the constraint is disabled c) do not allow insert, update and delete on the table. This will disable the constraint and drop the index on the constraint, but keep the constraint valid. All insert, update, delete are disallowed except the exchange partition and SQL*Load to this table. This fewture is most useful in data warehousing situations because it lets you load large amounts of data while also saving space by not having an index.
4)Disable Novalidate
a) the constraint do not take any effect. b) oracle makes no effort to maintain the constraint except keep the definition in dictionary.
Filed under: Data Management
