Description

The SQL keyword PRIMARY KEY is a constraint used to define a column (or a group of columns) that accepts unique values on a table.

  • A table can have only one primary key.
  • A primary key can consist of one or more columns from a table.
  • Always make sure the columns included in a PRIMARY KEY must be defined as NOT NULL columns.

CREATE TABLE With PRIMARY KEY

The below SQL creates a new table, with the "user_id" column as a PRIMARY KEY.

-- Syntax on MySQL database system
CREATE TABLE users (
    user_id INT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    age INT,
    PRIMARY KEY (user_id)
);
-- Syntax on SQL Server or Oracle or MS Access database systems
CREATE TABLE users (
    user_id INT NOT NULL PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    age INT
);

ALTER TABLE With PRIMARY KEY

The below SQL alters an existing table definition to set the "user_id" column as a PRIMARY KEY.

 

ALTER TABLE users
ADD PRIMARY KEY (user_id);

The below SQL alters an existing table definition to set the combination of "user_id" and "user_name" columns as a PRIMARY KEY.

ALTER TABLE users
ADD CONSTRAINT PK_User PRIMARY KEY (user_id, user_name);

DROP PRIMARY KEY

The below SQL deletes an existing PRIMARY KEY constraint from a table.

-- Syntax for MySQL database system
ALTER TABLE users
DROP PRIMARY KEY;
-- Syntax for SQL Server, Oracle, or MS Access database systems
ALTER TABLE users
DROP CONSTRAINT PK_User;

Related Links