0

Hi I'm completely new to MySQL, I'm creating a database and my question is that:

Should I add NOT NULL UNIQUE to the field for primary key?

For example I have:

CREATE TABLE Account (
    AccountID varchar(25) NOT NULL,
    ...
    PRIMARY KEY(AccountID)           // <------- (*)

I'm thinking about whether the (*) would implicitly mark the field as UNIQUE NOT NULL.

Btw, could anyone provide good resources about best practices of MySQL(I'm currently interested about creating table) so I won't add redundant restriction(s) in any schema.


Edit: In MySQLWorkbench it seems like UNIQUE is not checked by default.

3
  • Hi, and welcome to the forum! PKs are NOT NULL UNIQUE by definition. You typically add the NOT NULL UNIQUE definition to natural keys!
    – Vérace
    Commented Apr 21, 2021 at 3:19
  • @Vérace: So adding the two constrains is redundant right? Could you provide some resources that I can find more about the convention of best practices in MySQL? I've taken a course about database and I know that PKs are NOT NULL UNIQUE conceptually, but my problem is that I don't know whether MySQL would done that automatically for us.
    – Rainning
    Commented Apr 21, 2021 at 3:21
  • Run a few tests on MySQL and different servers (PostgreSQL)! Look at SHOW CREATE TABLE\G on tables with and without PKs. Do the same for FKs! In this case, it is redundant!
    – Vérace
    Commented Apr 21, 2021 at 3:33

1 Answer 1

4

It's true that a primary key is always implicitly NOT NULL and UNIQUE.

But in particular with the MySQL implementation, here are a couple of considerations:

  • The PRIMARY KEY column(s) will become NOT NULL. If you subsequently use SHOW CREATE TABLE or DESCRIBE, you will see that this column property is added whether you asked for it or not.

  • If you declare a column as UNIQUE as well as PRIMARY KEY, MySQL oddly creates a second index over the same columns. This is truly redundant and only takes up extra storage space and causes a bit of overhead when you insert/update/delete rows. It doesn't give any benefit.

MySQL also has a keyword SERIAL, which is shorthand for BIGINT UNSIGNED AUTO_INCREMENT UNIQUE, so if you use SERIAL PRIMARY KEY look what you get:

mysql> CREATE TABLE MyTable ( id SERIAL PRIMARY KEY );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table MyTable\G
*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The extra UNIQUE KEY is redundant in this case.

3
  • May I ask you that how to show the definition you printed using show create table ... \G` in MySQL Workbench? It seems like I cannot directly run this command in the Query tab.
    – Rainning
    Commented Apr 21, 2021 at 4:27
  • The \G terminator is specific to the command-line mysql client. Just leave that terminator off if you are using MySQL Workbench. Commented Apr 21, 2021 at 4:31
  • The PRIMARY KEY column(s) will become NOT NULL. More correct "columns mentioned in PK expression" - PK may be built by any valid expression and not refer to column separately and directly. This is truly redundant. MySQL always creates an index when you ask for this. SERIAL asks to create UNIQUE index, PK asks to create PRIMARY index - so 2 indices are created. MySQL does not check if the index with the same expression exists, it checks index name uniqueness only. So this redundancy is your issue, not MySQL issue.
    – Akina
    Commented Apr 21, 2021 at 5:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.