Unique Identifiers Made Easy: Primary Key Implementation in SQLite

Here's how you can add a Primary Key in SQLite:

During Table Creation:

This is the recommended approach. You define the Primary Key constraint while creating the table itself using the CREATE TABLE statement. There are two ways to do this:

1. Primary Key on a Single Column:

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY,  /* id is the primary key */
  name TEXT,
  age INTEGER
);

In this example, the id column is declared as an integer and also marked as the Primary Key.

1. Primary Key on Multiple Columns (Composite Key):

CREATE TABLE my_table (
  customer_id INTEGER,
  order_id INTEGER,
  PRIMARY KEY (customer_id, order_id)  /* Primary key on both columns */
);

Here, both customer_id and order_id together form the Primary Key for the table. No two rows can have the same combination of values in these columns.

Important Points:

• SQLite doesn't allow adding a Primary Key to an existing table.
• If you need a Primary Key on an existing table, you'll have to create a new table with the desired Primary Key definition and then copy the data from the old table to the new one.

Primary Key on a Single Column (During Table Creation):

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,  /* id is the primary key */
  name TEXT NOT NULL,
  age INTEGER
);

This code creates a table named customers with three columns:

• id: An integer column set as the Primary Key, ensuring unique values for each customer.
• name: A text column to store customer names.
• age: An integer column to store customer ages.

Primary Key on Multiple Columns (Composite Key - During Table Creation):

CREATE TABLE orders (
  customer_id INTEGER,
  order_id INTEGER,
  product_name TEXT,
  quantity INTEGER,
  PRIMARY KEY (customer_id, order_id)  /* Primary key on both customer_id and order_id */
);

• customer_id: An integer column referencing a customer.
• order_id: An integer column for order identification.
• product_name: A text column to store the product name in the order.
• quantity: An integer column for the quantity of the product ordered.

The PRIMARY KEY constraint is defined on both customer_id and order_id columns, making sure no two orders have the same combination of customer and order ID.

Create a New Table with Primary Key:

• Define a new table with the same structure as your existing table, but ensure it includes the desired Primary Key constraint during creation using CREATE TABLE.

Transfer Data from Existing Table:

• Use an INSERT INTO statement to copy all data from the old table to the new table.

(Optional) Drop the Old Table:

• Once the data transfer is complete and verified, you can optionally drop the old table if you no longer need it.

Here's an example to illustrate this process:

-- Existing table without Primary Key (assuming a table named 'products')
CREATE TABLE products (
  name TEXT,
  price REAL
);

-- New table with Primary Key on 'id' (assuming a new table named 'products_new')
CREATE TABLE products_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,  /* Auto-incrementing id as Primary Key */
  name TEXT NOT NULL,
  price REAL
);

-- Transfer data from old table to new table
INSERT INTO products_new (name, price)
SELECT name, price FROM products;

-- Optional: Drop the old table (after verifying data transfer)
DROP TABLE products;

Important Considerations:

• This approach requires creating a new table and copying data, so it might not be ideal for very large tables.
• Make sure to back up your data before performing these operations to avoid any potential issues.
• Foreign key constraints referencing the old table will need to be updated to point to the new table after the data transfer.