To create a table with a UNIQUE constraint on multiple columns in SQL, you can use the CREATE TABLE statement and specify the UNIQUE constraint either at the column level or at the table level.
Example of Creating a Table with UNIQUE Constraint
Here's a general SQL syntax to create a table with a UNIQUE constraint on multiple columns:
CREATE TABLE your_table_name (
column1 datatype,
column2 datatype,
column3 datatype,
-- Other columns
UNIQUE (column1, column2) -- UNIQUE constraint on column1 and column2
);
Example with Specific Data Types
Let’s demonstrate this with a specific example where we create a customers table with a UNIQUE constraint on email and phone_number:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(15),
UNIQUE (email, phone_number) -- UNIQUE constraint on email and phone_number
);
Adding a UNIQUE Constraint to an Existing Table
If you already have a table and want to add a UNIQUE constraint to it, you can use the ALTER TABLE statement:
ALTER TABLE your_table_name
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2);
Example of Altering an Existing Table
For the customers table, if we initially created it without the UNIQUE constraint and want to add it later, we would do:
ALTER TABLE customers
ADD CONSTRAINT unique_email_phone UNIQUE (email, phone_number);
Key Points
- The UNIQUE constraint ensures that the combination of values in the specified columns is unique across rows in the table.
- If you attempt to insert a duplicate combination of values in columns that are part of a UNIQUE constraint, you will receive an error.
- It's good practice to name your constraints (e.g., unique_email_phone) for easier reference and management.
That's how you create and manage UNIQUE constraints on multiple columns in SQL!