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.
Create a table "WORKERS" where worker_id is primary key.
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.
Create a table "employees" having no primary key.
Now, run the following command to make "employee_id" a primary key.
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 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.
Suppose, we have an existing table "engineers" with primary key "engineer_id" and we have to remove that primary key.
Now, run the following command to drop the primary key.
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.