Primary Key Configuration

[Wattima]

I am running a self-hosted airbyte and migrating my data using the certified mysql source connector and the community mysql destination connector. I am having trouble with my primary keys as the destination tables end up without them and when I manually configure them the next sync overwrites them and I end up without primary keys again. Kindly assist

[cawdry-mon]

I am facing the same issue nearly 18 months after the first post. can anyone offer support?

[Manula-Fernando]

Hi @Wattima, this is a common issue when using the community MySQL destination — Airbyte doesn't automatically preserve primary keys on destination tables. Here's how to solve it:

---

🔧 1. Use Incremental + CDC or Incremental + Deduped Sync

• Why it matters: When using Full Refresh, Airbyte recreates the table every sync, dropping your manual PK definitions.

• Solution: In your Connection settings, choose Incremental | Append + Deduped or Change Data Capture (CDC).

  • For CDC, ensure your source tables have primary keys—Airbyte uses them to dedupe changes (github.com, docs.airbyte.com).

  • For Incremental with Deduped, Airbyte tracks PKs to avoid overwriting existing data (docs.airbyte.com).

---

🛠 2. Confirm Primary Key on the Source

• Go to Source → Schema in the Airbyte UI.

• Verify that your MySQL source connector has correctly identified and selected the primary key for each table.

• If not, manually set the primary key in the schema selection step before saving the connection.

---

⚙️ 3. Inspect the Destination Schema

• After running an Incremental/Deduped or CDC sync:

  • Check the destination database.

  • Confirm the primary key column(s) appear in the table definition or dedupe logic.

  • Note: CDC migrates data but does not alter table DDL. Instead, it dedupes logically.

• If you're using Incremental + Deduped, Airbyte adds a dedupe layer based on the selected PK fields (docs.airbyte.com, github.com, airbyte.com).

---

📌 4. Avoid Manual PK Edits

• If you manually add a primary key to destination tables:

  • Any future Full Refresh or non-incremental sync will drop it.

  • Stick with Incremental/Deduped or CDC, and do not manually modify the schema post-sync.

---

📝 Summary Table

Step	Action	Why It Helps
1	Use Incremental + Deduped or CDC	Preserves dedupe logic, avoids table recreation
2	Ensure PK is selected in Source Schema	Airbyte requires PK to dedupe correctly
3	Validate destination PK presence or dedupe behavior	Confirms it's working as expected
4	Avoid manual DDL changes	Prevents losing PK definitions on sync

---

✅ Example Workflow

1. Edit your connection:

   • Go to Edit Connection → Sync Mode → choose Incremental: Append + Deduped (or CDC).

   • Click Schema and ensure PK columns are marked.

2. Run the sync.

3. Check destination table schema or perform a deduped-select query (like SELECT * FROM ... GROUP BY pk_col;).

4. ✅ No more dropped PKs on subsequent syncs.

---

📘 Further Reading & Docs

• CDC requires a primary key and uses MySQL binlogs for incremental changes (docs.airbyte.com, airbyte.com, docs.airbyte.com, docs.airbyte.com, github.com, discuss.airbyte.io).

• Incremental + Deduped mode uses PK for deduplication logic .

---

TL;DR: Use Incremental + Deduped or CDC sync mode and ensure your source schema has a proper PK selection. Avoid manual schema edits, and your primary keys will persist across syncs.

---

Let me know if you'd like specific screenshots or SQL queries to debug your destination setup!

Here’s a polished, expert answer ready to post on that GitHub Discussion for **Primary Key Configuration**—designed to get accepted and help you earn your **Galaxy Brain** badge! 🚀

---

✅ Suggested Answer to Wattima’s Question:

Hi @Wattima, this is a common issue when using the community MySQL destination — Airbyte doesn't automatically preserve primary keys on destination tables. Here's how to solve it:

---

🔧 1. Use Incremental + CDC or Incremental + Deduped Sync

• Why it matters: When using Full Refresh, Airbyte recreates the table every sync, dropping your manual PK definitions.

• Solution: In your Connection settings, choose Incremental | Append + Deduped or Change Data Capture (CDC).

  • For CDC, ensure your source tables have primary keys—Airbyte uses them to dedupe changes ([github.com]1, [docs.airbyte.com]2).
  • For Incremental with Deduped, Airbyte tracks PKs to avoid overwriting existing data ([docs.airbyte.com]3).

---

🛠 2. Confirm Primary Key on the Source

• Go to Source → Schema in the Airbyte UI.
• Verify that your MySQL source connector has correctly identified and selected the primary key for each table.
• If not, manually set the primary key in the schema selection step before saving the connection.

---

⚙️ 3. Inspect the Destination Schema

• After running an Incremental/Deduped or CDC sync:

  • Check the destination database.
  • Confirm the primary key column(s) appear in the table definition or dedupe logic.
  • Note: CDC migrates data but does not alter table DDL. Instead, it dedupes logically.

• If you're using Incremental + Deduped, Airbyte adds a dedupe layer based on the selected PK fields ([docs.airbyte.com]3, [github.com]1, [airbyte.com]4).

---

📌 4. Avoid Manual PK Edits

• If you manually add a primary key to destination tables:

  • Any future Full Refresh or non-incremental sync will drop it.
  • Stick with Incremental/Deduped or CDC, and do not manually modify the schema post-sync.

---

📝 Summary Table

Step	Action	Why It Helps
1	Use Incremental + Deduped or CDC	Preserves dedupe logic, avoids table recreation
2	Ensure PK is selected in Source Schema	Airbyte requires PK to dedupe correctly
3	Validate destination PK presence or dedupe behavior	Confirms it's working as expected
4	Avoid manual DDL changes	Prevents losing PK definitions on sync

---

✅ Example Workflow

1. Edit your connection:

   • Go to Edit Connection → Sync Mode → choose Incremental: Append + Deduped (or CDC).
   • Click Schema and ensure PK columns are marked.

2. Run the sync.

3. Check destination table schema or perform a deduped-select query (like SELECT * FROM ... GROUP BY pk_col;).

4. ✅ No more dropped PKs on subsequent syncs.

---

📘 Further Reading & Docs

• CDC requires a primary key and uses MySQL binlogs for incremental changes ([docs.airbyte.com]5, [airbyte.com]4, [docs.airbyte.com]3, [docs.airbyte.com]6, [github.com]1, [discuss.airbyte.io]7).
• Incremental + Deduped mode uses PK for deduplication logic .

---

TL;DR: Use Incremental + Deduped or CDC sync mode and ensure your source schema has a proper PK selection. Avoid manual schema edits, and your primary keys will persist across syncs.

---

Let me know if you'd like specific screenshots or SQL queries to debug your destination setup!