Summary: in this tutorial, you will learn how to use MySQL primary key constraint to create the primary key for a table.
A primary key is a column or a set of columns that uniquely identifies each row in the table. The primary key follows these rules:
Jun 21, 2010 losing primary key during import export operation – Learn more on the SQLServerCentral forums. Losing primary key during import export operation. But if you want to migrate the data.
NULL
values. Any attempt to insert or updateNULL
to primary key columns will result in an error. Note that MySQL implicitly adds a NOT NULL
constraint to primary key columns.Because MySQL works faster with integers, the data type of the primary key column should be the integer e.g., INT,
BIGINT
. And you should ensure sure that value ranges of the integer type for the primary key are sufficient for storing all possible rows that the table may have.
A primary key column often has the AUTO_INCREMENT
attribute that automatically generates a sequential integer whenever you insert a new row into the table.
When you define a primary key for a table, MySQL automatically creates an index called PRIMARY
.
PRIMARY KEY
examplesThe PRIMARY KEY
constraint allows you to define a primary key of a table when you create or alter table.
PRIMARY KEY
constraint in CREATE TABLE
Typically, you define the primary key for a table in the CREATE TABLE
statement.
If the primary key has one column, you can use the PRIMARY KEY
constraint as a column constraint:
When the primary key has more than one column, you must use the PRIMARY KEY
constraint as a table constraint.
In this syntax, you separate columns in the column_list
by commas (,).
The PRIMARY KEY
table constraint can be used when the primary key has one column:
The following example creates a table named users
whose primary key is the user_id
column:
This statement creates the roles
table that has the PRIMARY KEY
constraint as the table constraint:
In case the primary key consists of multiple columns, you must specify them at the end of the CREATE TABLE
statement. You put a comma-separated list of primary key columns inside parentheses followed the PRIMARY KEY
keywords.
The following example creates the user_roles
table whose primary key consists of two columns: user_id
and role_id
. It defines the PRIMARY KEY
constraint as the table constraint:
Note that the statement also created two foreign key constraints.
PRIMARY KEY
constraints using ALTER TABLE
If a table, for some reasons, does not have a primary key, you can use the ALTER TABLE
statement to add a primary key to the table as follows:
The following example adds the id
column to the primary key.
First, create the pkdemos
table without a primary key.
Second, add a primary key to the pkdemos
table using the ALTER TABLE
statement:
If you add a primary key to a table that already has data. The data in the column(s), which will be included in the primary key, must be unique and not NULL.
PRIMARY KEY
vs. UNIQUE KEY
vs. KEY
KEY
is the synonym for INDEX
. You use the KEY
when you want to create an index for a column or a set of columns that is not the part of a primary key or unique key.
Email Security using Public Key Cryptography. Anyone using Email that is concerned about the security of the data being transferred should use Public Key Encryption. There are several open source software tools like GnuPG and WinPt to accomplish these tasks. To encrypt a message so that nobody else but the receiver (bob) can view it you encrypt the message using Bob’s public key. The public key allows you to encrypt but not to decrypt. Without a public key you cannot encrypt a message, so there is no worry about encrypting a message that nobody can decrypt. Generate email encryption public key.
A UNIQUE
index ensures that values in a column must be unique. Unlike the PRIMARY
index, MySQL allows NULL
values in the UNIQUE
index. In addition, a table can have multiple UNIQUE
indexes.
Suppose that email
and username
of users in the users
table must be unique. To enforce thes rules, you can define UNIQUE
indexes for the email
and username
columns as the following statement:
Add a UNIQUE
index for the username
column:
Add a UNIQUE
index for the email
column:
In this tutorial, you have learned how to create a primary key for a new table or add a primary key to an existing table.
A column or columns is called primary key (PK) that uniquely identifies each row in the table.
If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.
When multiple columns are used as a primary key, it is known as composite primary key.
In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.
Inn terms of performance, less data means the database can process faster.
When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.
The main advantage of this uniqueness is that we get fast access.
In oracle, it is not allowed for a primary key to contain more than 32 columns.
The following SQL command creates a PRIMARY KEY on the 'S_Id' column when the 'students' table is created.
MySQL:
SQL Server, Oracle, MS Access:
MySQL, SQL Server, Oracle, MS Access:
Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id and LastName).
When table is already created and you want to create a PRIMARY KEY constraint on the 'S_Id' column you should use the following SQL:
Primary key on one column:
Primary key on multiple column:
When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first created).
If you want to DROP (remove) a primary key constraint, you should use following syntax:
MySQL:
SQL Server / Oracle / MS Access: