How do I create a Foreign Key?

Question

Hi

I am working on my assignment. It has 8 tables. Each table has a primary key. What do i do to generate a foreign key to a table?

My reason for asking is that when I generate a primary key, a key symbol appears on the left.

What do i do to make something a foreign key?

Answer 1

Here's the MySQL docs for the subject -- you might wanna take a look at it. Basically, here's an example of what you do:

CREATE TABLE `table_1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    PRIMARY KEY (`id`)
)
CREATE TABLE `table_2` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `table1_id` INT(11) NOT NULL REFERENCES table_1(`id`)
)

This should basically make table_2.table1_id reference table_1.id as a foreign key.

Answer 2

The SQL to add a foreign key between table1 (the referencing table) and table2 (the primary table) is:

ALTER TABLE table1
ADD CONSTRAINT FOREIGN KEY table1_table2_FK 
    REFERENCES table2 (intId) ON (intTable2Id)

Answer 3

Your confusion is arising from the fact that you think a foreign key is a modified version of a primary key. It is not, it is a separate item.

For instance, CustomerID in the Customers table would have a primary key, but CustomerID in the Orders table would be a foreign key referencing (pointing back to) the primary key in the Customers table.

In the Customers table, the primary key serves to uniquely identify each customer. In the Orders table the foreign key on CustomerID serves to guarantee that each order belongs to an existing record from the customer table.

You can have the primary key without establishing any foreign keys, it will still serve it's role in identifying records. But you cannot have a foreign key without a primary key in a different table (or, in rare occurrences, in the same table) because the primary key name is part of the definition of the foreign key. And you can have as many foreign keys as you want in different tables (in rare cases in the same tables) pointing back to a single primary key.