Definition de clés

Grâce à SQL, il est possible de définir des clés, c'est-à-dire spécifier la (ou les) colonne(s) dont la connaissance permet de désigner précisément un et un seul tuple (une ligne).
L'ensemble des colonnes faisant partie de la table en cours permettant de désigner de façon unique un tuple est appelé clé primaire et se définit grâce à la clause PRIMARY KEY suivie de la liste de colonnes, séparées par des virgules, entre parenthèses. Ces colonnes ne peuvent alors plus prendre la valeur NULL et doivent être telles que deux lignes ne puissent avoir simultanément la même combinaison de valeurs pour ces colonnes.

PRIMARY KEY (colonne1, colonne2, ...)
Lorsqu'une liste de colonnes de la table en cours de définition permet de définir la clé primaire d'une table étrangère, on parle alors de clé étrangère, et on utilise la clause FOREIGN KEY suivie de la liste de colonnes de la table en cours de définition, séparées par des virgules, entre parenthèses, puis de la clause REFERENCES suivie du nom de la table étrangère et de la liste de ses colonnes correspondantes, séparées par des virgules, entre parenthèses.



A primary key is used to unique identify each row on a table. It can either be part of the actual record itself , or it can be an artificial field (something that has nothing to do with the actual record). A primary key can consists of one or more fields on a table. When multiple fields are used as a primary key, it is called a composite key.

Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

Below is an example for specifying a primary key when creating a table:

MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

Below are examples for specifying a primary key by altering a table:

MySQL:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Oracle:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

SQL Server:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.


FOREIGN KEY (colonne1, colonne2, ...)

REFERENCES Nom_de_la_table_etrangere(colonne1,colonne2,...)



A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDER table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

The structure of these two tables will be as follows:

Table Customer
column name characteristic
SID Primary Key
Last_Name
First_Name

Table Orders
column name characteristic
Order_ID Primary Key
Order_Date
Customer_SID Foreign Key
Amount

In the above example, the Customer_SID column in the Order table is a foreign key pointing to the SID column in the Customer table.

Below we show examples of how to specify the foreign key when creating the ORDER table:

MySQL:
CREATE TABLE Order
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references Customer(SID));

Oracle:
CREATE TABLE Order
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references Customer(SID),
Amount double);

SQL Server:
CREATE TABLE Order
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references Customer(SID),
Amount double);

Below are examples for specifying a primary key by altering a table. This assumes that the Orders table has been created, and the foreign key has not yet been put in:

MySQL:
ALTER TABLE orders
ADD FOREIGN KEY (customer_sid) REFERENCES customer(sid);

Oracle:
ALTER TABLE orders
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES customer(sid);

SQL Server:
ALTER TABLE orders
ADD FOREIGN KEY (customer_sid) REFERENCES customer(sid);