FOREIGN KEY Constraint SQL

The FOREIGN KEY Constraint is generally placed at the time of table creation. The FOREIGN KEY establishes a relationship between a column within the table and a column within another table. For the sake of clarity, these columns should generally have the same name. The column containing the FOREIGN KEY can only contain data that is also contained within the associated column in the separate table.

For example if we wanted to set up a table to keep track of the number of times monopoly squares are landed on during a game, we might set up a table containing the 38 squares, and then another table (move_log) that logs each move and contains the square_id from the monopoly_squares table as a FOREIGN KEY. This would ensure a relationship between the two columns and would not allow any squares within column that were not also listed in the monopoly_squares table. This sets up the monopoly_squares table in mySQL:

CREATE TABLE monopoly_squares(
square_id INT NOT NULL,
square_name VARCHAR (100),
PRIMARY KEY (square_id)
)

The FOREIGN KEY relationship with the move_log table is established here:

CREATE TABLE move_log(
ml_id INT NOT NULL,
dice_roll INT NOT NULL,
PRIMARY KEY (ml_id),
FOREIGN KEY (square_id) REFERENCES monopoly_squares(square_id)
)

In other SQL applications the line will look something like this:

square id INT FOREIGN KEY REFERENCES monopoly_squares(square_id)

The FOREIGN KEY Constraint can also be named:

CONSTRAINT move_square_const
FOREIGN KEY (square_id)
REFERENCES monopoly_squares(square_id)

CONSTRAINTs can be DROPped or ADDed using the ALTER TABLE statement:

ALTER TABLE move_log
ADD CONSTRAINT move_square_const
FOREIGN KEY (square_id)
REFERENCES monopoly_squares(square_id)

The FOREIGN KEY is a vital tool in "normalization", which is an effort to minimize redundancy in the database. It often results in more and smaller tables.

< DROP Statement SQL | GROUP BY SQL >


Send notes in disappearing ink!

Interesting Pages