110 SQL PRIMARY KEY

The current webpage explains the SQL PRIMARY KEY constraint. Here’s a concise summary:

• The PRIMARY KEY constraint is used to uniquely identify each record in a SQL table.
• Primary keys must have unique values and cannot contain NULL values.
• Each table can have only one primary key, which may be a single column or a combination of columns.

Key usage examples:

• Define a primary key during table creation:
  • In MySQL: PRIMARY KEY (ID)
  • In SQL Server/Oracle/MS Access: Define ID INT NOT NULL PRIMARY KEY
• To set a primary key on multiple columns:
  CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)
• Add a primary key to an existing table using ALTER TABLE.
• Drop a primary key using:
  • MySQL: ALTER TABLE Persons DROP PRIMARY KEY;
  • Other databases: ALTER TABLE Persons DROP CONSTRAINT PK_Person;

Important Note:
Columns that will become primary keys must be declared as NOT NULL when using ALTER TABLE to add the key.

The page also provides code examples for each case and emphasizes that primary keys enforce both uniqueness and non-nullability in relational databases.[1]

1

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each record in a table.

Primary keys must contain unique values, and cannot contain NULL values.

Each table can have only ONE primary key. The primary key can be a single column or a combination of columns.

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    PRIMARY KEY (ID)  
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (  
    ID int NOT NULL PRIMARY KEY,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int  
);

To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)  
);

Note: In the example above there is one PRIMARY KEY (PK_Person). However, the value of the primary key is made up of two columns (ID + LastName).

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons  
ADD PRIMARY KEY (ID);

To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons  
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared with NOT NULL, when the table was first created.

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;