Database Tutorial

Oracle

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.

MySQL

You can use the CREATE statement to create tables, views, synonyms, stored functions and procedures. A key difference between Oracle and MySQL is that sequences are implicitly created with tables by using the AUTO_INCREMENT keyword. These examples demonstrate how to define tables and views.

CREATE INDEX Statement

The following demonstrates how you create an INDEX. You should note that table must already exist to run this command.

1
2

CREATE INDEX military_user_key
ON military_user (military_user_name, military_user_group_id, military_user_branch, military_user_rank, last_name, first_name, middle_initial);

CREATE TABLE Statement

The following demonstrates creating a table with inline PRIMARY KEY and NOT NULL constraints on columns. The CONSTRAINT constraint_name is not an optional phrase for an inline PRIMARY KEY or NOT NULL constraint in a CREATE statement. While 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. You must do so by defining the PRIMARY KEY constraint out-of-line.

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 IF NOT EXISTS military_user
( military_user_id        INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, military_user_name      VARCHAR(20)   NOT NULL
, military_user_group_id  INT UNSIGNED  NOT NULL
, military_user_branch    INT UNSIGNED  NOT NULL
, military_user_rank      INT UNSIGNED  NOT NULL
, last_name               VARCHAR(20)
, first_name              VARCHAR(20)
, middle_initial          VARCHAR(1)
, created_by              INT UNSIGNED  NOT NULL
, creation_date           DATE          NOT NULL
, last_updated_by         INT UNSIGNED  NOT NULL
, last_update_date        DATE          NOT NULL) ENGINE=InnoDB;

The IF NOT EXISTS clause tells the database to check to see if the table is already defined in the database catalog before running the CREATE statement. This prevents a CREATE table from raising a 1050 error because the table is already defined in the database.

ERROR 1050 (42S01): Table 'sample' already exists

You give a meaningful name to a PRIMARY KEY constraint by using an out-of-line constraint definition in a CREATE statement. The following shows you the comparative syntax.

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

CREATE TABLE IF NOT EXISTS military_user
( military_user_id        INT UNSIGNED  AUTO_INCREMENT
, military_user_name      VARCHAR(20)   NOT NULL
, military_user_group_id  INT UNSIGNED  NOT NULL
, military_user_branch    INT UNSIGNED  NOT NULL
, military_user_rank      INT UNSIGNED  NOT NULL
, last_name               VARCHAR(20)
, first_name              VARCHAR(20)
, middle_initial          VARCHAR(1)
, created_by              INT UNSIGNED  NOT NULL
, creation_date           DATE          NOT NULL
, last_updated_by         INT UNSIGNED  NOT NULL
, last_update_date        DATE          NOT NULL
, CONSTRAINT military_user_pk PRIMARY KEY(military_user_id)) ENGINE=InnoDB;

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. The FOREIGN KEY constraints require that you designate the table in the scope of a specific database engine that supports referential integrity. The InnoDB is generally the more frequent engine, but you could also do this with other engines.

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

SELECT 'CREATE TABLE military_lookup' AS "Statement"; 
CREATE TABLE IF NOT EXISTS military_lookup
( military_lookup_id  INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, table_name          VARCHAR(30)   NOT NULL
, column_name         VARCHAR(30)   NOT NULL
, lookup_type         VARCHAR(30)   NOT NULL
, lookup_value        VARCHAR(30)   NOT NULL
, lookup_meaning      VARCHAR(80)   NOT NULL
, lookup_code         VARCHAR(10)
, created_by          INT UNSIGNED  DEFAULT 0
, creation_date       DATE
, last_updated_by     INT UNSIGNED  DEFAULT 0
, last_update_date    DATE
, CONSTRAINT un1_military_lookup UNIQUE INDEX (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)) ENGINE=InnoDB;

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 only an out-of-line foreign key constraint syntax.

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, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.

In the scope of the InnoDB Database Engine, the MySQL 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, REPLACE INTO, 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 MySQL 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 ( military_lookup );

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

MySQL creates sequences implicitly when you use AUTO_INCREMENT in the CREATE TABLE syntax. Therefore, there is no syntax to let you create a sequence manually.

CREATE VIEW Statement

The MySQL 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 our met.

1
2
3
4
5
6
7
8
9
10
11

CREATE VIEW military_personnel AS
SELECT  CONCAT(ml2.lookup_meaning,' ',first_name,IF(middle_initial IS NULL,' ',CONCAT(' ',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';

Unfortunately, MySQL doesn’t support a CREATE OR REPLACE clause in their implementation of the DDL syntax that creates views. That means you have to DROP a view before creating it anew.

SQL Server