SQLite - PRIMARY KEY

A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Note: You would use these concepts while creating database tables.

Create Primary Key

Here is the syntax to define ID attribute as a primary key in a COMPANY table.

CREATE TABLE COMPANY(
   ID   INT       PRIMARY KEY     ,
   NAME          TEXT     NOT NULL,
   AGE  INT              NOT NULL UNIQUE,
   ADDRESS  CHAR (25) ,
   SALARY   REAL  ,       
   
);

To create a PRIMARY KEY constraint on the "ID" column when COMPANY table already exists, use the following SQLite syntax −

ALTER TABLE COMPANY ADD PRIMARY KEY (ID);

For defining a PRIMARY KEY constraint on multiple columns, use the following SQLite syntax −

CREATE TABLE COMPANY(
   ID   INT       PRIMARY KEY     ,
   NAME          TEXT     NOT NULL,
   AGE  INT              NOT NULL UNIQUE,
   ADDRESS  CHAR (25) ,
   SALARY   REAL  ,       
   
);

To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when COMPANY table already exists, use the following SQLite syntax −

ALTER TABLE COMPANY 
   ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

Delete Primary Key

You can clear the primary key constraints from the table, Use syntax −

ALTER TABLE COMPANY DROP PRIMARY KEY ;
Advertisements