What are Oracle Constraints?Oracle constraints are means in the process of defining some conditions about the database that must remain true while inputting/modifying/deleting data in the database.
Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity). You may also use them to enforce business rules (with some magination).
These constraints can be attribute-based (column), tuple-based (table), key based and referential integrity based.
As Oracle views are always dynamically generated from their base tables, so the view can not contain constraints.
If there is a violation of the constraints caused by some actions performed on the database, then the constraints aborts the action accordingly. The Oracle implementation of constraints differs from the SQL implementation of these constraints.
More on Oracle constraints:
The basic structure of an Oracle constraint is defined as:
The CONSTRAINT keyword is followed by a unique constraint name and then the constraint definition. The constraint name is used to manipulate the constraint once the table has been created.
In Oracle, constraints can be defined at the column or table level. An example of defining constraints at table level may be:
CREATE TABLE STUDENT (
We have now created our table with constraints. In this table, in the first attribute definition, after the CONSTRAINT keyword, S_ID is the name of the attribute on which it has to applied and “CHECK” is the type of constraint followed by the definition of that constraint to be followed for this table.
Column level constraints go directly after the column definition to which they refer and the table level constraints go after the last column definition.
CREATE TABLE CLASS (
Table level Oracle constraints are used for compound foreign and prime key definitions.
In the table given above, table level constraints could also have been placed as column definitions.
In a table constraint, you may omit the CONSTRAINT keyword and constraint name if you wish. But if you omit the constraint name then you will have no easy way of enabling / disabling the constraint without deleting the table and rebuilding it.
Oracle does give default names to constraints not explicitly named. This can be checked and verified by selecting from the USER_CONSTRAINTS data dictionary view.
Oracle supports the following constraints on tables and views:
Our two example tables do have some rules which need enforcing.
Mainly, both tables have a primary key constraint so that the database doesn't allow replication of data.
Similarly, the Section ID needs to be linked to each book to identify which library section it belongs to and this supports foreign key.
We also want to specify through Oracle constraints which columns must be filled in and which columns have default values for other attributes.
If we wish we can introduce cascading validation and some constraint violation logging to our tables.
CREATE TABLE AUDIT (
CREATE TABLE SECTION (
CREATE TABLE BOOK (
Oracle does not allow us to delete a section which had books assigned to it as this breaks integrity rules.
If we wanted to get rid of all the book records assigned to a particular section when that section was deleted we could implement a DELETE CASCADE.
The delete cascade operates across a foreign key link and removes all child records associated with a parent record.
ALTER TABLE BOOK ENABLE CONSTRAINT B_AUTH
The above statements demonstrate disabling and enabling a constraint: Note that if, between disabling a constraint and re enabling it, data was entered to the table that included NULL values in the AUTHOR column, then you wouldn't be able to re enable the constraint.
This is because the existing data would break the constraint integrity. You could update the column to replace NULL values with some default and then re enable the constraint.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.