PRIMARY KEY Constraint

The PRIMARY KEY is a constraint usually imposed in the CREATE TABLE statement. The data going into a PRIMARY KEY column is required to be unique. The column is usually an identifier and often labeled "mytable_id" and it is often best to cause it to be incremented automatically. It can be used in conjunction with a column designated as a FOREIGN KEY in JOINs to combine and normalize table data.

The syntax for mySQL looks like this:

CREATE TABLE table(
table_id INT NOT NULL AUTO_INCREMENT,
column2 DATA TYPE,
column3 DATA TYPE,
PRIMARY KEY (table_id)
)

In other SQL applications the PRIMARY KEY constraint is signified on the line of the column on which it is imposed:

CREATE TABLE table(
table_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
column2 DATA TYPE,
column3 DATA TYPE
)

With a flexible twist, the PRIMARY KEY can be a column actually composed of the data in two other columns. The command looks like this:

CREATE TABLE table(
column1 DATA TYPE NOT NULL,
column2 DATA TYPE NOT NULL,
column3 DATA TYPE,
CONSTRAINT table_id PRIMARY KEY (column1, column2)
)

This should work in most applications of SQL including mySQL. It should be noted that PRIMARY KEYs can never be NULL, and there can only be one per table. The above does not violate this stricture because there is actually only one column on the table designated as the PRIMARY KEY. Its cells simply contain the contents of two other columns. It is probably best to avoid using this particular construction.

If we wished to set up a simple table of female fictional characters with a PRIMARY KEY, we could do the following:

CREATE TABLE fem_fict_chars (
ffc_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
title VARCHAR(255),
author VARCHAR(255),
PRIMARY KEY (ffc_id)
)

A PRIMARY KEY can also be added or dropped after the table has already been created. To add:

ALTER TABLE table
ADD PRIMARY KEY (table_id)

To drop in mySQL:

ALTER TABLE table
DROP PRIMARY KEY

To drop in other applications:

ALTER TABLE table
DROP CONSTRAINT table_id

Obviously, it is better to designate a PRIMARY KEY when the table is created rather than to add it later. Try to plan out the structure of your database in advance and avoid having to use ALTER TABLE.

< PREPARE and EXECUTE | SELECT Statement >


Send notes in disappearing ink!

Interesting Pages