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.
- PERFORMANCE_PARTS : contains the categories of employee performance that are evaluated and their relative weights
- EVALUATIONS: contains employee information, evaluation date, job, manager, and department
- 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:
- Expand HR connection and click on New Table.
Fill the details as shown below:
Table Name: PERFORMANCE_PARTS
Columns: PERFORMANCE_ID, NAME, and WEIGHT.
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) );
Creating Scores Table
CREATE TABLE SCORES ( EVALUATION_ID NUMBER(8,0), PERFORMANCE_ID VARCHAR2(2), SCORE NUMBER(1,0) );
Using Constraints to ensure Data integrity
Constraints are used to restrict the values a column can have.
Below are the types of constraints:
Not Null: Restricts a value from being null.
UNIQUE: Restricts multiple rows for having same value but allows some values to be null.
Primary Key: It is the combination of NOT NULL and UNIQUE.
Foreign Key: Used for value in one table to match with value in another table.
Check: Used to satisfy a specified condition.
REF: describes the relationship between a REF column and the object that it references
Adding NOT NULL Constraints to existing columns:
- 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.
Click on Name and select check the Not Null and click on OK.
Adding constraints to PERFORMANCE_PARTS using ALTER TABLE statement.
ALTER TABLE PERFORMANCE_PARTS MODIFY WEIGHT NOT NULL;
Adding UNIQUE Constraints.
Add UNIQUE CONSTRAINTS on SCORES Table Using Tool.
Right click on SCORES -> select constraints -> Click on add Unique.
Enter Constraints Name: SCORES_EVAL_PERF_UNIQUE
And select columns EVALUATION_ID and PERFORMANCE_ID.
Adding Primary Key constraints.
Add primary key constraints on PERFORMANCE_PART Table using tool.
Primary KEY: PERF_PERF_ID_PK
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);
Adding Foreign Key Constraints
Add two Foreign Key constraints using SQL Developer tool
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.
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);
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.
Inserting Values in the Table using SQL Developer tool
Add rows of data in PERFORMANCE_PARTS table.
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.
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.
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.