Term: CONSTRAINT

Definition:
The Oracle CONSTRAINT clause allows you to restrict the data that is entered into a column to ensure that it is valid or that it meets certain conditions. For example, a column containing a person's height should probably only accept positive values, but there is no data type that accepts only positive numbers. One way to solve this problem would be to create a constraint that prevents negative numbers from being stored in the column. Constraints can be written to validate any kind of data required. For instance, a constraint can restrict stored data only to values between 1 and 10, or it could specify that a value must contain at least 5 characters, or that it may not be empty.

Properly written constraints give you as much control over the data in your tables as you need. If an attempt is made to store data in a column that violates a constraint, an error (or exception) is raised.

Constraints can be defined when a table is first created via the CREATE TABLE statement, or after the table is already created by using the ALTER TABLE statement.

Check Constraint

A check constraint is the simplest and most common type of constraint. The check constraint allows you to set one or more conditions that the value in a column must meet before it is stored. For instance, to require that the cost of a product be a positive number, you could use this constraint:

CREATE TABLE products (

    product_id INTEGER,

    product_name TEXT,

    cost numeric CONSTRAINT positive_cost CHECK (cost > 0)

);

Notice in the example above that we've also given the constraint a name of "positive_cost". Giving constraints names helps identify what they do and makes your code more readable and maintainable.

You can also create constraints that include multiple columns and conditions, if you want. This example checks to make sure that the cost is above 0, and it also checks to make sure that the discounted cost is less than the regular cost:

CREATE TABLE products (

    product_id INTEGER,

    product_name TEXT,

    cost numeric CHECK (cost > 0),

    discounted_cost numeric CHECK (discounted_cost > 0), CHECK (cost > discounted_cost)

);

Foreign Key Constraint

A foreign key is a constraint between two tables in a database The foreign key identifies a column (or set of columns) in one table (the "referencing" table) that refers to set of columns in another table (the "referenced" table).

The referencing and referenced table may be the same table, that is, the foreign key refers back to the same table. Such a foreign key is known in ANSII SQL as a self-referencing or recursive foreign key.

A table may have multiple foreign keys, and each foreign key can (and often does) have a different referenced table. Each foreign key constraint is enforced independently by the database system.

Example Syntax:

Foreign keys are normally defined as part of the CREATE TABLE SQL statement:

CREATE TABLE Sales(

  S_Id int NOT NULL,

  SalesNo int NOT NULL,

  U_Id int,

  PRIMARY KEY (O_Id),

  CONSTRAINT fk_User_Sales FOREIGN KEY (U_Id)

REFERENCES Users(U_Id)

);

Foreign keys can, however, be added to an existing table using the ALTER TABLE command:

ALTER TABLE Sales

  ADD CONSTRAINT fk_User_Sales

  FOREIGN KEY (U_Id)

REFERENCES Users(U_Id);

Unique Key Constraint

The UNIQUE constraint ensures that all values in a specified column are distinct, that is, the column cannot contain two identical instances of any value. Attempting to insert a duplicate value into a UNIQUE column will violate the UNIQUE constraint and result in an error.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

Note that a PRIMARY KEY constraint automatically includes a UNIQUE constraint.

Example Syntax:

CREATE TABLE users

(user_id integer UNIQUE,

  Last_Name varchar (30),

  First_Name varchar(30));

Primary Key Constraint

A Primary Key is used to uniquely identify each row in a table. It acts to enforce row-level integrity of the table, meaning that it ensures that there are no duplicate records in a table. A primary key can be part of the record itself (such as Social Security Number) or it can be an independent field used only to identify the record itself. A primary key can also be composed of multiple fields on a table. When multiple fields are used as a primary key, it is referred to as a composite primary key.

Primary keys can be created when the table itself is created or added to an existing table structure using the ALTER TABLE statement.

Example Syntax:

CREATE TABLE users (

 UserID INTEGER PRIMARY KEY NOT NULL,

 First_Name varchar(20),

 Last_Name varchar(20),

 ZipCode varchar(10)

);

You don't have to specify the primary key at the same time you are creating the column. You may also add it at the end of the table, as shown in this example:

CREATE TABLE users (

 UserID INTEGER NOT NULL,

 First_Name varchar(20),

 Last_Name varchar(20),

 ZipCode varchar(10),

PRIMARY KEY(UserID)

);