The primary key of a table uniquely identifies each row in a table. In a table, only one field can be assigned as a primary key. The primary key will be guaranteed as unique in the entire table, that is no two rows in a table will have the same primary key. The foreign key field of a table is used to match the primary key field of another table.
To make the concept clear, lets see an example. Consider the database of a library management system. It contains a table "authors" which contains a list of all the authors. It contains two fields, a_id and a_name. Here a_id represents the author ID and a_name represents the author name. In this table, a_id will be the primary key since no two authors will have the same id. (Note: Two authors may have same name).
Here is the MySQL command to create the "authors" table.
CREATE TABLE authors
(
a_id int NOT NULL,
a_name varchar(200) NOT NULL,
PRIMARY KEY (a_id)
)
The database contains another table called "books" which lists all the books available in the library. It has three fields, b_id, b_name and a_id. Here b_id represents the book ID, b_name represents the book name and the a_id represents the author id of the author of that book. In this table, b_id will be the primary key since no two books will have the same book id. The field a_id in the "books" table will contain values from the a_id of "authors" table. So the a_id of "books" table is a foreign key. For a value to be given in the a_id of "books" table, it should already exist in a_id of "authors" table. It means, the author should be added to the table before the book can be added.
Here is the MySQL command to create "books" table.
CREATE TABLE books
(
b_id int NOT NULL,
b_name varchar(200) NOT NULL,
a_id int,
PRIMARY KEY (b_id),
FOREIGN KEY (a_id) REFERENCES authors(a_id)
)
Defining a foreign key prevents actions that destroy links between the two tables and also prevents entering invalid data in the foreign key field.
To make the concept clear, lets see an example. Consider the database of a library management system. It contains a table "authors" which contains a list of all the authors. It contains two fields, a_id and a_name. Here a_id represents the author ID and a_name represents the author name. In this table, a_id will be the primary key since no two authors will have the same id. (Note: Two authors may have same name).
Here is the MySQL command to create the "authors" table.
CREATE TABLE authors
(
a_id int NOT NULL,
a_name varchar(200) NOT NULL,
PRIMARY KEY (a_id)
)
The database contains another table called "books" which lists all the books available in the library. It has three fields, b_id, b_name and a_id. Here b_id represents the book ID, b_name represents the book name and the a_id represents the author id of the author of that book. In this table, b_id will be the primary key since no two books will have the same book id. The field a_id in the "books" table will contain values from the a_id of "authors" table. So the a_id of "books" table is a foreign key. For a value to be given in the a_id of "books" table, it should already exist in a_id of "authors" table. It means, the author should be added to the table before the book can be added.
Here is the MySQL command to create "books" table.
CREATE TABLE books
(
b_id int NOT NULL,
b_name varchar(200) NOT NULL,
a_id int,
PRIMARY KEY (b_id),
FOREIGN KEY (a_id) REFERENCES authors(a_id)
)
Defining a foreign key prevents actions that destroy links between the two tables and also prevents entering invalid data in the foreign key field.
No comments:
Post a Comment