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 TABLEData 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.
Naming Database Objects
When it comes to naming database objects there are certain rules and also corporate conventions.
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.
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).
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.
To remove a table or column comment set the value to ''.
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.
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.
Similar to table specifications, Constraints apply rules or restrictions on the table fields.
Oracle SQL supports the UNIQUENOT NULL, PRIMARY KEY, FOREIGN KEY, and CHECK constraint types.
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.
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).