MySQL 8.0.22 | Constraint and Key (Unique, Not Null, Default, Primary key)

Last time we had created tables and checked with select and where.

MySQL 8.0.22 | How to insert or select data in the table + where clause

This time we are going to take a look what we can put more in the table we had made.

1. Unique

You must have seen “This id is duplicated.” on the sign in form. And ‘Unique’ is the exactly how you make that rule. Let’s say I want to create a table that not allows duplicated id.

mysql> create table member(
    -> id varchar(8) unique,
    -> name varchar(10),
    -> phone varchar(13)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into member values ('A','Jenny','000-0000-0000');
Query OK, 1 row affected (0.01 sec)

There, I made the table ‘member’. And at the end of the id line, I put space and unique. And then I put the member ‘Jenny’ who’s ID is ‘A’. What’s going to happen if i put other member ‘Lisa’ who has the same ID?

mysql> insert into member values ('A','Lisa','111-1111-1111');
ERROR 1062 (23000): Duplicate entry 'A' for key 'member.id'

There’s gonna be an error like this. So you managed to make a ID which is not allowed to duplicate others!

2. Not Null

On the sign in field, “This is required” would also be very common. That is what ‘Not Null’ does.

mysql> create table member2(
    -> id varchar(8) unique,
    -> name varchar(10) not null,
    -> phone varchar(13) not null
    -> );
Query OK, 0 rows affected (0.04 sec)

And then I’ll put the new member who forgot to type the name in it.

mysql> insert into member values ('A',null,'000-0000-0000');
ERROR 1048 (23000): Column 'name' cannot be null

Just like this, you can make required section.

3. Primary Key

Primary key(pk) is the combination between unique and not null. If i want to make an ID which is not allowed to duplicate or leave it empty, I’ll use primary key in it.

mysql> create table member3(
    -> id varchar(8) primary key,
    -> name varchar(10) not null,
    -> phone varchar(13) not null
    -> );
Query OK, 0 rows affected (0.06 sec)

But, unlike the unique or the not null constraint, you can set the primary key in only one column in one table. You will use ‘PK’ a lot with the another key named ‘Foreign Key’, which I’ll explain next time.

4. default

What if you want to give 100 point to every member who joined in? You can use ‘default’.

mysql> create table member4(
    -> id varchar(8) primary key,
    -> name varchar(10) not null,
    -> phone varchar(13) not null,
    -> point int default 100
    -> );
Query OK, 0 rows affected (0.04 sec)

Put default 100 and i’ll put ‘Jenny’ and just put default in the point section.

mysql> insert into member values ('A','Jenny','000-0000-0000',default);
Query OK, 1 row affected (0.01 sec)
mysql> select * from member;
+----+-------+---------------+-------+
| id | name  | phone         | point |
+----+-------+---------------+-------+
| A  | Jenny | 000-0000-0000 |   100 |
+----+-------+---------------+-------+
1 row in set (0.00 sec)

Great. Now we can make better table!

next time I’ll talk about how to revise tables and tuples.