Constraints

SQL constraints are used to specify rules for the data in a table. These are used to limit the type of data that can go into a table. We use constraints for

* Uniqueness of values in certain column.
* Matching of column values across columns (primary key and foreign key)
* Values in certain columns with specified range.
* This ensures the accuracy and reliability of the data .

Types of constraints:

Constraints could be column level or table level.
Column level:-
If the constraints are defined with the column definition, it is called as column level constraint. Column level constraint can be applied to any one column at a time.

Table level:-
A constraint given at the table level is called as Table Constraint. It may refer to more than one column of the table. A typical example is PRIMARY KEY constraint that is used to define composite primary key. A column level constraint can be given even at the table level, but a constraint that deals with more than one column must be given only at the table level.

Commonly used Constraints:
Following are commonly used constraints available in SQL.
NOT NULL Constraint: Ensures that a column cannot have NULL value.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures hat all values in a column satisfy certain conditions.

Not Null:

NOT NULL constraint restricts a column from having a NULL value.
Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column.
It enforces a column to contain a proper value.
One important point to note about NOT NULL constraint is that it cannot be defined at table level

Syntax:

SQL> CREATE TABLE CUSTOMERS(
Tname dataype     NOT NULL,
Tname dataype     NOT NULL,
Tname dataype    NOT NULL,
Tname dataype);

Example:

SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25));

To add not null constraint on existing tables column.
Syntax:

SQL> ALTER TABLE table_name MODIFY
column_name NOT NULL;

Example:

SQL> alter table test modify Age not null;
UNIQUE Constraint:

The UNIQUE Constraint prevents two records from having identical values in a particular
column. In the CUSTOMERS table, for example, you might want to prevent two or more
people from having identical age.

Syntax:
SQL> CREATE TABLE CUSTOMERS(
Tname datatype        NOT NULL,
Tname datatype       NOT NULL,
Tname datatype          NOT NULL UNIQUE,
Tname datatype);

Example:

SQL> CREATE TABLE CUSTOMERS(
ID INT                                  NOT NULL,
NAME VARCHAR (20)      NOT NULL,
AGE INT                          NOT NULL UNIQUE,
ADDRESS CHAR (25) );
If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following:

SQL> ALTER TABLE CUSTOMERS MODIFY
          AGE INT NOT NULL UNIQUE;You can also use following syntax, which supports naming the constraint in multiple columns as well:

SQL> ALTER TABLE CUSTOMERS ADD CONSTRAINT conname UNIQUE(AGE, SALARY);
           DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

Syntax:
SQL> alter table table_name drop constraint constraint_name;

Example:
SQL> ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;

Primary key Constraint:

A primary key is a field in a table which uniquely identifies each row/record in a database table.
Primary keys must contain unique values.
A primary key column cannot have NULL values.
A table can have only one primary key.
primary key When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Create Primary Key:
syntax :
SQL> CREATE TABLE table_name (
Column_name dataype,
Column_name dataype,
Column_name dataype,
PRIMARY KEY (ID) );
Example:
SQL> CREATE TABLE CUSTOMERS(
ID INT       NOT NULL,
NAME VARCHAR (20)           NOT NULL,
AGE INT           NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Constraint cust_const PRIMARY KEY (ID) );
To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL

Syntax:
SQL> ALTER TABLE table_name ADD CONSTRAINT PK_dept PRIMARY KEY (column_name);
Example:
SQL> alter table customers add constriant  Ps_dept primary key (ID);

Delete Primary Key:
You can clear the primary key constraints from the table.

Syntax:
SQL> ALTER TABLE table_name DROP PRIMARY KEY

Example:
SQL> alter table dept drop primary key;
                               or
SQL> alter table dept drop constraint PK_dept ;

foreign key:

A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

syntax :
SQL> CREATE TABLE table_name (
Column_name dataype,
Column_name dataype,
Column_name dataype,
PRIMARY KEY (column_name) );

SQL> create table table_name (
Column_name datatype,
Column_name datatype,
Constraint cons_name foreign_key prefremces
pri_table_name (column_name);
Example:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Constraint cust_const PRIMARY KEY (ID) );
SQL> create table order_det (
order_id number(20),
order_name varchar2(20),
constraints for_ord foreign key(order_id) references cust(id))
Add foreign key on existing table.

Syntax:
SQL> alter table table_name add contraint
Const_name foreign key (column_name)
References pri_table_name(p_columname);
Example:
SQL> alter table oreder_details add constraint
Ord_forg foreing_key(order_id)
References cust(id);
DROP a FOREIGN KEY Constraint:
To drop a FOREIGN KEY constraint, use the following SQL:

Syntax:
SQL> ALTER TABLE table_name DROP FOREIGN KEY;

Example:
SQL> alter table order_det drop foreign key;

CHECK Constraint:

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Syntax:
SQL> CREATE TABLE table_name (
Cname datatype    NOT NULL,
Cname datatype    NOT NULL,
Cname datatype    NOT NULL CHECK (AGE >= 18));
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:

SQL> CREATE TABLE CUSTOMERS(
ID INT    NOT NULL,
NAME VARCHAR(20)    NOT NULL,
AGE INT NOT    NULL CHECK (AGE >= 18));
If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column

SQL> ALTER TABLE table_name MODIFY column_name NOT NULL CHECK (column_name >= 18 );You can also use following syntax, which supports
naming the constraint in multiple columns as well:

SQL> ALTER TABLE table_name ADD CONSTRAINT constraintname CHECK(colname >= 18);
To drop a CHECK constraint, use the following SQL.

Syntax:
SQL> ALTER TABLE table_name DROP CONSTRAINT const_name;

Example:
SQL> alter table cust drop constraint check_my;