What are constraints?

What are constraints?

Rating:

Constraints are rules for a database that limit the acceptable data values for a table. They are the optional schema objects that depend on a table. The existence of a table without any constraint is possible, but the existence of a constraint without any table is not possible. Constraints enforce the business rules in a database. Constraints can be created along with the table in the CREATE TABLE statement. Addition and deletion of constraints can be done in the ALTER TABLE statement.

The following types of constraints are available in Oracle 10g:

• NOT NULL: It enforces that a column, declared as not null, cannot have any NULL values. For example, if an employee's hire date is not known, then that employee may not be considered as a valid employee. If a protected column does not possess any value, then the INSERT and UPDATE statements on those columns will fail.


• UNIQUE: It ensures that columns protected by this constraint cannot have duplicate values.


• PRIMARY KEY: It is responsible for uniquely identifying a row in a table. A table can have only one PRIMARY KEY constraint. A PRIMARY KEY constraint completely includes both the NOT NULL and UNIQUE constraints. It is enforced with an index on all columns.
  
• FOREIGN KEY: It is also known as referential integrity constraint. It enforces that values referenced in one table are defined in another table. It establishes a parent-child or reference-dependent relationship between the two tables. It cannot be created on columns of type CLOB, NCLOB, BLOB, LONG, LONG RAW, and TIMESTAMP WITH TIMEZONE.


• CHECK: It enforces that columns must meet a specific condition that is evaluated to a Boolean value. If the value evaluates to false, then the database will raise an exception, and not allow the INSERT and UPDATE statements to operate on columns.

Rating: