Open source training & tutorials from experienced, passionate people
By A Web Design
A primary key is one or more column(s) in a table used to uniquely identify each row of data in the table.
If a table has a single column as its Primary Key this is known as a Simple key.
If a table has multiple columns as its Primary Key this is known as a Composite key.
None of the columns that are part of the primary key can contain a NULL value.
A table can have only one primary key.
NOTE:
OR
The MySQL database engine will create an independent table, under its exclusive control, which will hold Primary Key data. This is a system table, completely independent of the table that was created to hold user data for which the Primary Key was defined.
Hence, when creating a table with a Primary Key, although there is only single CREATE Table SQL syntax written, two unique tables strongly interlinked, and controlled by the MySQL DBMS engine come into existence.
The Primary Key table cannot be accessed by any user. It’s content is manipulated automatically and exclusively by the MySQL RDBMS engine.
A primary key column in a table has special attributes:
A Primary key constraint can be defined at various levels:
The primary key constraint specified immediately after the table column definition is called column-level Primary Key definition.
The following is the syntax for creating a primary key at column level.
Syntax:
<Column Name> <datatype>(<size>) Primary Key
Example:
CREATE TABLE Clientmaster (
Client_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(25),
Age VARCHAR(10),
Address VARCHAR(25),
Company VARCHAR(15)
);
Diagram 1
In the above example, the Client_ID column of the Clientmaster table is set as its Primary key.
While defining the column, it is set as the Primary key column.
After creating the table, you can have a look on the table by using desc command.
The following is the syntax for describing the table structure, such as its column datatype and other attributes.
<system prompt> desc <Tablename>;
The Primary Key constraint specified after all the columns of a table are defined is called a table-level, Primary Key definition.
The following is the syntax for creating a primary key at table level.
Syntax:
Primary key (<Column Name>, <Column Name>)
Example:
CREATE TABLE Clientmaster(
Serial_NO VARCHAR(10),
Client_ID VARCHAR(10),
Name VARCHAR(25),
Age VARCHAR(10),
Address VARCHAR(25),
Company VARCHAR(15),
Primary key(Client_ID, Serial_NO)
);
Diagram 2
In the above example, Serial_NO and Client_ID column of Clientmaster table is set as Primary key.
After all the table columns have been defined, the column to be used as its Primary key is set.
After creating the table, you can have a look at the table’s structure by using the desc command.
Subsequent to creating a table, you can add a Primary Key constraint to it at any time. This is done using the ALTER TABLE statement.
The following is the syntax for adding a primary key using Alter Table statement.
Syntax:
ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> <Constraint type> (<Constraint condition>);
Example:
ALTER TABLE Clientmaster ADD CONSTRAINT client_id_pk Primary Key(Client_ID);
Diagram 3
You can have a look at the table structure using desc command.
NOTE: There are issues with creating a Primary key on a table subsequently if the table is populated with data. If any of the data held in the table columns on which the Primary Key is being created fails the Primary Key Constraints which are NULL and UNIQUE, the Primary Key creation will fail.
It’s really best to export the table data to a delimited ASCII file, alter its structure by adding its Primary Key and then importing the table data back to the structure. Those records whose data fails the Primary Key constraint will not be imported, the others will.
Subsequently, the table data that has been rejected can be corrected in the ASCII file and then imported in to the structure with its Primary Key.
If the table is completely empty, then there are no issues whatsoever, however if the table is empty why not consider dropping the table and recreating the table with its Primary Key rather than altering its structure.
Foreign keys represent relationships between table data (i.e. the records held in the table). A foreign key is a column (or a group of columns) whose values (i.e. the data they hold) are identical to those of a primary key of some other table.
The table in which the foreign key is defined is called a Foreign table or Detail table. The table that defines the primary referenced by the foreign key is called the Primary table or Master table.A Foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement
The master table can be referenced in the foreign key definition by using the clause REFERENCESfollowed by TableName(ColumnName) when the Tablename is the name of the table which has the Primary Key referenced by the Foreign key, and the ColumnName is the Primary Key column name.
Hence, the Foreign Key constraint establishes a relationship between the records (i.e. column data) of a Master and a Detail table.
A Foreign key is defined at table level.
The following is the syntax for creating a foreign key at table level.
Syntax:
FOREIGN KEY (<Column Name> [,<Column Name>] )
REFERENCES <Table Name> [(<Column Name>,<Column Name>)
Example:
CREATE TABLE Productdetail (
Serial_NO Int(10),
ID VARCHAR(10),
Product VARCHAR(25),
Quantity Int(10),
Price Int(15),
Foreign Key (ID) REFERENCES Clientmaster(Client_ID)
);
NOTE: The column which is being defined as a Foreign Key will only link to a primary key in a parent table whose width and datatype are identical.
Diagram 4
In the above example, you have created the table and set ID as its foreign key. The value MUL in the column Key indicates that this is a Foreign Key column.
If while creating the table, you have forgotten to add it’s Foreign Key constraint, you can easily add the constraint using the ALTER TABLE statement.
The following is the syntax for adding a Foreign Key using the Alter Table statement.
Syntax:
ALTER TABLE <ChildTable name> ADD CONSTRAINT <Constraint name> <Constraint type> (<Constraint condition>) References <ParentTable name> (<ColumnName>);
Example:
ALTER TABLE Productdetail ADD CONSTRAINT id_fk Foreign Key(ID) References Clientmaster(Client_ID);
Diagram 5
You can have a look on the table by using DESC command.
Please wait...