CREATE TABLE Students (
StudentID INTEGER PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20),
HomePhone varchar(16));
You don't have to specify the primary key at the same
time you are creating the column. Before the end of the statement that
creates the table, you can enter name of the column and specify PRIMARY
KEY. To do this, on a separate line, type PRIMARY KEY() and, in
the parentheses, enter the name of the column that will serve as the
primary key. Here is an example:
CREATE TABLE Students (
StudentID INTEGER NOT NULL,
FirstName varchar(20),
LastName varchar(20),
HomePhone varchar(16),
PRIMARY KEY(StudentID));
|
Practical
Learning: Creating a Tables With a Primary Key |
|
Foreign Keys
|
In our introduction to relationships, we illustrated a
table that could be used to enter a customer's information and the car he
wants to rent:
We also mentioned that it was not effective to put
these two categories of information, namely the customer and the car's, in
the same list. The reason is that the same customer may come at different
times to rent different types of cars and the same car is regularly rented
by different customers. To reduce the likelihood of mistakes, you should
separate these two categories, each in its own list:
This time, if you keep these two lists separate, when
it is time to rent a car to a customer, you can use another list that
allows the clerk to select the name of the customer, followed by the car
she wants to rent:
To make this scenario work, there must be a column in
the Rental Order list that represents the customers: this would be the
Customer column in our illustration. The column that belongs to the Rental
Order list but is used to represent the customers is called a foreign key.
This column behaves like an ambassador who is not a citizen of the country
where he works but instead represents his native country.
Because a foreign key is used to represent a table
other than the one where it resides, the table that the foreign key
represents must have a primary key that would insure the uniqueness of
records in the original table. The table that holds the necessary values
and that has the primary key can be referred to as the parent table. In
the above illustration, the Customers table is the parent. The table that
holds the foreign key is referred to as the child table, which is the case
for the Rental Orders list of our illustration.
To create a foreign key, you can start by adding the
necessary column in the table that will need or use it. There are rules
and suggestions you should or must follow. As a suggestion, the name of
the column used as the foreign key should be the same as the primary key of the
table it represents. As a rule, the data type of the primary key of the
parent table must be the same as the data type of the foreign key.
To create a foreign
key, when creating the table, before the closing comma of the name of a
column (if the column is not the last in the table) or the closing
parenthesis of the table (if the column is the last), you can type REFERENCES
followed by the name of the parent table with parentheses. In the
parentheses of the name of the parent table, enter the name of the primary
key column. Here is an example:
mysql> CREATE TABLE Departments (
-> DepartmentID INTEGER AUTO_INCREMENT NOT NULL,
-> Department varchar(50),
-> PRIMARY KEY(DepartmentID));
Query OK, 0 rows affected (0.19 sec)
mysql> CREATE TABLE Employees (
-> EmployeeID INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
-> FirstName varchar(20),
-> MI char(1),
-> LastName varchar(20),
-> HourlySalary decimal(6,2),
-> DateHired DATE,
-> DepartmentID INTEGER REFERENCES Departments(DepartmentID));
Query OK, 0 rows affected (0.41 sec)
mysql>
When you create a table like this, the interpreter
would know that, in the Employees table, the DepartmentID column is a
foreign key. Still, if you want to indicate that the column will be
used as a foreign key, you can formally specify it. To do this, on one
line, first create the column by specifying its name, its data type, and
other options related to a column. Then, on another line, type
FOREIGN KEY() REFERENCES ParentTable(ParentPrimaryKey)
In the parentheses of the FOREIGN KEY
expression, enter the name of the column that was created and that would
serve as the foreign key.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Practical
Learning: Creating a Foreign Key |
CREATE TABLE Countries ( CountryID integer auto_increment not null, DepartmentID integer not null, CountryName varchar(60), CountryArea varchar(40), CountryPopulation varchar(40), CountryCode char(2), PRIMARY KEY(CountryID), FOREIGN KEY(DepartmentID) REFERENCES Continents(ContinentID));
No comments:
Post a Comment