SQL Constraints #-- Samples

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

 If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

 In SQL, we have the following constraints:

· NOT NULL - Indicates that a column cannot store NULL value

· UNIQUE - Ensures that each row for a column must have a unique value

· PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly

· FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

· CHECK - Ensures that the value in a column meets a specific condition

· DEFAULT - Specifies a default value when specified none for this column

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

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

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

PRIMARY KEY --#

Primary key is an Input/output Data constraint. It serves the purpose of uniquely identifying the rows in a table
Technically Primary key constraint is the combination of NOT NULL and UNIQUE constraints.
There are Two types of Primary key.

1 Simple Primary key and

2 Composite Primary Key.

Primary key which involves only one column of a table is called Simple Primary key whereas the primary key which involvesmore than one columns of a table is called Composite Primary key.
More than one column doesn't mean that you can give any number of columns. This still has some restrictions. A composite key in oracle can have maximum of 32 columns only.

Primary key has a single function of identifying a unique row in the table. In case the simple primary key fails to identify that unique row, the user must then define a composite primary key.
You cannot Delete a Primary key if it is referenced by a foreign key in some other table. There is a unique index created automatically by Oracle so that the requirement of the uniqueness of the PRIMARY KEY constraint is fulfilled.

There are few things which you should know about primary key. (Features of Primary key)

1. A table can have only One Primary key no matter whether its Simple Primary Key Or Composite Primary Key.
2. Columns which are participating in Primary Key cannot have NULL values. This means you cannot leave them unattended or you cannot put NULL value into them.

3 A table can have only One Primary key no matter whether its Simple Primary Key Or Composite Primary Key.

4 Columns which are participating in Primary Key cannot have NULL values. This means you cannot leave them unattended or you cannot put NULL value into them.

5 As primary key is all about Row or Record’s uniqueness thus it will not allow duplicate values.

6 When a Primary Key constraint has been defined on multiple columns then its values can be duplicated provided the duplication is happening within one single column. However the combination of values of all the columns defining each primary key constraint should be unique.

7 Data-types such as LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type are not allowed with the columns which are part of Primary key. Any attempt of creating a primary key with the column of these data-types will raise SQL Error: ORA-02269.

8 The size of the primary key cannot exceed approximately one database block.

9 As I have already mentioned above that a composite primary key can have 32 columns maximum.

10 The Primary key and Unique key should never be designated as the same column or combination of columns.

11 You cannot specify a primary key when creating a sub view in an inheritance hierarchy. The primary key can be specified only for the top-level (root) view.

12 Unique cluster Index gets created automatically on the time creating Primary key.

13 Although it is not necessary for you to define a primary key yet it is always recommended to do so.

How to create a primary key or How to Define a Primary key.

You can define a Primary key either while CREATING a table or After Creating a table using ALTER TABLE statement.

Let’s start with Defining a primary key Using CREATE TABLE statement. There are two ways of defining a Primary Key using CREATE TABLE

14 Defining Primary Key at Column Level and

15 Defining Primary Key at Table Level

efining Primary Key using Create table

1. Defining primary Key at Column Level.

This way of defining constraint is called column level because we define constraints with the column definition while creating table, Here we Define a primary key while defining the column during Create table.
For example 
Let’s Create a table by the name of “Product_master” 

CREATE TABLE product_master
 (
  Product_id    NUMBER(3)  PRIMARY KEY,
  Product_name  VARCHAR2(30),
  Product_price  NUMBER(5)
  );

To make a column - a primary key, you just have to put keyword primary key after datatype and size in column definition as we did above with our first column.

But its always a good practice to provide a meaningful name to your constraint while creating it because oracle database saves all the constraints in Data dictionary and constraints with meaningful name are easy to reference.

So Lets modify the above query and provide a meaningful name to our Primary key constraint.

CREATE TABLE product_master
 (
  Product_id    NUMBER(3)  CONSTRAINT  pmst_pid_pk  PRIMARY KEY,
  Product_name  VARCHAR2(30),
  Product_price  NUMBER(5)
  );

2. Defining Primary Key at Table Level

This way of defining constraint is called table level because we define constraints explicitly in create table statement. Here in this type of primary key creation we first define all our columns and after defining all the columns we define our constraint
for example 
Lets again Create a table this time by the name of ORDER_MASTER

CREATE TABLE order_master
 (
  order_id   NUMBER(3),
  order_data   DATE,
  order_qnty   NUMBER(5),
  CONSTRAINT   ordmstr_oid_pk   PRIMARY KEY ( order_id )
 );

Defining Primary Key using ALTER TABLE statements.

When we have already created table and then want to add primary key constraint then we use ALTER TABLE statement for adding Primary Key constraint.

Let’s say we have a table “Customer” with 3 columns Cust_id, cust_name, phone_no and we don’t have any Primary Key c onstraint on any column and now we want to add Primary Key constraint on cust_id column.
To do this task we will use ALTER TABLE statement as

ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id);

Similarly if you want to define a composite primary key on customer table then

ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id,phone_number);

COMPOSITE PRIMARY KEY

As we already know that Primary Key which involves more than one column of a table is known as Composite primary Key.

We can only define composite primary key at table level. As composite key requires more than one column thus it becomes mandatory to define column of a table first and then primary key.
Let see how

CREATE TABLE customer
 (
  cust_id   NUMBER(3),
  cust_name  VARCHAR2(3),
  phone_no  NUMBER(10),
  CONSTRAINT  cust_cid_pk  PRIMARY KEY ( cust_id, phone_no)
);

How To Drop Primary Key Constraint

To drop primary key constraint we again use ALTER TABLE statement 
Syntax for dropping a constraint is

ALTER TABLE  table_name  DROP CONSTRAINT  constraint_name;

For Example.

ALTER TABLE  customer  DROP CONSTRAINT  cust_cid_pk;

ENABLE & DISABLE PRIMARY KEY

You can enable or Disable primary key constraint by using constraint name with ALTER TABLE statement.
For example Let’s say you want to disable cust_cid_pk constraint which we earlier defined on Customer table.

Disable Primary key constraint

ALTER TABLE  customer  DISABLE CONSTRAINT  cust_cid_pk;

or if you want to enable this constraint then simply write

Enable Primary key constraint

ALTER TABLE  customer  ENABLE CONSTRAINT  cust_cid_pk;

HOW TO CHECK CONSTRAINT ON A TABLE

As we know that DESC statement will show nothing about constraints on a table. But Oracle Database provides us several DATA DICTIONARIES for checking or Describing all the constraint which we have defined on our table.
These Data Dictionaries are

16 USER_CONSTRAINTS

17 USER_CONS_COLUMNS

18 .

Where USER_CONSTRAINTS will give a brief about constraint on table. USER_CONS_COLUMNS is a Data Dictionary which holds information about columns of a table by using this Data dictionary you can have detail information about the constraint on a table. 
For example
Let’s say you want to see constraint detail on customer table

USER_CONSTRAINTS 

SELECT
  CONSTRAINT_NAME,
  CONSTRAINT_TYPE,
  TABLE_NAME,
  STATUS,
  INDEX_NAME
FROM  user_CONSTRAINTS  WHERE  table_name = 'CUSTOMER';

USER_CONS_COLUMNS

SELECT 
  CONSTRAINT_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  POSITION
FROM  user_cons_columns  WHERE  table_name = 'CUSTOMER';

So that’s all about PRIMARY KEY. Hope it gives you a detailed insight into the concept. You can visit my YouTube channel for tutorials on Primary Key. Do like and Subscribe my videos.