Database Tutorial

Oracle

You can use the CREATE statement to create objects, like tables, views, objects, sequences, types, functions, procedures, and sequences. 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.

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.

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