UNIQUE, DEFAULT, and AUTO_INCREMENT

Dialect note: AUTO_INCREMENT is MySQL syntax. The number of NULL values allowed by a UNIQUE constraint differs by database, and a default is not used when NULL is inserted explicitly.

1. UNIQUE Constraint

Definition

A UNIQUE constraint ensures that no two rows in a table can have the same value in that column.

• Allows NULL values (unless specified otherwise).
• Ensures data uniqueness.

1.1 Creating a Table With UNIQUE Columns

CREATE TABLE customers (
  customer_id INT,
  customer_fname VARCHAR(45),
  customer_lname VARCHAR(45),
  customer_email VARCHAR(45) UNIQUE KEY,
  customer_phone VARCHAR(45) UNIQUE KEY,
  customer_street VARCHAR(255),
  customer_city VARCHAR(45),
  customer_state VARCHAR(45),
  customer_zipcode VARCHAR(10)
);

Explanation

• customer_email must be unique
• customer_phone must be unique
• Attempting duplicate values will result in an error

1.2 Example – Unique Constraint Violation

INSERT INTO customers VALUES
(1,'Richard','Hernandez','richard@gmail.com','9191919191','Heather Plaza','Brownsville','TX','78521');

INSERT INTO customers VALUES
(2,'Mary','Barrett','mary@gmail.com','9191919191','Noble Embers Ridge','Littleton','CO','80126');

Second row fails because customer_phone = 9191919191 already exists.

1.3 Real-Life Use Cases

2. Primary Key vs Unique Key

Formula:

Primary Key = Unique + Not Null

3. DEFAULT Constraint

Definition

DEFAULT assigns an auto value to a column when no value is provided during INSERT.

Ensures:

• Data consistency
• Avoids NULL for common fields

3.1 Example: Employee Table With DEFAULT Status

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE,
  status VARCHAR(10) DEFAULT 'active'
);

Inserts:

INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES (1, 'Kapil', 'Raj', '2023-07-01');

→ status automatically becomes 'active'

INSERT INTO employees
(employee_id, first_name, last_name, hire_date)
VALUES (2, 'Rahul', 'Sharma', '2023-07-02');

→ status = 'active'

INSERT INTO employees VALUES (3, 'Jane', 'Smith', '2023-07-01', DEFAULT);

→ status = 'active'

INSERT INTO employees VALUES (4, 'Amit', 'Lal', '2023-07-01', 'inactive');

3.2 DEFAULT with CURRENT_DATE

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  quantity INT,
  order_date DATE DEFAULT (CURRENT_DATE)
);

Insert examples:

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (1, 101, 202, 3);

→ order_date = today’s date

INSERT INTO orders VALUES (2, 101, 202, 3, DEFAULT);

3.3 DEFAULT Example (Country)

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  country VARCHAR(50) DEFAULT 'USA'
);

INSERT INTO customers (customer_id, first_name, last_name)
VALUES (1, 'Alice', 'Smith');

→ country = 'USA'

4. AUTO-INCREMENT Constraint

Definition

AUTO-INCREMENT automatically generates unique integer values for a column—typically used for primary keys.

No need to manually insert values.

4.1 Creating a Table with AUTO_INCREMENT

CREATE TABLE employees (
  employee_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE
);

Insert Example:

INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('John', 'Doe', '2023-07-01');

Auto-generated IDs:

• First insert → 1
• Next → 2

4.2 Changing the Starting Value

ALTER TABLE employees AUTO_INCREMENT = 1000;

Next inserted record will have ID = 1000.

4.3 Changing Auto-Increment Step Size

SET @@auto_increment_increment = 2;

Sequence becomes:

• 1000
• 1002
• 1004
• 1006

5. Quick Summary of Today’s Learning

UNIQUE

• Prevents duplicate values
• Allows NULL (unless not null added)

DEFAULT

• Provides automatic values
• Ensures consistency and avoids NULL issues

AUTO-INCREMENT

• Automatically generates primary key values
• Common for identity columns

Combined Constraints

Examples:

• email → UNIQUE + NOT NULL
• customer_id → PRIMARY KEY
• status → DEFAULT 'active'
• employee_id → AUTO-INCREMENT

Common Mistakes to Avoid

• Misusing UNIQUE and PRIMARY KEY: Remember that you can have multiple UNIQUE columns, but only one PRIMARY KEY. Use the PRIMARY KEY for the main ID and UNIQUE for secondary identifiers like phone numbers.
• Check Constraints and NULLs: If a column allows NULLs, a CHECK constraint might not work on those NULL rows. Always pair CHECK with NOT NULL if you want the rule to be strictly enforced for every entry.
• Default Values and Existing Data: Adding a DEFAULT constraint to a table that already has data will only affect new rows. It will not automatically fill in the old rows that were already there.