{"id":347,"date":"2015-12-05T21:22:51","date_gmt":"2015-12-05T14:22:51","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=347"},"modified":"2022-04-03T15:05:15","modified_gmt":"2022-04-03T08:05:15","slug":"sqlite-primary-key","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-primary-key\/","title":{"rendered":"SQLite Primary Key"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use SQLite PRIMARY KEY<\/code> constraint to define a primary key for a table.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQLite primary key<\/h2>\r\n\r\n\r\n\r\n

A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.<\/p>\r\n\r\n\r\n\r\n

SQLite allows you to define primary key in two ways:<\/p>\r\n\r\n\r\n\r\n

First, if the primary key has only one column, you use the PRIMARY KEY<\/code> column constraint to define the primary key as follows:<\/p>\r\n\r\n\r\n

CREATE TABLE table_name(\r\n   column_1 INTEGER NOT NULL<\/span> PRIMARY KEY,\r\n   ...\r\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Second, in case primary key consists of two or more columns, you use the PRIMARY KEY<\/code> table constraint to define the primary as shown in the following statement.<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> table_name(\r\n   column_1 INTEGER<\/span> NOT<\/span> NULL<\/span>,\r\n   column_2 INTEGER<\/span> NOT<\/span> NULL<\/span>,\r\n   ...\r\n   PRIMARY KEY<\/span>(column_1,column_2,...)\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In SQL standard, the primary key column must not contain NULL<\/code> values. It means that the primary key column has an implicit NOT NULL<\/a><\/code> constraint.<\/p>\r\n\r\n\r\n\r\n
However, to make the current version of SQLite compatible with the earlier version, SQLite allows the primary key column to contain NULL<\/code> values.<\/p>\r\n\r\n\r\n\r\n
SQLite primary key and rowid table<\/h2>\r\n\r\n\r\n\r\n
When you create a table<\/a> without specifying the WITHOUT ROWID<\/code> option, SQLite adds an implicit column called rowid<\/code> that stores 64-bit signed integer. The rowid<\/code> column is a key that uniquely identifies the rows in the table. Tables that have rowid<\/code> columns are called rowid<\/code> tables.<\/p>\r\n\r\n\r\n\r\n
If a table has the primary key that consists of one column, and that column is defined as INTEGER<\/code> then this primary key column becomes an alias<\/em> for the rowid<\/code> column.<\/p>\r\n\r\n\r\n\r\n
Notice that if you assign another integer type such as BIGINT<\/code> and UNSIGNED INT<\/code> to the primary key column, this column will not be an alias for the rowid<\/code> column.<\/p>\r\n\r\n\r\n\r\n
Because the rowid<\/code> table organizes its data as a B-tree, querying and sorting data of a rowid<\/code> table are very fast. It is faster than using a primary key which is not an alias of the rowid<\/code>.<\/p>\r\n\r\n\r\n\r\n
Another important note is that if you declare a column with the INTEGER<\/code> type and PRIMARY KEY DESC<\/code> clause, this column will not become an alias for the rowid<\/code> column:<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> table<\/span>(\r\n   pk INTEGER<\/span> PRIMARY KEY<\/span> DESC<\/span>,\r\n   ...\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Creating SQLite primary key examples<\/h2>\r\n\r\n\r\n\r\n
The following statement creates a table named countries<\/code> which has country_id<\/code> column as the primary key.<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> countries (\r\n   country_id INTEGER<\/span> PRIMARY KEY<\/span>,\r\n   name<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Because the primary key of the countries<\/code> table has only one column, we defined the primary key using PRIMARY KEY<\/code> column constraint.<\/p>\r\n\r\n\r\n\r\n
It is possible to use the PRIMARY KEY<\/code> table constraint to define the primary key that consists of one column as shown in the following statement:<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> languages (\r\n   language_id INTEGER<\/span>,\r\n   name<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>,\r\n   PRIMARY KEY<\/span> (language_id)\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
However, for tables that the primary keys consist of more than one column, you must use PRIMARY KEY<\/code> table constraint to define primary keys.<\/p>\r\n\r\n\r\n\r\n
The following statement creates the country_languages<\/code> table whose primary key consists of two columns.<\/p>\r\n\r\n\r\n
CREATE TABLE country_languages (\r\n\tcountry_id INTEGER NOT NULL<\/span>,\r\n\tlanguage_id INTEGER NOT NULL<\/span>,\r\n\tPRIMARY KEY (country_id, language_id),\r\n\tFOREIGN KEY (country_id) REFERENCES countries (country_id) \r\n            ON DELETE CASCADE ON UPDATE NO ACTION,\r\n\tFOREIGN KEY (language_id) REFERENCES languages (language_id) \r\n            ON DELETE CASCADE ON UPDATE NO ACTION\r\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Adding SQLite primary key example<\/h2>\r\n\r\n\r\n\r\n
Unlike other database systems e.g., MySQL and PostgreSQL, you cannot use the ALTER TABLE<\/a><\/code> statement to add a primary key to an existing table.<\/p>\r\n\r\n\r\n\r\n
You need to follow these steps to work around the limitation:<\/p>\r\n\r\n\r\n\r\n
  1. First, set the foreign key<\/a> constarint check off.<\/li>
  2. Next, rename the table to another table name (old_table)<\/li>
  3. Then, create a new table (table) with exact structure of the table that you have been renamed.<\/li>
  4. After that, copy data from the old_table to the table.<\/li>
  5. Finally, turn on the foreign key constraint check on<\/li><\/ol>\r\n\r\n\r\n\r\n
    See the following statements:<\/p>\r\n\r\n\r\n
    PRAGMA<\/span> foreign_keys=off<\/span>;\r\n\r\nBEGIN<\/span> TRANSACTION<\/span>;\r\n\r\nALTER<\/span> TABLE<\/span> table<\/span> RENAME<\/span> TO<\/span> old_table;\r\n\r\n-- define the primary key constraint here<\/span>\r\nCREATE<\/span> TABLE<\/span> table<\/span> ( ... );\r\n\r\nINSERT<\/span> INTO<\/span> table<\/span> SELECT<\/span> * FROM<\/span> old_table;\r\n\r\nCOMMIT<\/span>;\r\n\r\nPRAGMA<\/span> foreign_keys=on<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    The BEGIN TRANSACTION<\/code> starts a new transaction. It ensures that all subsequent statements execute successfully or nothing executes at all.<\/p>\r\n\r\n\r\n\r\n
    The COMMIT<\/code> statement commits all the statements.<\/p>\r\n\r\n\r\n\r\n
    Let’s create a table name cities<\/code> without a primary key.<\/p>\r\n\r\n\r\n
    CREATE<\/span> TABLE<\/span> cities (\r\n   id<\/span> INTEGER<\/span> NOT<\/span> NULL<\/span>,\r\n   name<\/span> text<\/span> NOT<\/span> NULL<\/span>\r\n);\r\n\r\nINSERT<\/span> INTO<\/span> cities (id<\/span>, name<\/span>)\r\nVALUES<\/span>(1<\/span>, 'San Jose'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    In order to add the primary key to the cities<\/code> table, you perform the following steps:<\/p>\r\n\r\n\r\n
    PRAGMA<\/span> foreign_keys=off<\/span>;\r\n\r\nBEGIN<\/span> TRANSACTION<\/span>;\r\n\r\nALTER<\/span> TABLE<\/span> cities RENAME<\/span> TO<\/span> old_cities;\r\n\r\nCREATE<\/span> TABLE<\/span> cities (\r\n   id<\/span> INTEGER<\/span> NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>,\r\n   name<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>\r\n);\r\n\r\nINSERT<\/span> INTO<\/span> cities \r\nSELECT<\/span> * FROM<\/span> old_cities;\r\n\r\nDROP<\/span> TABLE<\/span> old_cities;\r\n\r\nCOMMIT<\/span>;\r\n\r\nPRAGMA<\/span> foreign_keys=on<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    If you use SQLite GUI tool, you can use the following statement to show the table’s information.<\/p>\r\n\r\n\r\n
    PRAGMA<\/span> table_info([cities]);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    In this tutorial, you have learned use the SQLite PRIMARY KEY<\/code> constraint to define the primary key for a table.<\/p>\r\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
    Try It<\/a><\/p>\r\n\r\n\r\n\r\n
    Try It<\/a><\/p>\r\n\r\n\r\n\r\n
    Try It<\/a><\/p>\r\n\r\n\r\n\r\n
    Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n