Quick Reference
The UNIQUE keyword ensures that all values in a column are unique.
UNIQUE CONSTRAINT on CREATE TABLE
The following creates a UNIQUE constraint on the customer_id column when the Customers table is created.
MySQL:
CREATE TABLE Customers (
customer_id int NOT NULL,
last_name varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
UNIQUE (customer_id)
);SQL Server:
CREATE TABLE Customers (
customer_id int NOT NULL UNIQUE,
last_name varchar(255) NOT NULL,
first_name varchar(255) NOT NULL
);The following creates a UNIQUE constraint on multiple columns (customer_id column and last_name column) when the Customers table is created.
MySQL and SQL Sever:
CREATE TABLE Customers (
customer_id int NOT NULL,
last_name varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
CONSTRAINT uc_customers UNIQUE (customer_id, last_name)
);UNIQUE CONSTRAINT on ALTER TABLE
The following creates a UNIQUE constraint on the customer_id column when the Customers table already exists.
MySQL and SQL Server:
ALTER TABLE Customers
ADD UNIQUE (cusomer_id);The following creates a UNIQUE constraint on multiple columns (customer_id column and last_name column) when the Customers table already exists.
MySQL and SQL Server:
ALTER TABLE Customers
ADD CONSTRAINT uc_customers UNIQUE (customer_id, last_name);DROP a UNIQUE CONSTRAINT
MySQL:
ALTER TABLE Customers
DROP INDEX uc_customers;SQL Server:
ALTER TABLE Customers
DROP CONSTRAINT uc_customers;SQL Notes:
- Any work being done to modify the structure of a database or delete tables or the the database itself should only be done after making a recent backup
We’d like to acknowledge that we learned a great deal of our coding from W3Schools and TutorialsPoint, borrowing heavily from their teaching process and excellent code examples. We highly recommend both sites to deepen your experience, and further your coding journey. We’re just hitting the basics here at 1SMARTchicken.