Tech Honey

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

CREATETABLEPLSQL

Oracle PL/SQL Primary Keys

by

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.

CREATE TABLE Statement in Oracle SQL – PLSQL Using Already Existing Tables

by

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Here we will learn how to use CREATE TABLE statement to create new tables using already existing tables in the database.

Syntax for using CREATE TABLE statement to create a new table by already existing table(s) is:

CREATE TABLE new_table_name
AS
(SELECT *
FROM
old_table_name
);

Let’s take an example for understanding:

Suppose we already have a table named “Employee” as shown below.

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

Scenario 1:

We want to create a new table named “employee_copy” which should be copy of “Employee” table. For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT *
FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’. Also all the data from the employee table is copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You 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

Scenario 2:

We want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) . For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
 FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 3 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table. Also all the data from the employee table of the aforementioned 3 columns will be copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result:

Employee_ID Employee_Name Salary
101 Emp A 10000
102 Emp B 20000
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Scenario 3:

Suppose we also have a department table in our database which has the list of departments as shown below:

Department_Id Department
1000 Sales
2000 IT
3000 Support

And we want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) and 1 column from the “Department” table e.g. (department). For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
        ,dept.department
 FROM employee emp
      ,department dept
 WHERE emp.department = dept.department);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 4 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table and ‘department’ from department table.

The employee_copy table will have the data as shown below:

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

CREATE TABLE Statement in Oracle SQL – PLSQL

by

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Syntax for the CREATE TABLE statement is:

CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
);

Each column must have a data type. Also every column has to be defined as NULL or NOT NULL, if nothing is specified in NULL or NOT NULL place then the default value will be assumed as NULL.

Let’s take an example for understanding:

Suppose we want to create a table named “Employee” as shown below (without data).

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

Now we will see how we can use CREATE STATEMENT to create or define a new table in data base.

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)
);

The above CREATE TABLE statement will create a new table named as employee in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’.