Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.Following are commonly used constraints available in SQL. These constraints have already been discussed in SQL - RDBMS Concepts chapter but its worth to revise them at this point.
- NOT NULL Constraint: Ensures that a column cannot have NULL value.
- DEFAULT Constraint: Provides a default value for a column when none is specified.
- 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 that all values in a column satisfy certain conditions.
- INDEX: Use to create and retrieve data from the database very quickly.
Dropping Constraints:
Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command:
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.
Integrity Constraints:
Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.There are many types of integrity constraints that play a role in referential integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints mentioned above.
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, which may consist of single or multiple fields. 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 cannot have two records having the same value of that
field(s).
Note: You would use these concepts while creating database
tables.
Create Primary Key:
Here is the syntax to define ID
attribute as a primary key in a CUSTOMERS table.
CREATE
TABLE CUSTOMERS(
ID
INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE
INT NOT NULL,
ADDRESS
CHAR (25) ,
SALARY
DECIMAL (18, 2),
PRIMARY KEY (ID)
);
To create a PRIMARY KEY constraint
on the "ID" column when CUSTOMERS table already exists, use the
following SQL syntax:
ALTER
TABLE CUSTOMER ADD PRIMARY KEY (ID);
NOTE: If you use the ALTER TABLE statement to add a primary key,
the primary key column(s) must already have been declared to not contain NULL
values (when the table was first created).
For defining a PRIMARY KEY
constraint on multiple columns, use the following SQL syntax:
CREATE
TABLE CUSTOMERS(
ID
INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE
INT NOT NULL,
ADDRESS
CHAR (25) ,
SALARY
DECIMAL (18, 2),
PRIMARY KEY (ID, NAME)
);
To create a PRIMARY KEY constraint
on the "ID" and "NAMES" columns when CUSTOMERS table
already exists, use the following SQL syntax:
ALTER
TABLE CUSTOMERS
ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID,
NAME);
Delete Primary Key:
You can clear the primary key
constraints from the table, Use Syntax:
ALTER
TABLE CUSTOMERS DROP PRIMARY KEY ;
SQL>
desc cust;
Name Null? Type
-----------------------------------------
-------- ----------------------------
ID NOT
NULL NUMBER(38)
NAME NOT NULL
VARCHAR2(20)
AGE NOT NULL
NUMBER(38)
ADDRESS
CHAR(25)
SALARY
NUMBER(18,2)
SQL>
alter table cust add primary key(id);
alter
table cust add primary key(id)
*ERROR
at line 1: ORA-02260: table can have
only one primary key
SQL>
create table cust1(id int not null, name varchar2(20) not null, age int not
null, address char(25), salary decimal(18,2));
Table
created.
SQL>
alter table cust1 add primary key(id,name);
Table
altered.
SQL>
desc cust1;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID NOT
NULL NUMBER(38)
NAME NOT NULL
VARCHAR2(20)
AGE NOT NULL
NUMBER(38)
ADDRESS
CHAR(25)
SALARY
NUMBER(18,2)
SQL>
alter table cust1 add constraint pk_custid primary key(id,name);
alter
table cust1 add constraint pk_custid primary key(id,name)
*
ERROR
at line 1: ORA-02260: table can have only one primary key
SQL>
alter table cust1 drop primary key;
Table
altered.
SQL>
alter table cust drop primary key;
Table
altered.
SQL>
alter table cust add constraint pk primary key(id , name);
Table
altered.
SQL>
desc cust;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID NOT
NULL NUMBER(38)
NAME NOT NULL
VARCHAR2(20)
AGE NOT NULL
NUMBER(38)
ADDRESS
CHAR(25)
SALARY
NUMBER(18,2)
SQL>
