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:
DESCRIBE
to View the dba_objects TableDESCRIBE 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)
DESCRIBE
to View the all_objects TableCOLUMN 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.
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.
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.
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
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
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
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.
ALTER TABLE
To Change a Table StructureTable 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.
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.
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.
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
CHECK
Constraint