Database Tutorial

Week #2: Articles

Learning Outcomes

• Learn how to create a column with a PRIMARY KEY constraint.
• Learn how to modify a column with a PRIMARY KEY constraint.

• NOT NULL Constraint
• CHECK Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

PRIMARY KEY Constraint

A PRIMARY KEY constraint checks whether a column value will be unique among all rows in a table and disallows null values. A PRIMARY KEY may span two or more columns. A multiple column PRIMARY KEY is known as a composite or compound key, which can be confusing but essentially the terms only mean that the primary key spans two or more columns.

Good primary keys typically have two or more columns. The occurrence of multiple columns in primary keys also helps the database create more effective self-balancing tree (b-tree) indexes, which speed searches.

Therefore, a PRIMARY KEY has the behaviors of both NOT NULL and UNIQUE constraints. However, it does go further than a UNIQUE constraint because it disallows any null values in a primary key that contains two or more column.

After you identify the natural key, you have identified a candidate key that may become the primary key of a table. Any unique key automatically becomes a candidate key. As a rule, it is ill advised to use a natural key as a primary key because the natural key represents your understanding of uniqueness at a moment in time. That understanding of uniqueness may evolve as your understanding of the table’s subject evolves.

If you use the natural key as the primary key the following occurs:

• The database management system (DBMS) automatically creates a unique index on every primary key, which stores copies of all the data in the primary key columns in the index.
• You must create copies of the primary key columns in every table that requires a foreign key back to the table. Foreign keys let you connect rows in one table to another table, and are effectively reference comparison keys.
• You must write SQL join logic that connects all columns in the primary key to all columns in the foreign key.

If your understanding of the natural key of the table changes, you must add that new knowledge. Typically, that means you must do:

1. Drop the primary key and unique index.
2. Create a new primary key by adding any new columns to it and create a new unique index.
3. Replicating any new columns to all external foreign keys.
4. Re-write all joins between the base table and all tables that hold a foreign key reference to the base table.

Such a change could require rewriting thousands, if not millions of SELECT, INSERT, UPDATE, MERGE, and DELETE statements. It may take an enormous amount of programmer time to accomplish such a change. However, there’s an alternative strategy available that eliminates Step #4 – re-writing all the joins.

The alternative strategy requires that you create a surrogate key column for every table. A surrogate column is a substitute for the natural key and is simply an ID column populated with a unique sequence of integer values. In fact, modern databases provide a sequence structure for this purpose. A sequence is a structure that holds a counter variable between and across connections to the database that lets you populate an ID column.

If you use the natural key as the primary key the following occurs:

• The database management system (DBMS) automatically creates a unique index on the primary key, which stores a copy of all integers inside an ID column into an index.
• You create a unique index across the ID column and the columns of the natural key, which gets stored in the index.
• You must create copies of the ID column primary key in every table that requires a foreign key back to the table. Foreign keys let you connect rows in one table to another table, and are effectively reference comparison keys. They use the unique index that you created that connected the surrogate key to the natural key. That unique index leverages a b-tree to optimize operations.
• You must write SQL join logic that connects all columns in the primary key to only the ID column and any copies of the ID column used as a foreign key in other tables.

If your understanding of the natural key of the table changes, you must add that new knowledge. The impact on your application is dramatically smaller because surrogate keys simplify implementation. Typically, when you use surrogate keys, you must do the following as the natural key evolves:

1. Drop the primary key and unique index.
2. Drop the unique index you created using the surrogate key and old natural key.
3. Create a new unique index with the surrogate key and new natural key.

The use of surrogate keys ensures your data model can evolve quickly at a low cost. Using natural keys as the primary key is always a bad idea provided you never use a surrogate key to disambiguate a natural key that isn’t unique. Or to put it simply, “Natural keys should always be unique, and surrogate keys should never become the column that makes natural keys unique.”

Oracle lets you implement a PRIMARY KEY constraint in a CREATE statement as either an in-line or out-of-line constraint. You may use the in-line syntax when there’s only one unique column but you must use a table-level or out-of-line when the primary key constraint applies across more than one column. The alter command works when you have one column or a set of columns in a primary key constraint.

• CREATE Statement:
• You can define a PRIMARY KEY constraint on the same line as you define a single column. This approach is called placing an inline constraint on a column.

  The following sample table has two named inline constraints:

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck UNIQUE);

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck UNIQUE);

  The first inline constraint is a PRIMARY KEY constraint on the sample_id column. You should note that the PRIMARY KEY constraint makes a column applies three rules. It verifies the sample_id column is mandatory and unique in the set of rows inside the sample table, and designates the sample_id column as a primary key that you can refer to from foreign key columns.

• You can define a PRIMARY KEY constraint after the list of column values as a table-level or out-of-line constraint with the following syntax:

  CREATE TABLE sample ( sample_id NUMBER , sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL , CONSTRAINT sample_pk PRIMARY KEY (sample_id) , CONSTRAINT sample_ck UNIQUE (sample_text));

  CREATE TABLE sample ( sample_id NUMBER , sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL , CONSTRAINT sample_pk PRIMARY KEY (sample_id) , CONSTRAINT sample_ck UNIQUE (sample_text));

• ALTER Statement:
• You can add or drop a PRIMARY KEY constraint when you want to add a customer_pk constraint that includes the first_name, middle_name, and last_name columns.

  You drop an existing customer_pk UNIQUE constraint with the following syntax:

  ALTER TABLE customer DROP CONSTRAINT customer_pk;

  ALTER TABLE customer DROP CONSTRAINT customer_pk;

• After you drop the customer_uq PRIMARY KEY constraint, you can add a new version of the customer_pk PRIMARY KEY constraint with the following syntax:

  ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY(first_name, middle_name, last_name);

  ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY(first_name, middle_name, last_name);