Test Your PostgreSQL UNIQUE Constraint Knowledge

Progress

True / False

The UNIQUE constraint in PostgreSQL ensures that all values in a column are different.

True False

The UNIQUE constraint ensures that all values in a column or a group of columns are unique across the rows of the table.

True / False

You can have multiple UNIQUE constraints on a single table in PostgreSQL.

True False

A table can have multiple UNIQUE constraints, each enforcing uniqueness for different columns or sets of columns.

True / False

A UNIQUE constraint automatically creates an index in PostgreSQL.

True False

When a UNIQUE constraint is created on a column or a set of columns, PostgreSQL automatically creates a unique index on those columns.

True / False

You can apply the UNIQUE constraint to multiple columns at once in PostgreSQL.

True False

The UNIQUE constraint can be applied to multiple columns together, ensuring the combination of values in these columns is unique across the table.

True / False

The UNIQUE constraint in PostgreSQL can include NULL values.

True False

In PostgreSQL, a column with a UNIQUE constraint can include multiple NULL values because NULL is not considered equal to any other NULL.

True / False

If you drop a UNIQUE constraint in PostgreSQL, the associated index is also dropped automatically.

True False

Dropping a UNIQUE constraint will also drop the unique index associated with it in PostgreSQL.

True / False

You can add a UNIQUE constraint to an existing column with duplicate values in PostgreSQL.

True False

You cannot add a UNIQUE constraint to an existing column that has duplicate values. All existing values must be unique before applying the constraint.

True / False

UNIQUE constraints can be deferred in PostgreSQL, meaning their enforcement can be delayed until the end of a transaction.

True False

PostgreSQL supports deferrable constraints, including UNIQUE constraints, allowing their enforcement to be deferred until the end of a transaction.

True / False

PostgreSQL supports the use of the UNIQUE constraint on arrays.

True False

PostgreSQL does not support applying the UNIQUE constraint directly on array data types.

Q10

True / False

In PostgreSQL, the UNIQUE constraint can be used in conjunction with the PRIMARY KEY constraint on the same column.

True False

In PostgreSQL, a column with a PRIMARY KEY constraint automatically has a UNIQUE constraint. Applying both constraints on the same column is redundant and unnecessary.

Q11

Single Choice

Which of the following SQL statements correctly creates a table with a UNIQUE constraint on the email column?

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE); CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE(email)); CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255));CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE KEY);

In MySQL, you can add a UNIQUE constraint directly to a column by adding the keyword UNIQUE after the column definition.

Q12

Single Choice

What is the primary purpose of using a UNIQUE constraint in MySQL?

To ensure that no two rows have the same value in a particular column. To allow duplicate values in a column. To automatically create an index on a column.To enforce foreign key relationships.

The UNIQUE constraint ensures that all values in a column are distinct, meaning no duplicates are allowed.

Q13

Single Choice

How can you add a UNIQUE constraint to an existing column in a MySQL table?

ALTER TABLE table_name ADD UNIQUE (column_name); ALTER TABLE table_name MODIFY UNIQUE (column_name); ALTER TABLE table_name ADD UNIQUE CONSTRAINT (column_name);ALTER TABLE table_name SET UNIQUE (column_name);

The correct syntax to add a UNIQUE constraint to an existing column is ALTER TABLE table_name ADD UNIQUE (column_name);.

Q14

Single Choice

Consider the following SQL statement. What happens if you try to insert a duplicate value into the email column?

SQL Code

CREATE TABLE employees (
 id INT PRIMARY KEY,
 email VARCHAR(255) UNIQUE
);

The insertion fails, and an error is thrown. The insertion succeeds, and a warning is generated. The insertion succeeds, but the duplicate value is ignored.The insertion fails, but no error is shown.

The UNIQUE constraint enforces uniqueness for the email column, so attempting to insert a duplicate value will result in an error.

Q15

Single Choice

Given the following SQL code, how many UNIQUE constraints are defined on the products table?

SQL Code

CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(255),
 sku VARCHAR(100) UNIQUE,
 product_code VARCHAR(100) UNIQUE
);

1 2 34

There are two UNIQUE constraints defined: one on the sku column and another on the product_code column.

Q16

Single Choice

How can you drop a UNIQUE constraint from a column in a MySQL table?

ALTER TABLE table_name DROP UNIQUE (column_name); ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name MODIFY column_name DROP UNIQUE;ALTER TABLE table_name DROP CONSTRAINT unique_name;

The UNIQUE constraint in MySQL is implemented as an index, so to drop it, you use ALTER TABLE table_name DROP INDEX index_name;.

Q17

Single Choice

What will be the result of the following SQL code if there are already duplicate values in the username column?

SQL Code

ALTER TABLE users ADD UNIQUE (username);

The UNIQUE constraint is added, and the duplicates are removed. The UNIQUE constraint is added, and a warning is generated for duplicates. The UNIQUE constraint is not added, and an error is thrown.The UNIQUE constraint is added, but the duplicates remain.

If there are existing duplicate values in the column, MySQL will throw an error and will not add the UNIQUE constraint.

Q18

Single Choice

How can you define a UNIQUE constraint on multiple columns in a MySQL table?

ALTER TABLE table_name ADD UNIQUE (column1, column2); CREATE UNIQUE INDEX index_name ON table_name (column1, column2); CREATE TABLE table_name (column1, column2, UNIQUE (column1, column2));All of the above

All three options are valid methods to define a UNIQUE constraint on multiple columns in MySQL.

Q19

Single Choice

In MySQL, what is the difference between a PRIMARY KEY and a UNIQUE constraint?

A PRIMARY KEY constraint allows NULL values, while a UNIQUE constraint does not. A UNIQUE constraint allows multiple NULL values, while a PRIMARY KEY does not. A UNIQUE constraint automatically creates an index, while a PRIMARY KEY does not.There is no difference between them.

A UNIQUE constraint allows multiple NULL values, while a PRIMARY KEY does not allow any NULL values and enforces uniqueness.

Q20

Single Choice

Consider the following SQL statements. Which of the following is true regarding the creation of a composite UNIQUE constraint?

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 UNIQUE (customer_id, order_date)
);

A composite UNIQUE constraint ensures that no two rows have the same combination of customer_id and order_date The composite UNIQUE constraint ensures that no two rows have the same value in either customer_id or order_date. The composite UNIQUE constraint does not allow NULL values in both customer_id and order_date.The composite UNIQUE constraint allows only one NULL value across customer_id and order_date.

A composite UNIQUE constraint ensures that the combination of values in the specified columns (customer_id and order_date in this case) is unique across all rows.

Q21

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint on the 'email' column in PostgreSQL?

SQL Code

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 email VARCHAR(255) UNIQUE
);

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL );CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) );

This command creates a 'users' table where the 'email' column has a UNIQUE constraint, ensuring no two users can have the same email address.

Q22

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint on multiple columns in PostgreSQL?

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT NOT NULL,
 product_id INT NOT NULL,
 UNIQUE (customer_id, product_id)
);

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL, product_id INT NOT NULL, UNIQUE (customer_id, product_id) ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT, product_id INT, UNIQUE (customer_id, product_id) ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL, product_id INT NOT NULL, UNIQUE (product_id, customer_id) );CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT, product_id INT );

This command creates an 'orders' table where the combination of 'customer_id' and 'product_id' is unique, ensuring no customer can order the same product more than once.

Q23

Multiple Choice

Which SQL command adds a UNIQUE constraint to an existing column in PostgreSQL?

SQL Code

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email); ALTER TABLE employees ADD UNIQUE (email); ALTER TABLE employees ALTER COLUMN email SET UNIQUE;ALTER TABLE employees ADD UNIQUE CONSTRAINT (email);

This command adds a UNIQUE constraint to the 'email' column in the 'employees' table, ensuring that each email address is unique.

Q24

Multiple Choice

Which SQL command drops an existing UNIQUE constraint from a table in PostgreSQL?

SQL Code

ALTER TABLE users
DROP CONSTRAINT unique_email;

ALTER TABLE users DROP CONSTRAINT unique_email; ALTER TABLE users DROP UNIQUE (email); ALTER TABLE users REMOVE CONSTRAINT unique_email;ALTER TABLE users DELETE CONSTRAINT unique_email;

This command removes the UNIQUE constraint named 'unique_email' from the 'users' table, allowing duplicate email addresses.

Q25

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint that ignores NULL values in PostgreSQL?

SQL Code

CREATE TABLE contacts (
 contact_id SERIAL PRIMARY KEY,
 phone_number VARCHAR(20) UNIQUE
);

CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, phone_number VARCHAR(20) UNIQUE ); CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, phone_number VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, phone_number VARCHAR(20) NOT NULL UNIQUE );CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, phone_number VARCHAR(20) UNIQUE NULL );

In PostgreSQL, a UNIQUE constraint allows multiple NULL values. This command creates a 'contacts' table where the 'phone_number' column is unique but can have multiple NULL entries.

Q26

Multiple Choice

Which SQL command creates a table with a case-insensitive UNIQUE constraint in PostgreSQL?

SQL Code

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_code CITEXT UNIQUE
);

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_code CITEXT UNIQUE ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_code VARCHAR(50) UNIQUE ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_code VARCHAR(50) UNIQUE COLLATE "C" );CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_code TEXT UNIQUE );

This command creates a 'products' table where the 'product_code' column has a case-insensitive UNIQUE constraint, using the CITEXT data type.

Q27

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint on a computed column in PostgreSQL?

SQL Code

CREATE TABLE invoices (
 invoice_id SERIAL PRIMARY KEY,
 total_amount NUMERIC(10, 2),
 tax_amount NUMERIC(10, 2),
 UNIQUE (total_amount + tax_amount)
);

CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, total_amount NUMERIC(10, 2), tax_amount NUMERIC(10, 2), UNIQUE (total_amount + tax_amount) ); CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, total_amount NUMERIC(10, 2), tax_amount NUMERIC(10, 2), UNIQUE (total_amount, tax_amount) ); CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, total_amount NUMERIC(10, 2) UNIQUE, tax_amount NUMERIC(10, 2) );CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, total_amount NUMERIC(10, 2), tax_amount NUMERIC(10, 2) );

This command creates an 'invoices' table where the sum of 'total_amount' and 'tax_amount' is unique, ensuring no two invoices can have the same total plus tax value.

Q28

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint that applies to a specific condition in PostgreSQL?

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 email VARCHAR(255),
 department VARCHAR(50),
 UNIQUE (email) WHERE department = 'Sales'
);

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, email VARCHAR(255), department VARCHAR(50), UNIQUE (email) WHERE department = 'Sales' ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, email VARCHAR(255), department VARCHAR(50), UNIQUE (email, department) ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE, department VARCHAR(50) );CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, email VARCHAR(255), department VARCHAR(50) UNIQUE );

This command creates an 'employees' table where the 'email' column is unique only within the 'Sales' department, allowing duplicate emails in other departments.

Q29

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint that ensures a column's value is unique across multiple tables in PostgreSQL?

SQL Code

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 email VARCHAR(255) UNIQUE
);

CREATE TABLE admins (
 admin_id SERIAL PRIMARY KEY,
 email VARCHAR(255) UNIQUE
);

ALTER TABLE admins ADD CONSTRAINT unique_email_global EXCLUDE USING btree (email WITH =);

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); CREATE TABLE admins ( admin_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); ALTER TABLE admins ADD CONSTRAINT unique_email_global EXCLUDE USING btree (email WITH =); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); CREATE TABLE admins ( admin_id SERIAL PRIMARY KEY, email VARCHAR(255) ); ALTER TABLE admins ADD UNIQUE (email); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) ); CREATE TABLE admins ( admin_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); ALTER TABLE users ADD CONSTRAINT unique_email_global EXCLUDE USING btree (email WITH =);CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); CREATE TABLE admins ( admin_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE );

This setup attempts to enforce uniqueness on the 'email' column across the 'users' and 'admins' tables, though in practice, this would require more advanced techniques like triggers.

Q30

Multiple Choice

Which SQL command creates a table with a UNIQUE constraint and handles violations using a constraint name in PostgreSQL?

SQL Code

CREATE TABLE members (
 member_id SERIAL PRIMARY KEY,
 username VARCHAR(50) UNIQUE CONSTRAINT unique_username
);

INSERT INTO members (username) VALUES ('john_doe') ON CONFLICT ON CONSTRAINT unique_username DO NOTHING;

CREATE TABLE members ( member_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE CONSTRAINT unique_username ); INSERT INTO members (username) VALUES ('john_doe') ON CONFLICT ON CONSTRAINT unique_username DO NOTHING; CREATE TABLE members ( member_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE ); INSERT INTO members (username) VALUES ('john_doe') ON CONFLICT (username) DO NOTHING; CREATE TABLE members ( member_id SERIAL PRIMARY KEY, username VARCHAR(50) ); ALTER TABLE members ADD UNIQUE (username);CREATE TABLE members ( member_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE ); INSERT INTO members (username) VALUES ('john_doe') ON CONFLICT DO NOTHING;

This command creates a 'members' table with a UNIQUE constraint named 'unique_username' on the 'username' column and handles any constraint violation using the ON CONFLICT clause.

PostgreSQL Database Quiz Questions