Creating Tables

Below are some examples of how to create tables in Oracle 12. PL/SQL. For the most part PL/SQL follows standard SQL conventions.  I will show how to create some tables and some of the more popular options when creating tables.

Create Table example with primary key and check constraint.

The table creation is straight forward and similar to other create table SQL statements.  There are a few items of interest in creating the table.

In the column names the column state is in quotes.  The reason being is that STATE is a reserved word in PL/SQL.  While normally not advisable to name a column with the same value as a reserved word, in the example I am showing how you can do it.

On each line, I’m specifying a NOT NULL constraint. When inserting rows not only will the datatype need to match but each column will need a value.  Alternatively if a column could be NULL the NOT NULL can be omitted, or implicitly stated by just using NULL.

The next constraint is specifying the custno column as the primary key.  The value will need to be unique and by definition can not be null.  The last constraint added, was added to the isInternal column where the value must be a “Y” or “N”.

Foreign Keys

The next example adds a foreign key when creating the table.

While a primary key column can not be null, a foreign key can be null.  If the value is optional, you will want to designate it as a NULL column.  The foreign key will reference values of the primary key in the FACILITY table.  The value must exist in the FACILITY table before trying to insert the row into the Location table.

Self Reference Constraints

The next example shows how you can reference another column in the table for the constraint.

Here in this example, the constraint is on the TimeStart and TimeEnd columns.  When inserting or updating rows, the TimeStart must be less than the TimeEnd.

The above are some common examples of creating tables in PL/SQL, while it doesn’t cover every possibility it shows some of the more common tasks. Let me know if you found the above useful.