Database Tutorial

The following shows you how to define an inline PRIMARY KEY and FOREIGN KEY constraints in an Oracle database. When you define table-level (or out-of-line) constraints the FOREIGN KEY takes an argument list, which is the one or comma-delimited list of columns that make up the foreign key. The data types of each column and their organizational order is also critical. Any difference between the primary and foreign keys will cause the DDL statement to fail.

CREATE TABLE tweetie_bird
( tweetie_bird_id     NUMBER        CONSTRAINT pk1_tweetie_bird PRIMARY KEY
, tweetie_bird_house  VARCHAR(30)  CONSTRAINT nn1_tweetie_bird NOT NULL
, grandma_id          NUMBER        CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id));

You would add primary and foreign keys after table creation by using the following syntax:

ALTER TABLE tweetie_bird
  ADD CONSTRAINT pk1_tweetie_bird PRIMARY KEY (tweetie_bird_id)
  ADD CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id);

Like the inline PRIMARY KEY constraint, the table-level constraint takes a single tweetie_bird_id column name as an argument, but it could easily be a parameter list when it points to a compound primary key. The REFERENCES keyword takes a table name followed by a parameter list of comma delimited column names that are found in the referenced table.

The following shows you how to define an inline PRIMARY KEY and FOREIGN KEY constraints in a MySQL database. When you define table-level (or out-of-line) constraints the FOREIGN KEY takes an argument list, which is the one or comma-delimited list of columns that make up the foreign key. The data types of each column and their organizational order is also critical. Any difference between the primary and foreign keys will cause the DDL statement to fail.

CREATE TABLE tweetie_bird
( tweetie_bird_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, tweetie_bird_house  VARCHAR(30)  NOT NULL
, grandma_id          INT UNSIGNED CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id));

A common mistake made by folks new to MySQL is to give FOREIGN KEY columns an int data type. This is fine provided that the PRIMARY KEY column is also an int not an int unsigned. If the PRIMARY KEY column is an int unsigned, you must define the FOREIGN KEY column likewise.

You would add primary and foreign keys after table creation by using the following syntax:

ALTER TABLE tweetie_bird
  ADD CONSTRAINT pk1_tweetie_bird PRIMARY KEY (tweetie_bird_id)
, ADD CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id);

Like the inline PRIMARY KEY constraint, the table-level constraint takes a single tweetie_bird_id column name as an argument, but it could easily be a parameter list when it points to a compound primary key. The REFERENCES keyword takes a table name followed by a parameter list of comma delimited column names that are found in the referenced table.

You have two options for defining a NOT NULL constraint in an Oracle database. One is inline and the other is out-of-line. An out-of-line constraint is also known as a table-level constraint. Only the inline NOT NULL constraint shows the column value as required or mandatory. The out-of-line alternative is a CHECK constraint, which constrains the column but doesn’t evidence itself when you describe it.

In an Oracle database, you define an inline NOT NULL constraint like this:

CREATE TABLE sample
( sample_id    NUMBER       CONSTRAINT pk1_sample PRIMARY KEY
, sample_text  VARCHAR2(20) CONSTRAINT nn1_sample NOT NULL );

Alternatively, the Oracle database lets you define the table without the constraint. You can then constrain the column with a CHECK constraint, like this:

-- Create the table with an unconstrained column.
CREATE TABLE sample
( sample_id     NUMBER CONSTRAINT pk1_sample PRIMARY KEY
, sample_text   VARCHAR2(20));
 
-- Add the Check constraint that disallows a null value and makes the column mandatory.
ALTER TABLE sample
  ADD CONSTRAINT ck1_sample CHECK (sample_text IS NOT NULL);

Unlike Oracle, you have only one way to define a NOT NULL constraint in a MySQL database. It is inline constraint, which can’t have a meaningful name. MySQL treats a NOT NULL constraint as a table property, which explains why an alternative doesn’t exist.

In a MySQL database, you define an inline NOT NULL constraint like this:

CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_text  VARCHAR(20)  NOT NULL );

The following illustrates an out-of-line constraint definition in an Oracle database. One of differences between Oracle and MySQL are the data type definitions and the automatic numbering convention. You can read more about those differences here.

The only reason for this syntax on a single column is because somebody doesn’t understand that a PRIMARY KEY implicitly makes a column NOT NULL constrained. Therefore, while a NOT NULL constraint is unnecessary for a primary key column, some folks use this technique.

CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_nn1 NOT NULL
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk       PRIMARY KEY (grandma_id));

The CONSTRAINT keyword precedes the constraint name and is optional if you want to accept a system assigned name. The type of constraint follows the constraint name when provided. The recommended best practices is:

CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_pk PRIMARY KEY
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL);

A PRIMARY KEY constraint may designate a collection of columns as a primary key. When a set of columns defines a PRIMARY KEY constraint, you must define it as an out-of-line constraint. You may only define a PRIMARY KEY inline when it only references a single column in a CREATE statement. You must define a multiple column PRIMARY KEY constraints through an out-of-line constraint during table creation, or through an ALTER TABLE statement after creating the table.

You implement an inline PRIMARY KEY constraint and automatic numbering by using the following syntax:

CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_text  VARCHAR(20)  NOT NULL );

You would implement a compound PRIMARY KEY constraint with the following syntax. A compound primary key generally doesn’t apply to automatic numbering, and it’s truly interesting how some programmers work around that. A programmer is typically somebody who codes without great insight, whereas a developer is somebody who codes with design responsibility. Developers are also called software engineers.

CREATE TABLE sample
( sample_id    VARCHAR(10)
, sample_text  VARCHAR(20)
, CONSTRAINT pk_sample PRIMARY KEY (sample_id, sample_text));

The sample_id column data type is not numeric, which should indicate to most people that the sample’s identification comes from a manual identification system. You will revisit this in the UNIQUE constraint section.

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 nn5_military_lookup NOT NULL
, lookup_meaning      VARCHAR2(80) CONSTRAINT nn6_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 preceding CREATE statement builds a UNIQUE constraint across five columns in the table. It means that only one row can contain those values. An INSERT or UPDATE statement is thereby constrained from allowing a second row to contain that set of unique values in those specific columns. However, Oracle doesn’t optimize queries based on that constraint.

MySQL lets you implement a UNIQUE constraint as either a KEY or as an INDEX. The choice of a KEY acts most like a hash map. You choose INDEX when you want the UNIQUE constraint to guarantee uniqueness and expedite queries.

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 preceding CREATE statement builds a UNIQUE constraint across five columns in the table. It means that only one row can contain those values. An INSERT or UPDATE statement is thereby constrained from allowing a second row to contain that set of unique values in those specific columns.