![]() Let's look at an example of how to remove a primary key from an existing table in SQLite. old_table The name of the original table that will be left behind after you have created the new table with the primary key removed. This is the table that you wish to remove the primary key from. PRAGMA foreign_keys=on table_name The name of the table to modify. The syntax to drop a primary key from a table in SQLite is: PRAGMA foreign_keys=off Instead, you must create a new table with the primary key removed and copy the data into this new table. In SQLite, you can not use the ALTER TABLE statement to drop a primary key. You can drop the old_employees table once you have verified that your employees table and data are as expected. The original table will still exist in the database called old_employees. In this example, we've created a primary key on the employees table called employees_pk which consists of the employee_id column. INSERT INTO employees SELECT * FROM old_employees We could run the following commands: PRAGMA foreign_keys=off ĪLTER TABLE employees RENAME TO old_employees So say, we already have an employees table with the following definition: CREATE TABLE employeesĪnd we wanted to add a primary key to the employees table that consists of the employee_id. Let's look at an example of how to add a primary key to an existing table in SQLite. pk_col_n The columns that make up the primary key. constraint_name The name of the primary key. old_table The name of the original table that will be left behind after you have created the new table with the primary key added. This is the table that you wish to add a primary key to. INSERT INTO table_name SELECT * FROM old_table The syntax to add a primary key to a table in SQLite is: PRAGMA foreign_keys=off ĪLTER TABLE table_name RENAME TO old_table Instead, you must create a new table with the primary key and copy the data into this new table. If your table already exists and you wish to add a primary key later, you can not use the ALTER TABLE statement to create a primary key. So each combination of last_name and first_name must be unique in the customers table. This example creates a primary key on the customers table called customers_pk that is made up of a combination of the last_name and first_name columns. ![]() If you create a primary key that is made up of 2 or more columns, you are limited to using only the first syntax where the primary key is defined at the end of the CREATE TABLE statement.ĬONSTRAINT customers_pk PRIMARY KEY (last_name, first_name) We could have used the alternate syntax and created this same primary key as follows: CREATE TABLE employeesīoth of these syntaxes are valid when creating a primary key with only one field. It consists of only one column - the employee_id column. In this example, we've created a primary key on the employees table called employees_pk. We will start with a very simple one where our primary key consists of just one column.ĬONSTRAINT employees_pk PRIMARY KEY (employee_id) ![]() Let's look at an example of how to create a primary key using the CREATE TABLE statement in SQLite. ![]() column1, column2 The columns that you wish to create in the table. ) table_name The name of the table that you wish to create. pk_col_n)Ĭolumn1 datatype CONSTRAINT constraint_name PRIMARY KEY, The syntax to create a primary key using the CREATE TABLE statement in SQLite is: CREATE TABLE table_nameĬONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2. Create Primary Key (CREATE TABLE statement)Ī primary key can be created when you execute a CREATE TABLE statement in SQLite. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |