Basic about Primary Keys – using and example

This SQL tutorial explains how to create and drop a main key in SQL with syntax and examples.

What is a primary key in SQL?

In SQL, a primary key is a single area or mixture of fields that uniquely defines a record. None of the fields that are phase of the predominant key can incorporate a NULL value. A table can have solely one predominant key.

You use both the CREATE TABLE statement or the ALTER TABLE statement to create a principal key in SQL.

Create Primary Key (CREATE TABLE statement)

A essential key can be created when you execute a CREATE TABLE announcement in SQL.

Syntax

The syntax to create a most important key using the CREATE TABLE statement in SQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

OR

CREATE TABLE table_name
(
  column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
  column2 datatype [ NULL | NOT NULL ],
  ...
);

table_name The title of the table that you desire to create. column1, column2 The columns that you want to create in the table. constraint_name The title of the foremost key. pk_col1, pk_col2, … pk_col_n The columns that make up the principal key.

Example

Let’s appear at an example of how to create a essential key using the CREATE TABLE statement in SQL. We will start with a very easy one where our most important key consists of simply one column.

For example:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

In this example, we’ve got created a foremost key on the suppliers table known as suppliers_pk. It consists of only one column – the supplier_id column.

We ought to have used the alternate syntax and created this identical major key as follows:

CREATE TABLE suppliers
( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

Both of these syntaxes are legitimate when developing a most important key with solely one field.

If you create a primary key that is made up of 2 or greater columns, you are limited to the usage of only the first syntax the place the main key is defined at the cease of the CREATE TABLE statement.

For example:

CREATE TABLE contacts
( last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25) NOT NULL,
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)
);

This example creates a essential key referred to as contacts_pk that is made up of a mixture of the last_name and first_name columns. So each mixture of last_name and first_name ought to be special in the contacts table.

Create Primary Key (ALTER TABLE statement)

If your desk already exists and you wish to add a foremost key later, you can use the ALTER TABLE announcement to create a primary key.

Syntax

The syntax to create a predominant key the usage of the ALTER TABLE announcement in SQL is:

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
    PRIMARY KEY (column1, column2, ... column_n);

table_name The identify of the table to modify. This is the desk that you desire to add a principal key to. constraint_name The title of the predominant key. column1, column2, … column_n The columns that make up the main key.

Example

Let’s seem at an instance of how to create a important key using the ALTER TABLE declaration in SQL. So say, we already have a suppliers desk created in our database. We should add a predominant to the suppliers table with the following ALTER TABLE statement:

ALTER TABLE suppliers
  ADD CONSTRAINT suppliers_pk 
    PRIMARY KEY (supplier_id);

In this example, we have created a important key on the current suppliers desk known as suppliers_pk. It consists of the supplier_id column.

We may want to additionally create a fundamental key with greater than one discipline as in the instance below:

ALTER TABLE suppliers
  ADD CONSTRAINT suppliers_pk
    PRIMARY KEY (supplier_id, supplier_name);

This instance would created a major key known as suppliers_pk that is made up of a mixture of the supplier_id and supplier_name columns.

Drop Primary Key

In SQL, you can drop a primary key the use of the ALTER TABLE statement.

Syntax

The syntax to drop a primary key in SQL is:

ALTER TABLE table_name
  DROP PRIMARY KEY;

table_name The title of the desk to modify. This is the table whose fundamental key you wish to drop.

Example

Let’s look at an instance of how to drop a major key the usage of the ALTER TABLE announcement in SQL.

ALTER TABLE suppliers
  DROP PRIMARY KEY;

In this example, we’ve dropped the fundamental key on the suppliers table. We do now not want to specify the title of the predominant key as there can solely be one essential key on a table.