sql

DROPPING, DISABLING and ENABLING CHECK CONSTRAINT in Oracle SQL – PLSQL

October 31, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a CHECK CONSTRAINT allows us to specify a condition on each record / row of a table.
Important points about CHECK CONSTRAINT in Oracle SQL / PLSQL:

In Oracle SQL / PLSQL a CHECK CONSTRAINT can never be defined on a VIEW but only on a table.
A CHECK CONSTRAINT on a table can refer to columns of the same table and not of any other table.
No sub-query is allowed in the CHECK CONSTRAINT.
A CHECK CONSTRAINT can be created using CREATE TABLE or ALTER TABLE statement.

Syntax to DROP a CHECK CONSTRAINT in SQL / PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`DROP` `CONSTRAINT` `employee_pk;`

The above statement will drop the check constraint ‘employee_pk’ from the ‘employee’ table.

Disable a CHECK CONSTRAINT.

If we do not wish to delete the check constraint as we may need the same in future but for some time we want the check constraint not to function, then we can disable the check constraint.

Syntax for disabling the check constraint is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`DISABLE` `CONSTRAINT` `employee_pk;`

Here in the above ALTER statement we have disabled the CHECK CONSTRAINT ‘employee_pk’ on the ‘employee’ table.

Enabling a CHECK CONSTRAINT

We can enable a check constraint that has been disabled earlier, the syntax for enabling a check constraint is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`ENABLE` `CONSTRAINT` `employee_pk;`

Here in the above statement we have enabled the check constraint ‘employee_pk’ in ‘employee’ table using ALTER TABLE statement.

CHECK CONSTRAINT in Oracle SQL – PLSQL

October 31, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a CHECK CONSTRAINT allows us to specify a condition on each record / row of a table.

Important points about CHECK CONSTRAINT in Oracle SQL / PLSQL:

In Oracle SQL / PLSQL a CHECK CONSTRAINT can never be defined on a VIEW but only on a table.
A CHECK CONSTRAINT on a table can refer to columns of the same table and not of any other table.
No sub-query is allowed in the CHECK CONSTRAINT.
A CHECK CONSTRAINT can be created using CREATE TABLE or ALTER TABLE statement.

Syntax to create a CHECK CONSTRAINT in CREATE TABLE statement in Oracle SQL / PLSQL is:

CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
,column_nameN datatype NULL/NOT NULL
,CONSTRAINT constraint_name CHECK (column_name condition ) [DISABLE];

Note: The DISABLE keyword is optional, if we create a CHECK CONSTRAINT with disable keyword then the constraint will be created but will not be enforced.

Syntax to create a CHECK CONSTRAINT in ALTER TABLE statement is :

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

Let’s take an example for understanding:

Scenario 1:

Step 1: Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ column having CHECK CONSTRAINT.

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We can achieve the same as:

1CREATE TABLE employee
2(employee_id   NUMBER(10)     NOT NULL
3,employee_name VARCHAR2(500)  NOT NULL
4,salary        NUMBER(20)     NOT NULL
5,department    VARCHAR2(300)  NOT NULL
6,commission    NUMBER(20)
7,CONSTRAINT employee_pk CHECK (employee_id BETWEEN 101 AND 500)
8);

Also we are having CHECK CONSTRAINT on ‘employee_id’ column which ensures that we do not enter any employee_id less than 101 or more than 500.

Step 2: Inserting the data in the ‘employee’ table.

1INSERT INTO employee
2VALUES (101,'Emp A',10000,'Sales',10);
3 
4INSERT INTO employee
5VALUES (102,'Emp B',20000,'IT',20);
6 
7INSERT INTO employee
8VALUES (103,'Emp C',28000,'IT',20);
9 
10INSERT INTO employee
11VALUES (104,'Emp D',30000,'Support',NULL);
12 
13INSERT INTO employee
14VALUES (105,'Emp E',32000,'Sales',10);

The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.

Now, if we query the employee table as:

`1`	`SELECT` `*`

`2`	`FROM` `employee;`

We will get the following result:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have successfully created ‘employee’ table with column ‘employee_id’ having UNIQUE CONSTRAINT and also inserted data in ‘employee’ table.
If we try to insert one more record of employee_id = 50 as:

`1`	`INSERT` `INTO` `employee`

`2`	`VALUES` `(50,'Emp F',40000,'Sales',30);`

We get an error that check constraint is getting violated and the data cannot be entered, this is because ‘employee_id’ column has CHECK CONSTRAINT that enforces a condition that the ‘employee_id’ entered must be greater than 100 and lesser than 500 .

Scenario 2:

Using ALTER TABLE statement to create CHECK CONSTRAINT on a table
As mentioned earlier that an ALTER TABLE statement can be used to create CHECK CONSTRAINT on a table.

The syntax to create CHECK CONSTRAINT using ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`ADD` `CONSTRAINT` `employee_pk` `CHECK(employee_id` `BETWEEN` `101` `AND` `500);`

Here we have altered the ‘employee’ table and have successfully created CHECK CONSTRAINT on ‘employee_id’ column.

DROPPING, DISABLING and ENABLING UNIQUE CONSTRAINT in Oracle SQL – PLSQL

October 31, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a UNIQUE CONSTRAINT is single or group of column(s)/field(s) that can uniquely identify each record in a table.

Some of the column(s) / field(s) of unique constraint can have NULL values until the combination can uniquely identify each record of table.

Important points about UNIQUE CONSTRAINT in Oracle SQL / PLSQL:

In Oracle SQL / PLSQL a UNIQUE CONSTRAINT can be created by using maximum of 32 columns / fields.
A UNIQUE CONSTRAINT can be dropped, disabled and enabled in ALTER TABLE statement.

Syntax to DROP a UNIQUE CONSTRAINT in SQL / PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`DROP` `CONSTRAINT` `employee_pk;`

The above statement will drop the unique constraint ‘employee_pk’ from the ‘employee’ table.

Disable a UNIQUE CONSTRAINT.

If we do not wish to delete the unique constraint as we may need the same in future but for some time we want the unique constraint not to function, then we can disable the unique constraint

Syntax for disabling the unique constraint in Oracle SQL / PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`DISABLE` `CONSTRAINT` `employee_pk;`

Here in the above ALTER statement we have disabled the UNIQUE CONSTRAINT ‘employee_pk’ on the ‘employee’ table.

Enabling a UNIQUE CONSTRAINT

We can enable a unique constraint that has been disabled earlier, the syntax for enabling a unique constraint in Oracle SQL / PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`ENABLE` `CONSTRAINT` `employee_pk;`

Here in the above statement we have enabled the unique constraint ‘employee_pk’ in ‘employee’ table using ALTER TABLE statement.

UNIQUE CONSTRAINT in Oracle SQL – PLSQL

October 31, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a UNIQUE CONSTRAINT is single or group of column(s)/field(s) that can uniquely identify each record in a table.

Some of the column(s) / field(s) of unique constraint can have NULL values until the combination can uniquely identify each record of table.

Important points about UNIQUE CONSTRAINT in Oracle SQL / PLSQL:

In Oracle SQL / PLSQL a UNIQUE CONSTRAINT can be created by using maximum of 32 columns / fields.
A UNIQUE CONSTRAINT can be defined in CREATE TABLE or ALTER TABLE statement.

Syntax to create a UNIQUE CONSTRAINT in CREATE TABLE statement is:

Syntax to create a UNIQUE CONSTRAINT in ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, . . . ,column_nameN );

Let’s take an example for understanding:

Scenario 1:

Step 1: Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ column having UNIQUE CONSTRAINT.

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We can achieve the same as:

1CREATE TABLE employee
2(employee_id   NUMBER(10)      NOT NULL
3,employee_name VARCHAR2(500)   NOT NULL
4,salary        NUMBER(20)      NOT NULL
5,department    VARCHAR2(300)   NOT NULL
6,commission    NUMBER(20)
7,CONSTRAINT employee_pk UNIQUE (employee_id)
8);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 5 columns namely ‘employee_id’, ‘employee_name’, ‘department’, ‘salary’ and ‘commission’. Also we are having UNIQUE CONSTRAINT on ‘employee_id’ column of the ‘employee’ table.

Step 2: Inserting the data in the ‘employee’ table.

1INSERT INTO employee
2VALUES (101,'Emp A',10000,'Sales',10);
3 
4INSERT INTO employee
5VALUES (102,'Emp B',20000,'IT',20);
6 
7INSERT INTO employee
8VALUES (103,'Emp C',28000,'IT',20);
9 
10INSERT INTO employee
11VALUES (104,'Emp D',30000,'Support',NULL);
12 
13INSERT INTO employee
14VALUES (105,'Emp E',32000,'Sales',10);

The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.

Now, if we query the employee table as:

`1`	`SELECT` `*`

`2`	`FROM` `employee;`

We will get the following result:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have successfully created ‘employee’ table with column ‘employee_id’ having UNIQUE CONSTRAINT and also inserted data in ‘employee’ table.

If we try to insert one more record of employee_id = 105 as:

`1`	`INSERT` `INTO` `employee`

`2`	`VALUES` `(105,'Emp F',40000,'Sales',30);`

We get an error that unique constraint is getting violated and the data cannot be entered, this is because ‘employee_id’ column has UNIQUE CONSTRAINT and for employee_id = 105 we already have a record in ‘employee’ table and hence one more record of the same key cannot be entered.

Scenario 2:

Creating a combination of columns as UNIQUE CONSTRAINT

Suppose we want to create a table named ‘employee’ in the database as shown below, with combination of ‘employee_id’ and ‘employee_name’ as the UNIQUE CONSTRAINT columns for the ‘employee’ table

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

We can achieve the same as:

1CREATE TABLE employee
2(employee_id   NUMBER(10)       NOT NULL
3,employee_name VARCHAR2(500)    NOT NULL
4,salary        NUMBER(20)       NOT NULL
5,department    VARCHAR2(300)    NOT NULL
6,commission    NUMBER(20)
7,CONSTRAINT employee_pk UNIQUE (employee_id,employee_name)
8);

Let’s insert some data in the newly created ‘employee’ table

1INSERT INTO employee
2VALUES (101,'Emp A',10000,'Sales',10);
3 
4INSERT INTO employee
5VALUES (102,'Emp B',20000,'IT',20);
6 
7INSERT INTO employee
8VALUES (103,'Emp C',28000,'IT',20);
9 
10INSERT INTO employee
11VALUES (104,'Emp D',30000,'Support',NULL);
12 
13INSERT INTO employee
14VALUES (105,'Emp E',32000,'Sales',10);

The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.

Now, if we query the employee table as:

`1`	`SELECT` `*`

`2`	`FROM` `employee;`

We will get the following result:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Suppose we want to insert one more record with ‘employee_id = 105’ and ‘employee_name = Emp F’ then we can do this and oracle will not give any error because the combination of ‘employee_id’ and ‘employee_name’ is still unique.

Scenario 3:

Using ALTER TABLE statement to create UNIQUE CONSTRAINT on a table

As mentioned earlier that an ALTER TABLE statement can be used to create UNIQUE CONSTRAINT on a table.
The syntax to create UNIQUE CONSTRAINT using ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name1,column_name2, . . column_nameN);

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`ADD` `CONSTRAINT` `employee_pk` `UNIQUE` `(employee_id);`

Here we have altered the ‘employee’ table and have successfully created UNIQUE CONSTRAINT on ‘employee_id’ column.

We can also create UNIQUE CONSTRAINT by a combination of columns using the ALTER TABLE statement

Example:

`1`	`ALTER` `TABLE` `employee`

`2`	`ADD` `CONSTRAINT` `employee_pk` `UNIQUE` `(employee_id, employee_name);`

Here we have altered the ‘employee’ table and have successfully created UNIQUE CONSTRAINT on ‘employee_id’ and ‘employee_name’ column.

Oracle/SQL DROP, DISABLE, ENABLE PRIMARY Key

October 31, 2012 by techhoneyadmin

Oracle SQL allows to DROP PRIMARY KEY, DISABLE and ENABLE a PRIMARY KEY Constraint using the SQL ALTER TABLE Statement.

In other words we can say that, whenever we want to Drop, Disable or Enable a PRIMARY KEY in SQL, we have to use the ALTER TABLE Statement.

Important points about PRIMARY KEYS in Oracle PL/SQL:

A table can have one and only one primary key.
In Oracle SQL / PLSQL a primary key can be created by using maximum of 32 columns / fields.
The columns / fields that are used to define a primary key on table cannot have NULL values.
A primary key can be dropped, disabled and enabled in ALTER TABLE statement.

DROP PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

DROP PRIMARY KEY Constraint Example

The below example will DROP the PRIMARY KEY ‘employee_pk’ from ‘employee’ table.

ALTER TABLE employee
DROP CONSTRAINT employee_pk;

DISABLE PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

DISABLE PRIMARY KEY Constraint Example

The below example will DISABLE the PRIMARY KEY ‘employee_pk’ from ‘employee’ table.

ALTER TABLE employee
DISABLE CONSTRAINT employee_pk;

To DISABLE a PRIMARY key can be a handy option if we wish to use the same PRIMARY key in near future.

ENABLE PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

ENABLE PRIMARY KEY Constraint Example

The below example will ENABLE the PRIMARY KEY ‘employee_pk’ of ‘employee’ table

ALTER TABLE employee
ENABLE CONSTRAINT employee_pk;

We can ENABLE only those PRIMARY KEYS which have been DISABLED earlier