SQLite Primary Keys

SQLite primary key is a simple field or a combination of fields which is used to uniquely define a record. A table can have only one primary key.

A primary key should not be a NULL value.

Create Primary Key

Primary key is generally created at the time of creating the table. We define the primary key while executing CREATE TABLE statement.

Syntax:

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)
);

Parameter explanation:

• table_name: It specifies the name of the table that you want to create.
• column1, column2: It specifies the columns that you want to create in the table.
• constraint_name: It specifies the name of the primary key.
• pk_col1, pk_col2, ... pk_col_n: It specifies the columns that make up the primary key.

Example:

Create a table "WORKERS" where worker_id is primary key.

CREATE TABLE WORKERS
( worker_id INTEGER PRIMARY KEY,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
join_date DATE
);

Add Primary Key

It is used when you have not defined primary key at CREATE TABLE statement and you have to add a primary key later.

You cannot use ALTER TABLE statement to create a primary key. Instead you have to create a new table with primary key and copy all data from old table.

Syntax:

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE table_name RENAME TO old_table;
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)
);
INSERT INTO table_name SELECT * FROM old_table;
COMMIT;
PRAGMA foreign_keys=on; 

Parameter explanation:

• Table_name: It specifies a new table in which includes the primary key.
• Old_table: It species the table which has to be changed.
• Constraint_name: It specifies the name of the primary_key.
• pk_col1, pk_col2, ... pk_col_n: Specifies the name of the columns which makes the primary key.

Example:

Create a table "employees" having no primary key.

CREATE TABLE employees
( employee_id INTEGER,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
hire_date DATE
);

Now, run the following command to make "employee_id" a primary key.

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE employees RENAME TO old_employees;
CREATE TABLE employees
(
employee_id INTEGER,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
hire_date DATE,
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
INSERT INTO employees SELECT * FROM old_employees;
COMMIT;
PRAGMA foreign_keys=on;

Now, it will rename your employees table into "old_employees" then create a new table "employees" and transfer all data from onld table to new table.

Now you can drop "old_employees" table.

DROP TABLE old_employees; 

---

Drop Primary Key



You cannot use ALTER TABLE statement to drop primary key. Instead, you must create a new table with the primary key removed and copy the data into this new table.

Syntax:

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE table_name RENAME TO old_table;
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
);
INSERT INTO table_name SELECT * FROM old_table;
COMMIT;
PRAGMA foreign_keys=on; 

Parameter explanation:

• table_name: It specifies the name of the table from which you want to remove the primary key from.
• old_table: This specifies the name of the original table that will be deleted after you have created the new table with the primary key removed.

Example:

Suppose, we have an existing table "engineers" with primary key "engineer_id" and we have to remove that primary key.

CREATE TABLE engineers
(engineer_id INTEGER,
engineerr_name VARCHAR NOT NULL,
address VARCHAR,
city VARCHAR,
CONSTRAINT engineers_pk PRIMARY KEY (engineer_id)
); 

Now, run the following command to drop the primary key.

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE engineers RENAME TO old_engineers;
CREATE TABLE engineers
(
engineer_id INTEGER,
engineer_name VARCHAR NOT NULL,
address VARCHAR,
city VARCHAR
);
INSERT INTO engineers SELECT * FROM old_engineers;
COMMIT;
PRAGMA foreign_keys=on;

Primary key is now dropped from the existing "engineers" table. But the original table is now renamed as "old_engineers".

Now drop the "old_engineers" table.

DROP TABLE old_engineers;

---

Next TopicSQLite Foreign Key



← prev next →