Type and statu of oracle constraint

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.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to Type and statu of oracle constraint

  1. neworacledba says:

    oracle constraints have been explained very clearly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: