Oracle Apps Modules

Oracle DDL Statements: Creating and Managing Tables

Oracle DDL Statements: Creating and Managing Tables

DDL Statements are used to create, change and drop schema objects like tables, views, index, synonym, sequence etc.

Note: Before and after a DDL statement, oracle issues an implicit Commit, hence one cannot rollback a DDL Statement.

Creating and Managing TABLES

In this tutorial I am going to create 3 tables in Oracle HR schema which will contain data about Employee evaluations.

  1. PERFORMANCE_PARTS : contains the categories of employee performance that are evaluated and their relative weights
  2. EVALUATIONS: contains employee information, evaluation date, job, manager, and department
  3. SCORES : contains the scores assigned to each performance category for each evaluation

In this we will follow two ways of Table creation: one by SQL Developer Create Table tool and second with Create Table Statement.

Creating PERFORMANCE_PARTS using SQL Developer Create Table tool:

  1. Expand HR connection and click on New Table.

Oracle DDL Statements: Creating and Managing Tables

Fill the details as shown below:

Table Name: PERFORMANCE_PARTS

Columns: PERFORMANCE_ID, NAME, and WEIGHT.

Oracle DDL Statements: Creating and Managing Tables

And Click Ok.

Creating the EVALUATIONS table using Create Table statement.

CREATE TABLE EVALUATIONS (
EVALUATION_ID NUMBER(8,0),
EMPLOYEE_ID NUMBER (6,0),
EVALUATION_DATE DATE,
JOB_ID VARCHAR2(10),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0),
TOTAL_SCORE NUMBER(3,0)
);

Oracle DDL Statements: Creating and Managing Tables

Creating Scores Table

CREATE TABLE SCORES (
EVALUATION_ID NUMBER(8,0),
PERFORMANCE_ID VARCHAR2(2),
SCORE NUMBER(1,0)
);

Oracle DDL Statements: Creating and Managing Tables


Using Constraints to ensure Data integrity

Constraints are used to restrict the values a column can have.

Below are the types of constraints:

  1. Not Null: Restricts a value from being null.

  2. UNIQUE: Restricts multiple rows for having same value but allows some values to be null.

  3. Primary Key: It is the combination of NOT NULL and UNIQUE.

  4. Foreign Key: Used for value in one table to match with value in another table.

  5. Check: Used to satisfy a specified condition.

  6. REF: describes the relationship between a REF column and the object that it references


Adding NOT NULL Constraints to existing columns:

  1. Adding NOT NULL Constraints on PERFORMANCE_PARTS using SQL Developer edit table tool.

Expand HR_CONN -> Expand Tables -> right-click on PERFORMANCE_PARTS Table and click on edit.

Oracle DDL Statements: Creating and Managing Tables

Click on Name and select check the Not Null and click on OK.

Oracle DDL Statements: Creating and Managing Tables

Adding constraints to PERFORMANCE_PARTS using ALTER TABLE statement.

ALTER TABLE PERFORMANCE_PARTS
MODIFY WEIGHT NOT NULL;

Oracle DDL Statements: Creating and Managing Tables


Adding UNIQUE Constraints.

Add UNIQUE CONSTRAINTS on SCORES Table Using Tool.

Right click on SCORES -> select constraints -> Click on add Unique.

Oracle DDL Statements: Creating and Managing Tables

Enter Constraints Name: SCORES_EVAL_PERF_UNIQUE

And select columns EVALUATION_ID and PERFORMANCE_ID.

Oracle DDL Statements: Creating and Managing Tables


Adding Primary Key constraints.

Add primary key constraints on PERFORMANCE_PART Table using tool.

Primary KEY: PERF_PERF_ID_PK

Adding Primary Key constraints

Click on OK.

Add Primary Key Constraints on EVALUATION Table using ALTER TABLE statement.

ALTER TABLE EVALUATIONS
ADD CONSTRAINT EVAL_EVAL_ID_PK PRIMARY KEY (EVALUATION_ID);

Oracle DDL Statements: Creating and Managing Tables


Adding Foreign Key Constraints

Add two Foreign Key constraints using SQL Developer tool

Adding SCORES_EVAL_FK

Adding Foreign Key Constraints

Click on OK.

Hence, a foreign key constraint named SCORES_EVAL_FK is added to the EVALUTION_ID column of the SCORES table, referencing the EVALUTION_ID column of the EVALUATIONS table.

Adding SCORES_PERF_FK

Adding Foreign Key Constraints

Hence A foreign key constraint named SCORES_PERF_FK is added to the PERFORMANCE_ID column of the SCORES table, referencing the PERFORMANCE_ID column of the PERFORMANCE_PARTS table.

Add a foreign key constraints using ALTER TABLE Statement.

ALTER TABLE EVALUATIONS
ADD CONSTRAINT EVAL_EMP_ID_FK FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEES (EMPLOYEE_ID);

Add a foreign key constraints using ALTER TABLE Statement

Hence we have added a foreign key constraints to the EMPLOYEE_ID column of the EVALUATIONS table, referencing the EMPLOYEE_ID column of the EMPLOYEES table.


Adding a CHECK Constraints on SCORES Table using SQL Developer tool.

Constraints Name: SCORE_VALID

Condition: score >= 0 and score <= 9.

Adding a CHECK Constraints

Adding a CHECK Constraints


Inserting Values in the Table using SQL Developer tool

Add rows of data in PERFORMANCE_PARTS table.

Inserting Values in the Table using SQL Developer tool

Enter following values. And click on Commit change icon.

WM      Workload Management     0.2

CM        Communication                 0.2

BR         Building Relationships       0.2

CF         Customer Focus                 0.2

TW        Teamwork                          0.2

RO        Results Orientation            0.2

INSERT INTO "HR"."PERFORMANCE_PARTS" (PERFORMANCE_ID, NAME, WEIGHT) VALUES ('WM', 'Workload Management', '0.2')
INSERT INTO "HR"."PERFORMANCE_PARTS" (PERFORMANCE_ID, NAME, WEIGHT) VALUES ('CM', 'Communication', '0.2')
INSERT INTO "HR"."PERFORMANCE_PARTS" (PERFORMANCE_ID, NAME, WEIGHT) VALUES ('BR', 'Building Relationships', '0.2')
INSERT INTO "HR"."PERFORMANCE_PARTS" (PERFORMANCE_ID, NAME, WEIGHT) VALUES ('CF', 'Customer Focus', '0.2')
INSERT INTO "HR"."PERFORMANCE_PARTS" (PERFORMANCE_ID, NAME, WEIGHT) VALUES ('TW', 'Teamwork', '0.2')
INSERT INTO "HR"."PERFORMANCE_PARTS" (PERFORMANCE_ID, NAME, WEIGHT) VALUES ('RO', 'Results Orientation', '0.2')

Updating or changing the data in data pane.

Update following rows

And now commit changes.


Deleting rows of a table Using SQL Developer tool.

Delete the row where Name = Results Orientation.

Click on Commit button to made changes permanently.

After deletion

Working with Index in Oracle SQL

Index is used in a column of a table to speed up the sql statement execution of that table (reducing disk I/O when properly used).

Create an Index on EVALUATIONS table using SQL Developer tool.

Working with Index in Oracle SQL

Click OK.

Or you can use below query to do the same.

CREATE INDEX EVAL_JOB_IX
ON EVALUATIONS (JOB_ID ASC) NOPARALLEL;

Now the EVALUATIONS table has an index named EVAL_JOB_IX on the column JOB_ID.

Dropping an Index

DROP INDEX EVAL_JOB_ID;

Dropping a Table Using SQL Developer tool.


Hence you finished Oracle DDL Statements: Creating and Managing Tables.

In next tutorial, you will learn about Views, Sequence and Synonyms.

Please subscribe us to be updated.

Leave a Reply