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:

snippet
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.

snippet
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:

snippet
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.

    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.

    Syntax:

    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.

    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.


    Next TopicSQLite Foreign Key




Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents