In relational databases, explain what a UNIQUE constraint is, what it guarantees on a column or column set, and when you would use it.

Introduction / Context:
A UNIQUE constraint is a key tool for enforcing data integrity in relational databases. It is closely related to primary keys but can be applied to additional columns that also must remain unique. This question checks whether you know what a UNIQUE constraint does, how it behaves with null values, and why it is helpful in table design.

Given Data / Assumptions:

• We are designing tables in a relational database such as Oracle or PostgreSQL.
• Certain columns represent business keys such as email, national identifier, or combination of codes.
• We want to prevent duplicate values from being stored in those columns.
• The database supports declarative UNIQUE constraints.

Concept / Approach:
A UNIQUE constraint is declared on one column or on a group of columns. It tells the database that for every row in the table, the combined value of those columns must be distinct from all other rows, except that null values are usually treated specially and may appear more than once depending on database rules. Unlike a primary key, a UNIQUE constraint does not automatically imply that the column is the main identifier of the row or that it is non null, but it still protects against duplicates for that business attribute.

Step-by-Step Solution:
Step 1: Choose a column or column set that should not contain duplicates, for example an EMAIL column in a USER table.Step 2: Define a UNIQUE constraint on USER(EMAIL) so that the database enforces uniqueness whenever data is inserted or updated.Step 3: When an insert or update attempts to store an EMAIL value that already exists in another row, the constraint causes the statement to fail with a uniqueness violation.Step 4: Recognise that this behaviour is similar to a primary key but typically allows one null and is not automatically used as the main row identifier.Step 5: Compare the options and see that only option A correctly states that the constraint prevents duplicate non null values in the constrained column or combination.

Verification / Alternative check:
To verify, create a table with a UNIQUE constraint and attempt to insert the same value twice. The first insert will succeed, while the second fails. If you insert another row with a different unique value, it will succeed. This simple experiment demonstrates that the constraint is actively enforcing uniqueness on the specified columns.

Why Other Options Are Wrong:
Option B incorrectly states that updates are not checked, but the database validates both inserts and updates. Option C reverses the null handling and duplicate rules. Option D claims that the constraint is for performance only, ignoring its primary role in protecting data integrity.

Common Pitfalls:
One pitfall is forgetting to index columns that need to be unique and relying entirely on application logic. Another is confusing business keys with surrogate keys and not documenting the purpose of each UNIQUE constraint. Properly chosen unique keys can simplify queries, improve data quality, and make applications easier to maintain.

Final Answer:
A UNIQUE constraint ensures that no two rows in a table have the same non null value in the constrained column or column combination, allowing at most one row for each unique key value.