[MySQL] Day 6. UNIQUE & Not Null Constraint

In this post, we will take a look at two constraints in MySQL—UNIQUE and NOT NULL.

 

UNIQUE

UNIQUE constraint ensures that all values in a column are different. 

We can add a constraint to a table when we create it, or after. 

 

Let's make a new table named product, that stores three columns: id, name, and price of the product. 

Say we don't want any duplicative product id's in the table. 

We can use UNIQUE constraint when creating the table, specifying for the product_id column.

CREATE TABLE prouct(
    product_id INT UNIQUE,
    product_name VARCHAR(25),
    price DECIMAL(4, 2)
);

 

 

Notice that product_id column is bold font in MySQL Workbench UI. 

 

 

Now, say you forgot to include UNIQUE keyword when creating the table. 

Then, you can use ALTER keyword for the table to add UNIQUE constraint.

ALTER TABLE products
ADD CONSTRAINT 
UNIQUE(product_id);

 

 

Now, since we created the table, let's try inserting some data. 

You can learn using INSERT INTO syntax in following post. 

[SQL] Day 2: INSERT INTO & SELECT WHERE

 

 

Suppose we mistakenly tried to add a product with same product id. 

INSERT INTO products
VALUES  (001, "Hamburger", 3.99),
        (002, "Fries", 1.49),
        (003, "Soda", 1.00),
        (004, "Ice Cream", 1.49),
        (001, "Hot Dog", 2.49);	-- duplicative product id

-- Error Code: 1062. Duplicate entry '1' for key 'products.product_id'

It will show the error code as above, because we set product_id column to have UNIQUE constraint. 

MySQL is telling us that we have a duplicate under product_id column for data 1. 

 

Let's try removing the last row that causes problem and run the queries again. 

INSERT INTO products
VALUES  (001, "Hamburger", 3.99),
        (002, "Fries", 1.49),
        (003, "Soda", 1.00),
        (004, "Ice Cream", 1.49);

SELECT * from products;

 

 

Now the data were added succesfully because we followed the cosntraint to exclude any duplcates in product_id column. 

 

---

NOT NULL

 

NOT NULL is a simple constraint you can set on a table column to ensure it cannot contain null values.

Same as UNIQUE, we can either add this constraint for a table when we create a table, or after. 

 

Suppose we want to set product_name column with NOT NULL constraint. 

I will skip the query for creating table, because it's basically the same with above but with NOT NULL instead of UNIQUE. 

 

However, we will take a look for the case when we have to add NOT NULL after creating the table, because the syntax is actually different with UNIQUE.

We use MODIFY this time. 

ALTER TABLE products
MODIFY product_name VARCHAR(25) NOT NULL;

 

If we want to set it back to NULL-able, then simply use it without NOT keyword.

ALTER TABLE products
MODIFY COLUMN product_name VARCHAR(25) NULL;

 

Now, in MySQL Workbench there is one way you can easily the Nullable status on a table (and many other information about the table).

 

Right click the table, and you can find Table Inspector. 

Or you can simply click ℹ️ button that appear when you hover your mouse above the table. 

 

The table inspector will show many information about the table. 

In the Columns tab, you can find the the status about NOT NULL for each column. 

 

Under Columns tab, we can find this table and there is Nullable column that shows whether the column is Nullable or not. 

 

 

Going back to the queries, since we have succesfully set the Nullability to No for product_name, let's try if it actually works! 

Let's try adding a data with NULL product_name data. 

INSERT INTO products
VALUES (5, NULL, 20.00);

 

Now, they is no syntax error showing in the Workbench. 

But if we try running this statements it actually shows an error. 

--Error Code: 1048. Column 'product_name' cannot be null

Clearly shows that product_name column cannot be null, because we set the constraint! 

 

Now let's try some other column to be Null and product_name not null, and check if that works. 

INSERT INTO products
VALUES (NULL, "Burrito", 20.00);

 

Since other columns are nullable, there is no problem adding the data.