Creating a foreign key in MySQL is a part of referential integrity in the database. A foreign key connects to tables. A foreign key is used in conjunction with a primary key, which is the main record for the dataset. For instance, a primary key could be used on a customer's table. The customer ID is a unique field that distinctly identifies the customer. A foreign key is placed on the orders table, which connects the customer to his order.
Before creating a foreign key, a table that holds a primary key field needs to be created for referential integrity. In this example, creating the table for customers and orders can be accomplished using the MySQL command line. The syntax for creating a table is below:
CREATE TABLE customer ( CustId INT NOT NULL, first_name VARCHAR(30), PRIMARY KEY (CustId) ) TYPE = INNODB GO
In this example, a table is created using the \"create table\" keyword statement. If a primary key is undetermined, the programmer can leave out the primary key statement until he knows on which field to place the key. However, it's important for tables to contain a primary key, because these elements speed up performance. In this example, a primary key is created on the \"CustId\" field. The CustId is used to distinctly identify the customer. Additionally, when assigning a primary key to a table, it must be unique.
Now that the primary key is created, a foreign key is created on the orders table. Again, if the database developer is unsure of the foreign key to use at the time of table creation, it can be added later. The following code creates an orders table with a foreign key that points to the customers table:
CREATE TABLE order ( Orderid INT NOT NULL, cost INT, CustId INT NOT NULL, PRIMARY KEY(OrderId), INDEX (CustId), FOREIGN KEY (CustId) REFERENCES customer (CustId) ) TYPE = INNODB GO
Notice that a primary key was created for this table as well using the OrderId, which is also a unique value. The statement that defines the foreign key is the last one in the table syntax. It defines the foreign key and tells the database where its primary key is located. In this example, the CustId field in the orders table references the CustId in the customers table.