SQL Primary Key
SQL PRIMARY KEY Constraint
Every record in a table is uniquely identified by the PRIMARY KEY constraint.
Primary keys cannot have NULL values in them; instead, they must have UNIQUE values.
There can only be ONE primary key per table, and it can include one or more columns (fields) in the key.
SQL PRIMARY KEY on CREATE TABLE
The “Persons” table is created using the following SQL, which establishes a PRIMARY KEY on the “ID” column:
MySQL:
----- EXMPLE MUKAVA -----
SQL Server / Oracle / MS Access:
----- EXMPLE MUKAVA -----
MySQL / SQL Server / Oracle / MS Access:
----- EXMPLE MUKAVA -----
Note: The PK_Person primary key is the only one present in the sample above. But the primary key’s VALUE consists of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE
When the table has already been established, use the following SQL to create a PRIMARY KEY constraint on the “ID” column:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
Use the following SQL syntax to define a primary key constraint on several columns and to allow naming of the constraint:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: The primary key column(s) must have been declared to not contain NULL values (when the table was first formed) in order to add a primary key using ALTER TABLE.
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
