Guide to Oracle SQL (Buffalo State University Version)

Using DDL Statements to Create and Manage Tables

In chapter 2, I covered the CREATE DATABASE, CREATE TABLE, and DROP TABLE Data Definition Languge (DDL) statements. In this chapter I will cover some additional DDL statements to work with tables, the most common database objects.

Data Base Objects

There are many other objects in a database which can be listed from the dba_objects table. In fact, a "real world" database may have hundreds of objects.

DESCRIBE all_objects;

Name              Null? Type          
----------------- ----- ------------- 
OWNER                   VARCHAR2(128) 
OBJECT_NAME             VARCHAR2(128) 
SUBOBJECT_NAME          VARCHAR2(128) 
OBJECT_ID               NUMBER        
DATA_OBJECT_ID          NUMBER        
OBJECT_TYPE             VARCHAR2(23)  
CREATED                 DATE          
LAST_DDL_TIME           DATE          
TIMESTAMP               VARCHAR2(19)  
STATUS                  VARCHAR2(7)   
TEMPORARY               VARCHAR2(1)   
GENERATED               VARCHAR2(1)   
SECONDARY               VARCHAR2(1)   
NAMESPACE               NUMBER        
EDITION_NAME            VARCHAR2(128) 
SHARING                 VARCHAR2(13)  
EDITIONABLE             VARCHAR2(1)   
ORACLE_MAINTAINED       VARCHAR2(1)

-- This command fails if you do not have permissions to view this table
SELECT object_type, COUNT(object_type)
FROM dba_objects
GROUP_BY i=object_type 
ORDER BY object_type

Error starting at line : 2 in command -
SELECT object_type, COUNT(object_type) FROM dba_objects GROUP_BY i=object_type ORDER BY object_type
Error at Command Line : 2 Column : 45
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Figure 12-1: Using DESCRIBE to View the dba_objects Table

DESCRIBE all_objects;

Name              Null?    Type          
----------------- -------- ------------- 
OWNER             NOT NULL VARCHAR2(128) 
OBJECT_NAME       NOT NULL VARCHAR2(128) 
SUBOBJECT_NAME             VARCHAR2(128) 
OBJECT_ID         NOT NULL NUMBER        
DATA_OBJECT_ID             NUMBER        
OBJECT_TYPE                VARCHAR2(23)  
CREATED           NOT NULL DATE          
LAST_DDL_TIME     NOT NULL DATE          
TIMESTAMP                  VARCHAR2(19)  
STATUS                     VARCHAR2(7)   
TEMPORARY                  VARCHAR2(1)   
GENERATED                  VARCHAR2(1)   
SECONDARY                  VARCHAR2(1)   
NAMESPACE         NOT NULL NUMBER        
EDITION_NAME               VARCHAR2(128) 
SHARING                    VARCHAR2(13)  
EDITIONABLE                VARCHAR2(1)   
ORACLE_MAINTAINED          VARCHAR2(1)

Figure 12-2: Using DESCRIBE to View the all_objects Table

COLUMN object_name FORMAT A40
COLUMN object_type FORMAT A10
BREAK ON object_type
SELECT object_type AS "Object Type", object_name AS "Object Name"
FROM all_objects 
WHERE owner = 'GERLANJR' 
ORDER BY object_name;

OBJECT_TYP OBJECT_NAME                             
---------- ----------------------------------------
FUNCTION   FINDSTUDENT                             
INDEX      SYS_C0046788                            
           STATUSTABLE_PK                          
           THESISTABLE_PK                          
           COMMITTEE_PK                            
           COURSETABLE_PK                          
           GRADETABLE_PK                           
           PREREQ_PK                               
           COURSEOFFERINGS_PK                      
           OFFICETABLE_PK   
... I skiped a lot of indexes here           
           STUDENT_PK                              
           THESIS_PK                               
           THESISCOMMITTEE_PK                      
SEQUENCE   SEQ_CALL_ID                             
           SEQ_TELEPHONE_ID                        
           SEQ_SUBSCRIBER_NUMBER                   
           SEQ_STUDENT_SID                         
TABLE      COUNTRIES_COPY                          
           OFFICETABLE                             
           ENROLLMENTTABLE                         
           STATUSTABLE  
... I skiped a lot of tables here
           JIMTABLE                                
           LOCATIONS                               
VIEW       VIEW_STUDENT_GPA                        
           DEPT_EMP                                
           VIEW_REGION                             

97 rows selected.

Figure 12-3: Displaying All The Objects You Own

Naming Database Objects

When it comes to naming database objects there are certain rules and also corporate conventions.

Name must be between 1 and 30 characters. 128 characters is allowed on a link name.
Name cannot be a reserved work (for example, not SELECT).
Name must begin with a letter (A-Z or a-z).
Name can contain only letters, numbers, underscore (_), dollar sign ($, or the hash symbol (#).
Lowercase letters will be converted to uppercase.

Figure 12-4: Naming Database Objects

NOTE: You can override some of these rules by using double quotes (") arunf the object name. However, this is not recommended since you then have to remember to use double quotes when referencing that object. The above rules also apply to column names.

Naming Conventions

Many corporate environments require that database objects and columns conform to a particular format. For example, tables should be nameTable or tableName (most widely used with MySQl), or tblName (most widely used with Microsoft Access). Simarly, column names should be tableId (although Oracle will convert that to TABLEID). This is known as camel case because words are combined and the first letter of the second word is capitalized.

The Database Namespace

The database namespace defines the storage of the object names. Within this namespace the object names must be unique. Each use also has a user namespace so their object names to not conflict with other users' object names.Views are a type of object that resides in the user namespace.

Using Column Specifications When Creating Tables

In previous chapters I covered creating rather simple tables. The CREATE TABLE statement allows you to provide additional specifications (rules) for the tbole fields. You can define rules such as auto_increment a primary key (GENERATED BY DEFAULT AS IDENTITY), "a field cannot be NULL" (NOT NULL) or provide a default value for a field (DEFAULT value).

CREATE TABLE specsTable 
(
  specsID NUMBER GENERATED BY DEFAULT AS IDENTITY,
  firstName VARCHAR2(15),
  lastName VARCHAR2(15),
  hireDate DATE DEFAULT TRUNC(SYSDATE),
  salary NUMBER(9, 2),
  commission NUMBER(7, 2) DEFAULT .03
);
  
Table SPECSTABLE created.

Figure 12-5: Creating a Table With Field Specifications

Adding Table and Column Comments

As I mentioned previously, comments are an important part in any code development. Oracle provides the COMMENT ON command to add a comments to a table or a column. The table comments are stored in the user_tab_comments table and the column comments are stored in the user_col_table table.

COMMENT ON TABLE specsTable IS 'specifications test table';
COMMENT ON COLUMN specsTable.hireDate 
   IS 'defaults to the current system date';
COMMENT ON COLUMN specsTable.commission 
   IS 'defaults to .03 percent';
  
Table SPECSTABLE created.

COLUMN comments FORMAT A35
COLUMN column_name FORMAT A15
COLUMN table_name FORMAT A10
TTITLE "Table Comments"
SELECT * FROM user_tab_comments WHERE table_name = 'SPECSTABLE';

Table Comments
TABLE_NAME  TABLE_TYPE  COMMENTS                   ORIGIN_CON_ID
----------- ----------- -----------------------    -------------
SPECSTABLE  TABLE       specifications test table              0

SELECT * FROM user_tab_comments WHERE table_name = 'SPECSTABLE' AND comments IS NOT NULL;
TTITLE "Column Comments"
SELECT * FROM user_col_comments WHERE table_name = 'SPECSTABLE';

Column Comments
TABLE_NAME COLUMN_NAME     COMMENTS                            ORIGIN_CON_ID
---------- --------------- ----------------------------------- -------------
SPECSTABLE HIREDATE        defaults to the current system date             0
SPECSTABLE COMMISSION      defaults to .03 percent                         0

Figure 12-6: Adding Comments To Tables And Columns

To remove a table or column comment set the value to ''.

COMMENT ON TABLE specsTable IS '';
SELECT * FROM user_tab_comments WHERE table_name = 'SPECSTABLE' AND comments IS NOT NULL;

no rows selected

COMMENT ON COLUMN specsTable.hireDate IS '';
COMMENT ON COLUMN specsTable.commission IS '';
SELECT * FROM user_col_comments WHERE table_name = 'SPECSTABLE';

no rows selected

Figure 12-7: Removing Comments From Tables And Columns

Creating a Table Using a Subquery

Sometmies you need to create a copy of a table with a different name. You can use a subquery SELECT statement on your CREATE TABLE statement.

CREATE TABLE newJimTable AS SELECT * FROM jimTable;
  
Table NEWJIMTABLE created.

SELECT * FROM newJimTable; 

     JIMID FIRSTNAME                 LASTNAME                 
---------- ------------------------- -------------------------
         1 James                     Gerland                  
         2 Phil                      Phailure                 
         3 Sally                     Smart

Figure 12-8: Creating a Table Using a Subquery

Changing a Table Structure

Sometimes you need to change the structure of a table. You may need to add or delete a field or rename a field. The ALTER TABLE statement allow you to perform these tasks.

ALTER TABLE newJimTable ADD (departmentID NUMBER(9));
  
Table NEWJIMTABLE altered.

ALTER TABLE newJimTable ADD (departmentID NUMBER(9));
  
Table NEWJIMTABLE altered.

ALTER TABLE newJimTable RENAME COLUMN departmentID TO deptID;
  
Table NEWJIMTABLE altered.

Figure 12-9: Using ALTER TABLE To Change a Table Structure

Table Constraints

Similar to table specifications, Constraints apply rules or restrictions on the table fields.

Constraint Types

Oracle SQL supports the UNIQUE NOT NULL, PRIMARY KEY, FOREIGN KEY, and CHECK constraint types.

UNIQUE: The column or column combination must have a different vaoue in each record (row).
NOT NULL: Requires that a field (column) must gave a value. Great for fields that you may later search on, such as lastName or zipCode.
PRIMARY KEY: Gaurantees that each record has a unique value and can be retreived. Tables without primary keys are allowed but are not a good idea.
FOREIGN KEY: This field is used to relate the table (parent) to another table (child) based on a "common" field in both tables.
CHECK: Used to create/enforce rules such as "a range" or an "expression". Returns TRUE or FALSE.

Figure 12-10: Creating a Table with a Primary Key Constraint

The most prominent constraint is the Primary Key. There are two (2) ways to define a primary key. When you define a primary key using the CONSTRAINT command you are creating a new database object so you need to give it a unique object name.

-- create field as a primary key 
CREATE TABLE testTable2 (testID NUMBER PRIMARY KEY);

Table TESTTABLE2 created.

-- create a primary key as a constraint
CREATE TABLE testTable3 (
testID NUMBER
CONSTRAINT pk_testID PRIMARY KEY);

Table TESTTABLE3 created.

Figure 12-11: Creating a Table with a Primary Key Constraint

In a relatonal databse tables are "related" to each other via Primary Keys and Secondary (Foreign) Keys. You can use a CONSTRAINT to define this foreign key and the table it realtes to. When you define a foerign key using the CONSTRAINT command you are creating a new database object so you need to give it a unique object name. You also need to define what field in the other table this relationship is based on (REFERENCES).

-- create field as a secondary (foreign) key 
ALTER TABLE testTable3
  ADD CONSTRAINT fk_testID3 
  FOREIGN KEY (testID)
  REFERENCES testTable2(testID);

Table TESTTABLE3 altered.

Figure 12-12: Adding a Secondary (Foreign) Key Constraint to a Table

ALTER TABLE testTable3 ADD hireDate DATE;

Table TESTTABLE3 altered.

ALTER TABLE testTable3 ADD birthDate DATE;

Table TESTTABLE3 altered.

ALTER TABLE testTable3;

Table TESTTABLE3 altered.

-- create field with a CHECK constraint (rule) to ensure employee is at least 16 years old
ADD CONSTRAINT hireDate_ck CHECK (hireDate >= (birthdate + (365*16))); 

Table TESTTABLE3 altered.

SET UNDERLINE =
/
SELECT cols.table_name t, cols.column_name, cols.position, cons.constraint_name,   
  CASE cons.constraint_type
    WHEN 'P' THEN 'Primary Key'
    WHEN 'R' THEN 'Foreign Key'
    WHEN 'C' THEN 'Check'
  END ct
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_name = cols.constraint_name
AND cons.owner = 'GERLANJR'
AND cons.table_Name = 'TESTTABLE3'
ORDER BY cols.table_name, cols.position;

Table Name Field Name        POSITION Constraint Name Constraint Type
========== =============== ========== =============== ===============
TESTTABLE3 TESTID                   1 FK_TESTID3      Foreign Key    
           TESTID                   1 PK_TESTID       Primary Key    
           BIRTHDATE                  HIREDATE_CK     Check          
           HIREDATE                   HIREDATE_CK     Check

Figure 12-13: The CHECK Constraint