MySQL constraints

Back to: Structured Query Language (SQL)

UNIQUE constraint

The UNIQUE constraint ensures that all values in a column are all different. This may be useful for the SKU number of a product or the barcode, product name and so on. We can add the constraint when we CREATE a TABLE or after.

CREATE TABLE products (
    product_id INT,
    product_name varchar(25) UNIQUE,
    price DECIMAL(4, 2)
);

This has created a products table where the product_name data must all be different (or unique).

If we had created the table normally (without using UNIQUE), we can make a field (column) unique with this command:

ALTER TABLE products
ADD CONSTRAINT 
UNIQUE (product_name);

We can insert some data:

INSERT INTO products
VALUES (100, 'hamburger', 3.99),
       (101, 'chips', 5.50),
       (102, 'coke', 3.00),
       (103, "ice cream", 4.50);

SELECT * FROM products;

If we accidentally try to add more chips:

INSERT INTO products
VALUES (104, 'chips', 6.00);

We get an error:

This error is because the UNIQUE constraint enforces that ALL data under product_name is different.

DROP TABLE products;

NOT NULL constraint

The NOT NULL constraint can be added to a column when you create a table, whenever we enter a new row, the value within that column can NOT be NULL (or blank or empty).

CREATE TABLE products (
    product_id INT,
    product_name varchar(25),
    price DECIMAL(4, 2) NOT NULL
);

This has created a products table where the price data must have a value (not blank).

If we had created the table normally (without using NOT NULL), we can make a field (column) NOT NULL with this command:

ALTER TABLE products
MODIFY price DECIMAL(4, 2) NOT NULL;

We can insert some data:

INSERT INTO products
VALUES(104, "cookie", NULL);

This will throw an error – Error Code:1048 Column ‘price’ cannot be null

DROP TABLE products;

CHECK constraint

The CHECK constraint is used to limit what values can be places in a column. In Australia, the minimum wage for a 16 year old is $10.99. We can have MySQL perform a check of the hourly_rate data being entered to ensure it is at least $10.99.

If creating a new table you can write:

CREATE TABLE employees(
	employee_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	hourly_pay DECIMAL (5, 2),
	hire_date DATE, 
	CONSTRAINT chk_hourly_pay CHECK (hourly_pay >= 10.99)
);

Since we already have this table, we can ALTER the TABLE to do this CHECK:

ALTER TABLE employees
ADD CONSTRAINT chk_hourly_pay CHECK (hourly_pay >= 10.99);

If we try to add a value for hourly_pay that is less than $10.99, we’ll get an error:

INSERT INTO employees
VALUES (6, "Sheldon", "Plankton", 5.00, "2025-03-11");

This will throw an error – Error Code:3819. Check constraint ‘chk_hourly_pay’ is violated

To delete the CHECK we can use:

ALTER TABLE employees
DROP CHECK chk_hourly_pay;

DEFAULT constraint

When inserting a new row, if we do not specify a value for a column, by default, we can add some value that we set:

Let’s create our products table again, only this time we’ll add a DEFAULT price of 0.00 if a value is not specified:

CREATE TABLE products (
    product_id INT,
    product_name varchar(25) UNIQUE,
    price DECIMAL(4, 2) DEFAULT 0.00
);

SELECT * FROM products;

If the table already exists:

ALTER TABLE products 
ALTER price SET DEFAULT 0;

We will add the first six product items again:

INSERT INTO products
VALUES (100, 'hamburger', 3.99),
       (101, 'chips', 5.50),
       (102, 'coke', 3.00),
       (103, "ice cream", 4.50);

SELECT * FROM products;

Now let’s add some more products that happen to all be free – we could use the DEFAULT constraint for this. For a NEW table:

INSERT INTO products (product_id, product_name)
VALUES (104, "straw"),
       (105, "napkin"),
       (106, "fork"),
       (107, "spoon");

SELECT * FROM products;

Unfortunately, you do need to specify the field names (column names) for this to work.

Another example – let’s say we have a table of transactions, after inserting a new transaction, we will insert a time stamp of when that transaction took place, and that transaction will be DEFAULT:

CREATE TABLE transactions(
    transaction_id INT,
    amount DECIMAL(5, 2),
    transaction_date DATETIME DEFAULT NOW()
);
SELECT * FROM transactions;

Let’s insert a transaction:

INSERT INTO transactions (transaction_id, amount)
VALUES (1, 4.99);

SELECT * FROM transactions;

Add another transaction – we are doing this one at a time so we can see the changing transaction time:

INSERT INTO transactions (transaction_id, amount)
VALUES (2, 2.89);

SELECT * FROM transactions;

And one more…

INSERT INTO transactions (transaction_id, amount)
VALUES (3, 8.37);

SELECT * FROM transactions;

Outputs:

The DEFAULT constraint works well in this example to automatically time stamp our entries.

DROP TABLE transactions;