> ## Documentation Index
> Fetch the complete documentation index at: https://artie.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Tables without primary keys

> Learn how to add primary keys to your tables and why they're essential for data replication.

## Why Primary Keys Matter 🔑

Primary keys are crucial for data replication because they:

1. **Ensure Data Ordering**: We use primary keys as partition keys in Kafka to guarantee the correct sequence of operations
2. **Maintain Data Integrity**: Primary keys enable reliable `MERGE` operations to keep your data consistent

## Adding Primary Keys: Two Common Scenarios 🛠️

<Accordion title="Scenario 1: Using Existing Unique Columns">
  Let's say you have a `users_no_pk` table with a unique email field:

  ```sql theme={null}
  CREATE TABLE users_no_pk (
      email VARCHAR(255) UNIQUE NOT NULL,
      first_name VARCHAR(255) NOT NULL,
      last_name VARCHAR(255) NOT NULL
  );
  ```

  To make this table replication-ready, simply promote the unique email to a primary key:

  ```sql theme={null}
  ALTER TABLE users_no_pk ADD PRIMARY KEY (email);
  ```
</Accordion>

<Accordion title="Scenario 2: Adding a New Primary Key Column">
  For tables without any unique identifiers, we can add a new primary key column. Let's use this example:

  ```sql theme={null}
  CREATE TABLE no_primary_keys (
      key VARCHAR(5),
      value bool
  );

  INSERT INTO no_primary_keys (key, value) VALUES ('foo', true), ('bar', false);
  ```

  Current table contents:

  ```bash theme={null}
  postgres=# select * from no_primary_keys;
   key | value
  -----+------
   bar | f    
   foo | f  
  (2 rows)
  ```

  Add a new primary key column:

  ```sql theme={null}
  ALTER TABLE no_primary_keys ADD COLUMN pk SERIAL PRIMARY KEY;
  -- This automatically:
  -- 1. Creates a new SERIAL column
  -- 2. Backfills existing rows with sequential values
  -- 3. Sets up auto-increment for new rows
  ```

  After adding the primary key:

  ```bash theme={null}
  postgres=# select * from no_primary_keys;
   key | value | pk
  -----+-------+----
   bar | f     |  2
   foo | f     |  1
  (2 rows)
  ```

  > 💡 **Pro Tip**: Your application code doesn't need any changes! You can continue inserting data without specifying the primary key:

  ```sql theme={null}
  INSERT INTO no_primary_keys (key, value) VALUES ('qux', false);
  ```

  ```bash theme={null}
  postgres=# select * from no_primary_keys;
   key | value | pk
  -----+-------+----
   bar | f     |  2
   foo | f     |  1
   qux | f     |  3
  (3 rows)
  ```
</Accordion>

## Need Help? 🤝

Have questions about adding primary keys to your tables? Reach out to us at [hi@artie.com](mailto:hi@artie.com)!