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.
Primary key is generally created at the time of creating the table. We define the primary key while executing CREATE TABLE statement.
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) );
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 );
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.
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;
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.
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.