Questions
13 of 26
1What is a key in MySQL and why is it used?
2What is the difference between a key and an index?
3What is a primary key? Can a table have more than one primary key?
4What is a foreign key and why is it important in relational databases?
5How do you define a primary key when creating a table?
6Can a primary key column contain NULL values? Why or why not?
7What is the difference between a UNIQUE key and a PRIMARY key?
8What is an AUTO_INCREMENT key, and how does it work with the primary key?
9Can two tables have the same primary key name? Explain.
10What is the purpose of the FOREIGN KEY constraint in maintaining referential integrity?
11What is a composite key? How do you define one in MySQL?
12What are candidate keys, and how do they differ from alternate keys?
13What happens if you try to insert a duplicate value into a UNIQUE key column?
14How do foreign key constraints behave on DELETE and UPDATE operations (CASCADE, SET NULL, etc.)?
15What are the differences between ON DELETE CASCADE and ON DELETE SET NULL? What is a super key, and how does it relate to candidate keys? Can a foreign key reference a non-primary key column in another table?
16How do you disable and re-enable foreign key checks in MySQL? Why would you do that?
17How do you drop a primary key or foreign key from an existing table?
18How can you find all foreign key relationships in a MySQL database?
19How does InnoDB enforce foreign key constraints internally?
20What is the impact of defining multiple UNIQUE keys on the same table?
21Can you define a foreign key constraint referencing a table in a different database schema?
22Explain how key definitions affect indexing and query optimization.
23What are the differences between logical keys (like candidate or composite keys) and physical indexes?
24Can a composite key contain a foreign key as one of its columns? Provide an example.
25How does MySQL handle updates to parent keys that are referenced by foreign keys?
26What are deferred constraint checks, and does MySQL support them? How do you design keys in a many-to-many relationship using a junction (bridge) table? What are the potential performance implications of using multiple foreign keys and composite keys in large-scale systems?
mysqlmysql
Basics
Databases
Tables
Data Types
Queries
Views
Indexes
Clauses
Regex
Functions and Operators
Keys
Joins
Triggers
13 / 26

What happens if you try to insert a duplicate value into a UNIQUE key column?

Difficulty: 4/10
UNIQUE constraints, error handling, transaction behavior

What Happens When You Insert a Duplicate into a UNIQUE Key?

A UNIQUE key ensures that all values in the key column (or column group) remain distinct. If you insert a duplicate value, MySQL rejects the operation and raises an error because the uniqueness rule is violated.

1. How UNIQUE Key Enforces Uniqueness
  1. 1

    MySQL checks the UNIQUE index before inserting or updating a row.

  2. 2

    If the value already exists, MySQL blocks the operation.

  3. 3

    This prevents duplicate values and ensures data integrity at the key level.

Example: UNIQUE Key Definition
Attempting Duplicate Insert

The second insert fails with a duplicate key error.

MySQL Error
2. Additional Points About UNIQUE Keys
  1. 1

    UNIQUE key columns can store multiple NULL values because NULL is not considered equal to any value.

  2. 2

    In a composite UNIQUE key, the combination must be unique even if individual columns repeat.

Scenario Questions

0-2 years experience

  1. 1You have a users table where email is defined as UNIQUE. What does MySQL do if you try to INSERT a row with an email that already exists?
  2. 2If a script runs an INSERT that hits a duplicate UNIQUE key, what happens to the script's execution under default settings?

2-5 years experience

  1. 1During a recent feature rollout you start seeing duplicate‑key errors in the logs. Walk me through how you'd investigate the root cause.
  2. 2You need to bulk‑load data but want to silently skip rows that would violate a UNIQUE constraint. Which MySQL syntax would you use and why?
  3. 3Explain how a duplicate‑key error behaves when the INSERT is part of a transaction that also updates other tables.

5-8 years experience

  1. 1At high traffic volumes, duplicate‑key errors can cause retries and latency spikes. How would you design the ingestion pipeline to minimize their impact?
  2. 2Compare the performance and side‑effects of using INSERT IGNORE versus ON DUPLICATE KEY UPDATE in a sharded MySQL deployment.
  3. 3You must migrate legacy data that may contain duplicate keys into a new table with a UNIQUE index. What strategy would you use to preserve data integrity while completing the migration?

8+ years experience

  1. 1When multiple services share a MySQL schema with UNIQUE constraints, how do you coordinate schema changes to avoid duplicate‑key failures across teams?
  2. 2In a globally distributed system with eventual consistency, what long‑term architectural patterns help prevent duplicate‑key violations while still allowing high write throughput?

Follow-up Questions

  • How would you catch and handle that error in application code?
  • What changes if the INSERT runs inside a multi‑statement transaction?
  • When would you prefer INSERT IGNORE over ON DUPLICATE KEY UPDATE?
Share

Share via WhatsApp, X, Facebook, LinkedIn or copy link. Open Graph preview enabled.