• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql

sql

DROPPING, DISABLING and ENABLING a FOREIGN KEY in Oracle SQL – PLSQL

October 30, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a FOREIGN KEY is column / field that appears in one table and must appear in another table.

Important points about FOREIGN KEY in Oracle SQL / PLSQL:

  1. A FOREIGN KEY in Oracle SQL / PLSQL creates a parent child pattern between two tables.
  2. The referenced table is called the PARENT table and the table having the FOREIGN KEY is called as the CHILD table.
  3. The FOREIGN KEY in child table generally references PRIMARY KEY in parent table.
  4. A foreign key can be dropped, disabled or enabled in ALTER TABLE statement.

Scenario 1:

Dropping a FOREIGN KEY

Syntax to drop a FOREIGN KEY in ALTER statement is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Let’s take an example for understanding:
Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ as the primary key for the ‘employee’ table

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

And we want to create a table named ‘comm’ with ‘emp_id’ as FOREIGN KEY as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

We can achieve the same as:

CREATE TABLE employee
(employee_id   NUMBER(10)       NOT NULL
,employee_name VARCHAR2(500)    NOT NULL
,salary        NUMBER(20)       NOT NULL
,department    VARCHAR2(300)    NOT NULL
,CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);
CREATE TABLE comm
(emp_id             NUMBER(10)
,commission_percent NUMBER(20)
,CONSTRAINT fk_employee
 FOREIGN KEY (emp_id)
 REFERENCES employee(employee_id)
);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 4 columns namely ‘employee_id’, ‘employee_name’, ‘department’ and ‘salary’ and we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

Also, we have created a new table named ‘comm’ which has 2 columns namely ‘emp_id’ and ‘commission_percent’ and we are having ‘emp_id’ as foreign key referencing ‘employee_id’ of ‘employee’ table.

We want to drop the FOREIGN KEY constraint from ‘comm’ table then we can achieve it as:

ALTER TABLE comm
DROP CONSTRAINT fk_employee;

Scenario 2:

Disabling a FOREIGN KEY

Syntax for disabling a FOREIGN KEY using ALTER statement is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example:

ALTER TABLE comm
DISABLE CONSTRAINT fk_employee;

Scenario 3:

Enabling a FOREIGN KEY

Syntax for enabling a FOREIGN KEY using ALTER statement is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example:

ALTER TABLE comm
ENABLE CONSTRAINT fk_employee;

Filed Under: sql Tagged With: dropping disabling and enabling a foreign key in oracle plsql, dropping disabling and enabling a foreign key in oracle sql, FOREIGNKEYPLSQL, how to use dropping disabling and enabling a foreign key in oracle database query, how to use dropping disabling and enabling a foreign key in oracle plsql, how to use dropping disabling and enabling a foreign key in oracle sql, syntax and example of dropping disabling and enabling a foreign key in oracle database query, syntax and example of dropping disabling and enabling a foreign key in oracle plsql, syntax and example of dropping disabling and enabling a foreign key in oracle sql, using dropping disabling and enabling a foreign key in oracle database query, using dropping disabling and enabling a foreign key in oracle plsql, using dropping disabling and enabling a foreign key in oracle sql

FOREIGN KEY with ON DELETE SET NULL in Oracle SQL – PLSQL

October 30, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a FOREIGN KEY is column / field that appears in one table and must appear in another table.

Important points about FOREIGN KEY ON DELETE SET NULL in Oracle SQL / PLSQL:

  1. A FOREIGN KEY creates a parent child pattern between two tables.
  2. The referenced table is called the PARENT table and the table having the FOREIGN KEY is called as the CHILD table.
  3. The FOREIGN KEY in child table generally references PRIMARY KEY in parent table.
  4. A foreign key with cascade delete can be defined in CREATE TABLE or ALTER TABLE statement.

A FOREIGN KEY with ON DELETE SET NULL means that whenever a record from the parent table is deleted all the corresponding record(s) in the child table will have the FOREIGN KEY fields set to NULL. The records from the child table are not deleted unlike ON DELETE CASCADE.

Syntax to create a FOREIGN KEY with ON DELETE SET NULL in CREATE statement 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
FOREIGN KEY (column_name1, column_name2, . . . column_nameN )
REFERENCES parent_table_name(column_name1, column_name2, . . . column_nameN)
ON DELETE SET NULL
);

Syntax to create a FOREIGN KEY with ON DELETE SET NULL in ALTER statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN)
ON DELETE SET NULL;

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’ as the primary key for the ‘employee’ table

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

And we want to create a table named ‘comm’ with ‘emp_id’ as FOREIGN KEY as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

We can achieve the same as:


CREATE TABLE employee
(employee_id    NUMBER(10)    NOT NULL
,employee_name  VARCHAR2(500) NOT NULL
,salary         NUMBER(20)    NOT NULL
,department     VARCHAR2(300) NOT NULL
,CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

CREATE TABLE comm
(emp_id             NUMBER(10)
,commission_percent NUMBER(20)
,CONSTRAINT fk_employee
 FOREIGN KEY (emp_id)
 REFERENCES employee(employee_id)
 ON DELETE SET NULL
);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 4 columns namely ‘employee_id’, ‘employee_name’, ‘department’ and ‘salary’ and we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

Also, we have created a new table named ‘comm’ which has 2 columns namely ‘emp_id’ and ‘commission_percent’ and we are having ‘emp_id’ as foreign key referencing ‘employee_id’ of ‘employee’ table.

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


INSERT INTO employee
VALUES (101,'Emp A',10000,'Sales');

INSERT INTO employee
VALUES (102,'Emp B',20000,'IT');

INSERT INTO employee
VALUES (103,'Emp C',28000,'IT');

INSERT INTO employee
VALUES (104,'Emp D',30000,'Support');

INSERT INTO employee
VALUES (105,'Emp E',32000,'Sales');

Step 3: Inserting the data in the ‘comm’ table.


INSERT INTO comm
VALUES (102,20);

INSERT INTO comm
VALUES (103,20);

INSERT INTO comm
VALUES (104,NULL);

INSERT INTO comm
VALUES (105,10);

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

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

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

Now, if we query the ‘comm’ table as:

SELECT *
FROM comm;

We will get the following result:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

Here we have successfully created ‘employee’ and ‘comm’ tables with ‘employee_id’ as PRIMARY KEY for ‘employee’ table and ‘emp_id’ as foreign key for ‘comm’ and also inserted data in ‘employee’ and ‘comm’ tables.

If we try to insert one more record in ‘comm’ table of ‘emp_id = 106’ as:

INSERT INTO comm
VALUES (106,30);

We get an error that integrity constraint is getting violated and the data cannot be entered, this is because ‘emp_id = 106’ is not found in ‘employee’ table and hence the record cannot be entered in ‘comm’ table.

Lets delete a record of ‘employee_id =105’ from ‘employee’ table

DELETE FROM employee
WHERE employee_id = 105;

The above statement will delete a record of ‘employee_id = 105’ from ‘employee’ table and when we query the ‘employee’ table as

SELECT *
FROM employee;

We will get the below result

Employee_Id Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support

Also if we query the ‘comm’ table as


SELECT *
FROM comm;

We get the below result:

Emp_Id Commission_Percent
102 20
103 20
104
10

We can see that the record for ‘emp_id = 105’ has been set to NULL in the ‘comm’ table because we have written ON DELETE SET NULL while creating FOREIGN KEY in ‘comm’ table.


Scenario 2:

Using ALTER TABLE statement to create a FOREIGN KEY with ON DELETE CASCADE

Syntax for creating a FOREIGN KEY using ALTER TABLE statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN)
ON DELETE SET NULL;

Example:


ALTER TABLE comm
ADD CONSTRAINT comm_fk
FOREIGN KEY (emp_id)
REFERENCES employee (employee_id)
ON DELETE SET NULL;

Filed Under: sql Tagged With: foreign key on delete set null in oracle plsql, foreign key on delete set null in oracle sql, FOREIGNKEYPLSQL, how to use foreign key on delete set null in oracle database query, how to use foreign key on delete set null in oracle plsql, how to use foreign key on delete set null in oracle sql, syntax and example of foreign key on delete set null in oracle database query, syntax and example of foreign key on delete set null in oracle plsql, syntax and example of foreign key on delete set null in oracle sql, using foreign key on delete set null in oracle database query, using foreign key on delete set null in oracle plsql, using foreign key on delete set null in oracle sql

FOREIGN KEY with ON DELETE CASCADE in Oracle SQL – PLSQL

October 30, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a FOREIGN KEY is column / field that appears in one table and must appear in another table.

Important points about FOREIGN KEY ON DELETE CASCADE in Oracle SQL / PLSQL:

  1. A FOREIGN KEY creates a parent child pattern between two tables.
  2. The referenced table is called the PARENT table and the table having the FOREIGN KEY is called as the CHILD table.
  3. The FOREIGN KEY in child table generally references PRIMARY KEY in parent table.
  4. A foreign key with cascade delete can be defined in CREATE TABLE or ALTER TABLE statement.

A FOREIGN KEY with cascade delete means that whenever a record from the parent table is deleted all the corresponding record(s) from the child table are also automatically deleted.

Syntax to create a FOREIGN KEY with ON DELETE CASCADE in CREATE statement 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
FOREIGN KEY (column_name1, column_name2, . . . column_nameN )
REFERENCES parent_table_name(column_name1, column_name2, . . . column_nameN)
ON DELETE CASCADE
);

Syntax to create a FOREIGN KEY with ON DELETE CASCADE in ALTER TABLE statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN)
ON DELETE CASCADE;

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’ as the primary key for the ‘employee’ table

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

And we want to create a table named ‘comm’ with ‘emp_id’ as FOREIGN KEY as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

We can achieve the same as:


CREATE TABLE employee
(employee_id   NUMBER(10)      NOT NULL
,employee_name VARCHAR2(500)   NOT NULL
,salary        NUMBER(20)      NOT NULL
,department    VARCHAR2(300)   NOT NULL
,CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);
CREATE TABLE comm
(emp_id             NUMBER(10)
,commission_percent NUMBER(20)
,CONSTRAINT fk_employee
 FOREIGN KEY (emp_id)
 REFERENCES employee(employee_id)
 ON DELETE CASCADE
);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 4 columns namely ‘employee_id’, ‘employee_name’, ‘department’ and ‘salary’ and we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

Also, we have created a new table named ‘comm’ which has 2 columns namely ‘emp_id’ and ‘commission_percent’ and we are having ‘emp_id’ as foreign key referencing ‘employee_id’ of ‘employee’ table.

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

INSERT INTO employee
VALUES (101,'Emp A',10000,'Sales');

INSERT INTO employee
VALUES (102,'Emp B',20000,'IT');

INSERT INTO employee
VALUES (103,'Emp C',28000,'IT');

INSERT INTO employee
VALUES (104,'Emp D',30000,'Support');

INSERT INTO employee
VALUES (105,'Emp E',32000,'Sales');

Step 3: Inserting the data in the ‘comm’ table.


INSERT INTO comm
VALUES (102,20);

INSERT INTO comm
VALUES (103,20);

INSERT INTO comm
VALUES (104,NULL);

INSERT INTO comm
VALUES (105,10);

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

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

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

Now, if we query the ‘comm’ table as:

SELECT *
FROM comm;

We will get the following result:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

Here we have successfully created ‘employee’ and ‘comm’ tables with ‘employee_id’ as PRIMARY KEY for ‘employee’ table and ‘emp_id’ as foreign key for ‘comm’ and also inserted data in ‘employee’ and ‘comm’ tables.

If we try to insert one more record in ‘comm’ table of ‘emp_id = 106’ as:


INSERT INTO comm
VALUES (106,30);

We get an error that integrity constraint is getting violated and the data cannot be entered, this is because ‘emp_id = 106’ is not found in ‘employee’ table and hence the record cannot be entered in ‘comm’ table.

Lets delete a record of ‘employee_id =105’ from ‘employee’ table

DELETE FROM employee
WHERE employee_id = 105;

The above statement will delete a record of ‘employee_id = 105’ from ‘employee’ table and when we query the ‘employee’ table as

SELECT *
FROM employee;

We will get the below result:

Employee_Id Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support

Also if we query the ‘comm’ table as


SELECT *
FROM comm;

We get the below result:

Emp_Id Commission_Percent
102 20
103 20
104

We can see that the record for ‘emp_id = 105’ has been deleted from the ‘comm’ table also because we have written ON DELETE CASCADE while creating FOREIGN KEY in ‘comm’ table.


Scenario 2:

Using ALTER TABLE statement to create a FOREIGN KEY with ON DELETE CASCADE

Syntax for creating a FOREIGN KEY using ALTER TABLE statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN)
ON DELETE CASCADE;

Example:


ALTER TABLE comm
ADD CONSTRAINT comm_fk
FOREIGN KEY (emp_id)
REFERENCES employee (employee_id)
ON DELETE CASCADE;

Filed Under: sql Tagged With: foreign key on delete cascade in oracle plsql, foreign key on delete cascade in oracle sql, FOREIGNKEYPLSQL, how to use foreign key on delete cascade in oracle database query, how to use foreign key on delete cascade in oracle plsql, how to use foreign key on delete cascade in oracle sql, syntax and example of foreign key on delete cascade in oracle database query, syntax and example of foreign key on delete cascade in oracle plsql, syntax and example of foreign key on delete cascade in oracle sql, using foreign key on delete cascade in oracle database query, using foreign key on delete cascade in oracle plsql, using foreign key on delete cascade in oracle sql

FOREIGN KEY in Oracle SQL – PLSQL

October 30, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a FOREIGN KEY is column / field that appears in one table and must appear in another table.
Important points about FOREIGN KEY in Oracle SQL / PLSQL:

  1. A FOREIGN KEY creates a parent child pattern between two tables.
  2. The referenced table is called the PARENT table and the table having the FOREIGN KEY is called as the CHILD table.
  3. The FOREIGN KEY in child table generally references PRIMARY KEY in parent table.
  4. A foreign key can be defined in CREATE TABLE or ALTER TABLE statement.

Syntax to create a FOREIGN KEY in CREATE statement 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
FOREIGN KEY (column_name1, column_name2, . . . column_nameN )
REFERENCES parent_table_name(column_name1, column_name2, . . . column_nameN)
);

Syntax to create a FOREIGN KEY in ALTER statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (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’ as the primary key for the ‘employee’ table

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

And we want to create a table named ‘comm’ with ‘emp_id’ as FOREIGN KEY as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

We can achieve the same as:


CREATE TABLE employee
(employee_id   NUMBER(10)       NOT NULL
,employee_name VARCHAR2(500)    NOT NULL
,salary        NUMBER(20)       NOT NULL
,department    VARCHAR2(300)    NOT NULL
,CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

CREATE TABLE comm
(emp_id             NUMBER(10)
,commission_percent NUMBER(20)
,CONSTRAINT fk_employee
 FOREIGN KEY (emp_id)
 REFERENCES employee(employee_id)
);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 4 columns namely ‘employee_id’, ‘employee_name’, ‘department’ and ‘salary’ and we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

Also, we have created a new table named ‘comm’ which has 2 columns namely ‘emp_id’ and ‘commission_percent’ and we are having ‘emp_id’ as foreign key referencing ‘employee_id’ of ‘employee’ table.

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


INSERT INTO employee
VALUES (101,'Emp A',10000,'Sales');

INSERT INTO employee
VALUES (102,'Emp B',20000,'IT');

INSERT INTO employee
VALUES (103,'Emp C',28000,'IT');

INSERT INTO employee
VALUES (104,'Emp D',30000,'Support');

INSERT INTO employee
VALUES (105,'Emp E',32000,'Sales');

Step 3: Inserting the data in the ‘comm’ table.

INSERT INTO comm
VALUES (102,20);

INSERT INTO comm
VALUES (103,20);

INSERT INTO comm
VALUES (104,NULL);

INSERT INTO comm
VALUES (105,10);

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

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

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

Now, if we query the ‘comm’ table as:

SELECT *
FROM comm;

We will get the following result:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

Here we have successfully created ‘employee’ and ‘comm’ tables with ‘employee_id’ as PRIMARY KEY for ‘employee’ table and ‘emp_id’ as foreign key for ‘comm’ and also inserted data in ‘employee’ and ‘comm’ tables.

If we try to insert one more record in ‘comm’ table of ‘emp_id = 106’ as:

INSERT INTO comm
VALUES (106,30);

We get an error that integrity constraint is getting violated and the data cannot be entered, this is because ‘emp_id = 106’ is not found in ‘employee’ table and hence the record cannot be entered in ‘comm’ table.


Scenario 2:

Using ALTER TABLE statement to create a FOREIGN KEY

Syntax for creating a FOREIGN KEY using ALTER TABLE statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN);

Example:

ALTER TABLE comm
ADD CONSTRAINT comm_fk
FOREIGN KEY (emp_id)
REFERENCES employee (employee_id);

Filed Under: sql Tagged With: foreign key in oracle plsql, foreign key in oracle sql, FOREIGNKEYPLSQL, how to use foreign key in oracle database query, how to use foreign key in oracle plsql, how to use foreign key in oracle sql, syntax and example of foreign key in oracle database query, syntax and example of foreign key in oracle plsql, syntax and example of foreign key in oracle sql, using foreign key in oracle database query, using foreign key in oracle plsql, using foreign key in oracle sql

Oracle PL/SQL Primary Keys

October 29, 2012 by techhoneyadmin

Oracle PL/SQL PRIMARY KEY is single or group of column(s)/field(s) that can uniquely identify each record in a table.

An Oracle PL/SQL COMPOSITE PRIMARY KEY is the one which is made up of more than one fields or columns.

Important points about Oracle PL/SQL PRIMARY KEYS are:

  • A table can have one and only one PRIMARY KEY.
  • In Oracle SQL 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 defined using CREATE TABLE or ALTER TABLE Statement.

Oracle PL/SQL PRIMARY KEY using CREATE TABLE Statement Syntax

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/notnull
,CONSTRAINT constraint_name PRIMARY KEY (column_name1, column_name2, . , column_nameN );

Oracle PL/SQL PRIMARY KEY using ALTER TABLE Statement Syntax

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

Oracle PL/SQL PRIMARY KEY Examples

Oracle PL/SQL PRIMARY KEY – Using CREATE TABLE Statement

Suppose, we wish to create Oracle PL/SQL PRIMARY KEY constraint on ‘employee_id’ column of ‘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

Below CREATE TABLE Statement will define Oracle PL/SQL PRIMARY KEY on ‘employee’ table with “employee_id’ as PRIMARY KEY


CREATE TABLE employee
(employee_id        NUMBER(10)       NOT NULL
,employee_name      VARCHAR2(500)    NOT NULL
,salary             NUMBER(20)       NOT NULL
,department         VARCHAR2(300)    NOT NULL
,commission         NUMBER(20)
,constraint employee_pk PRIMARY KEY (employee_id);

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 the ‘employee_id’ column as the primary key for the ‘employee’ table.

In the above Oracle PL/SQL PRIMARY KEY example the SQL CREATE TABLE Statement will create a new table named ‘employee’ having a PRIMARY KEY on ‘employee_id’ column.

Let’s insert some data in the ‘employee’ table.


insert into employee
values (101,'Emp A',10000,'Sales',10);

insert into employee
values (102,'Emp B',20000,’IT’,20);

insert into employee
values (103,'Emp C',28000,'IT',20);

insert into employee
values (104,'Emp D',30000,'Support',NULL);

insert into employee
values (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:

SELECT *
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 ‘employee_id’ as PRIMARY KEY and also inserted data in ‘employee’ table.

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

insert into employee
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’ is the primary key 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.


Oracle PL/SQL PRIMARY KEY – Using ALTER TABLE Statement

Suppose we have not created any Oracle PL/SQL PRIMARY KEY on ‘employee’ table earlier .

We can use the SQL ALTER TABLE Statement to define a new PRIMARY KEY on employee table.

For example, the below SQL ALTER TABLE Statement will create PRIMARY KEY on ‘employee_id’ column of employee table.

ALTER TABLE employee
ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);

We can always DROP, DISABLE and ENABLE an Oracle PL/SQL PRIMARY KEY.


Filed Under: sql Tagged With: : primary key in oracle plsql, CREATETABLEPLSQL, how to use primary key in oracle database query, how to use primary key in oracle plsql, how to use primary key in oracle sql, primary key in oracle sql, PRIMARYKEYPLSQL, syntax and example of primary key in oracle database query, syntax and example of primary key in oracle plsql, syntax and example of primary key in oracle sql, using primary key in oracle database query, using primary key in oracle plsql, using primary key in oracle sql

  • « Go to Previous Page
  • Page 1
  • Page 2
  • Page 3
  • Page 4
  • Page 5
  • Go to Next Page »

Copyright © 2025 · Parallax Pro on Genesis Framework · WordPress · Log in