Database Tutorial

You can use the CREATE statement to create objects, like tables, views, objects, sequences, types, functions, and procedures. These examples demonstrate how to define tables, sequences, and views.

CREATE TABLE Statement

The following demonstrates creating a table with inline PRIMARY KEY and NOT NULL constraints on columns. The CONSTRAINT constraint_name is an optional phrase in the statement. It is a best practice to provide meaningful constraint names because they show up in error messages when DML statements attempt to violate the constraint. When you leave the phrase out of the statement, the system generated constraint name is displayed. System generated constraint names can be researched in the database catalog but it slows problem resolution time.

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE TABLE military_user
( military_user_id        NUMBER        CONSTRAINT pk1_military_user PRIMARY KEY
, military_user_name      VARCHAR2(20)  CONSTRAINT nn1_military_user NOT NULL
, military_user_group_id  NUMBER        CONSTRAINT nn2_military_user NOT NULL
, military_user_branch    NUMBER        CONSTRAINT nn3_military_user NOT NULL
, military_user_rank      NUMBER        CONSTRAINT nn4_military_user NOT NULL
, last_name               VARCHAR2(20)
, first_name              VARCHAR2(20)
, middle_initial          VARCHAR2(1)
, created_by              NUMBER        CONSTRAINT nn5_military_user NOT NULL
, creation_date           DATE          CONSTRAINT nn6_military_user NOT NULL
, last_updated_by         NUMBER        CONSTRAINT nn7_military_user NOT NULL
, last_update_date        DATE          CONSTRAINT nn8_military_user NOT NULL);

The following demonstrates an inline PRIMARY KEY constraint, several inline NOT NULL constraints, four DEFAULT values, an out-of-line UNIQUE constraint, and two out-of-line FOREIGN KEY constraints.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE military_lookup
( military_lookup_id  NUMBER       CONSTRAINT pk1_military_lookup PRIMARY KEY
, table_name          VARCHAR2(30) CONSTRAINT nn1_military_lookup NOT NULL
, column_name         VARCHAR2(30) CONSTRAINT nn2_military_lookup NOT NULL
, lookup_type         VARCHAR2(30) CONSTRAINT nn3_military_lookup NOT NULL
, lookup_value        VARCHAR2(30) CONSTRAINT nn4_military_lookup NOT NULL
, lookup_meaning      VARCHAR2(80) CONSTRAINT nn5_military_lookup NOT NULL
, lookup_code         VARCHAR2(10)
, created_by          NUMBER       DEFAULT -1
, creation_date       DATE         DEFAULT SYSDATE
, last_updated_by     NUMBER       DEFAULT -1
, last_update_date    DATE         DEFAULT SYSDATE
, CONSTRAINT un1_military_lookup UNIQUE (table_name, column_name, lookup_type, lookup_value, lookup_code)
, CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id)
, CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id));

The FOREIGN KEY phrase references a column name found in the table within the parentheses. The REFERENCES phrase points to the table name followed by a column name in that table in parentheses. It is important to note that the table name may be the same table or a different table. When the referenced table is the same table the FOREIGN KEY constrains the relationship between one column or set of columns and another column or set of columns in the same or different row of the table. This type of relationship is known as a recursive or self-referencing relationship.

It’s also very important to note that FOREIGN KEY constraints don’t force you to provide a value when inserting or updating rows. You must use an inline NOT NULL constraint and an out-of-line FOREIGN KEY constraint when you want the foreign key constrained column to be a mandatory column.

It is possible to create an optional foreign key constrained column. Let’s say you’re modeling an on military base Pizza Parlor’s ordering system. A customer may call in for delivery, which requires a home address; or for pickup, which doesn’t require a home address. You can create a FOREIGN KEY constraint on the ADDRESS_ID column of the ADDRESS table with the following inline syntax that creates a TELEPHONE table:

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE military_telephone
( telephone_id    NUMBER          CONSTRAINT pk1_military_lookup PRIMARY KEY
...
, address_id          NUMBER      REFERENCES address(address_id)
...
, created_by          NUMBER      DEFAULT -1
, creation_date       DATE        DEFAULT SYSDATE
, last_updated_by     NUMBER      DEFAULT -1
, last_update_date    DATE        DEFAULT SYSDATE
, CONSTRAINT fk1_military_lookup  FOREIGN KEY(created_by) REFERENCES military_user(military_user_id)
, CONSTRAINT fk2_military_lookup  FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id));

CREATE INDEX Statement

Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, MERGE, and DELETE statements. Indexes are also called fast access paths.

The Oracle Database 12c database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT, UPDATE, MERGE, and DELETE statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.

Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they are visible to the Oracle Database 12c cost-based optimizer and usable when statements run against the tables they support.

You have the ability to make any index invisible, in which case queries and DML statements won’t use the index because they won’t see it. However, the cost-based optimizer still sees the index and maintains it with any DML statement change. That means making an index invisible isn’t quite like making the index unusable or like dropping it temporarily. An invisible index becomes overhead and thus is typically a short-term solution to run a resource- intensive statement that behaves better without the index while avoiding the cost of rebuilding it after the statement runs.

It is also possible to make an index unusable, in which case it stops collecting information and becomes obsolete and the database drops its index segment. You rebuild the index when you change it back to a usable index.

Indexes work on the principal of a key. A key is typically a set of columns or expressions on which you can build an index, but it’s possible that a key can be a single column. An index based on a set of columns is a composite, or concatenated, index.

Indexes can be unique or non-unique. You create a unique index anytime you constrain a column by assigning a primary key or unique constraint, but they’re indirect indexes. You create a direct unique index on a single column with the following syntax against two non-unique columns:

1
2

CREATE INDEX military_lookup_u1
ON military_lookup ( lookup_table );

You could convert this to a non-unique index on two columns by using this syntax:

1
2

CREATE INDEX military_lookup_u1
ON military_lookup ( lookup_table, lookup_column );

Making the index unique is straightforward;, you only need to add a UNIQUE key wordk to the CREATE INDEX statement, like

1
2
3
4

CREATE UNIQUE INDEX military_lookup_u1
ON military_lookup ( lookup_table
                   , lookup_column
                   , lookup_type );

Most indexes use a B-tree (balanced tree). A B-tree is composed of three types of blocks—a root branch block for searching next-level blocks, branch blocks for searching other branch blocks, or and leaf blocks that store pointers to row values. B-trees are balanced because all leaf-blocks are at the same level, which means the length of search is the same to any element in the tree. All branch blocks store the minimum key prefix required to make branching decisions through the B-tree.

CREATE SEQUENCE Statement

Oracle requires that you explicitly define sequences, which differs from MySQL and SQL Server where they’re implicitly created with the table. The following demonstrates creating an explicit sequence in Oracle with a default START WITH value of 1.

1

CREATE SEQUENCE military_user_s1;

The following demonstrates creating an explicit sequence in Oracle with an override START WITH value of 1001.

1

CREATE SEQUENCE military_user_s1 START WITH 1001;

CREATE VIEW Statement

The Oracle database also let you define views. A view is the result of a SQL query or SELECT statement. You have two options with views. The most common is a query only view, which is like the example. The less frequent view is a key preserved view. You can update one or more of the tables in this type of view, provided the respective restrictions of the database engine are met.

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE OR REPLACE VIEW military_personnel AS
SELECT  ml2.lookup_meaning||' '
||      first_name||DECODE(middle_initial,'',' ',' '||middle_initial||'. ')||last_name
||      ', '||ml1.lookup_meaning AS military_person
FROM    military_user mu
,       military_lookup ml1
,       military_lookup ml2
WHERE   military_user_branch = ml1.military_lookup_id
AND     ml1.table_name = 'MILITARY_USER'
AND     ml1.column_name = 'MILITARY_BRANCH'
AND     military_user_rank = ml2.military_lookup_id
AND     ml2.table_name = 'MILITARY_USER'
AND     ml2.column_name = 'MILITARY_RANK';

Oracle supports a CREATE OR REPLACE DDL syntax for views. This syntax lets you avoid a DROP statement before creating a view again.