Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

UNIQUE Constraint

The UNIQUE constraint in SQL Server is used to ensure that no two rows have the same value in specified columns.This constraint helps maintain data integrity by preventing duplicate values in columns where uniqueness is required. Unlike the primary key, which also enforces uniqueness, a table can have multiple UNIQUE constraints but only one primary key.

Key Concepts

1.Purpose of UNIQUE Constraint

Ensures that values in a column or a combination of columns are unique across the table.
Useful for columns that require distinct values, such as email addresses or product codes.

2.Syntax

UNIQUE (column_name): Ensures that the values in the specified column are unique.
UNIQUE (column1, column2, ...): Ensures that the combination of values in the specified columns is unique.

3.Adding UNIQUE Constraints

You can add UNIQUE constraints when creating a table or modify an existing table to include a UNIQUE constraint.

4.Behavior

A column with a UNIQUE constraint can accept NULL values unless the column is also defined as NOT NULL.

Code Samples

1. Creating Tables with UNIQUE Constraints

Define tables with various UNIQUE constraints to ensure unique values for specific columns.

CREATE TABLE Store (
    StoreID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, automatically unique
    StoreName VARCHAR(100) NOT NULL UNIQUE, -- StoreName must be unique
    Location VARCHAR(100) UNIQUE -- Location must be unique
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, automatically unique
    ProductName VARCHAR(100) NOT NULL UNIQUE, -- ProductName must be unique
    Category VARCHAR(50) DEFAULT 'General', -- No UNIQUE constraint
    Price DECIMAL(10,2) NOT NULL, -- No UNIQUE constraint
    StockQuantity INT DEFAULT 0 -- No UNIQUE constraint

);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, automatically unique
    Email VARCHAR(100) UNIQUE, -- Email must be unique
    PhoneNumber VARCHAR(15) UNIQUE -- PhoneNumber must be unique

);

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, automatically unique
    SaleDate DATETIME NOT NULL DEFAULT GETDATE(), -- No UNIQUE constraint
    StoreID INT NOT NULL, -- Foreign key, no UNIQUE constraint
    ProductID INT NOT NULL, -- Foreign key, no UNIQUE constraint
    CustomerID INT NULL, -- Foreign key, no UNIQUE constraint
    Quantity INT NOT NULL DEFAULT 1, -- No UNIQUE constraint
    TotalAmount DECIMAL(10,2) DEFAULT 0.00 -- No UNIQUE constraint
);
These examples demonstrate how to use the UNIQUE constraint to enforce distinct values in your SQL Server tables. You can modify these examples to fit your specific needs, ensuring that your data remains unique and consistent.

Tansy SQL Course - UNIQUE Constraint - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

Microsoft SQL Server

UNIQUE Constraint

Key Concepts

Code Samples

Most visited