Javatpoint Logo
Javatpoint Logo

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:

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.


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:

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




Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA