What's the difference between the two DDLs in MySQL?

Question

create table categories(a integer unsigned NOT NULL,b integer unsigned NOT NULL,primary key(a,b));

And

create table categories(a integer unsigned NOT NULL,b integer unsigned NOT NULL,unique (a,b));

Are there any functional difference?

Answer 1

Yes, you can have only one primary key, but you can have as many unique indexes as you like.

Answer 2

Have a look at The difference between a unique index and primary key in MySQL

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL

Answer 3

Primary keys are like unique indexes except:

• Cannot contain NULLable columns
• Some storage engines treat them differently - InnoDB, for example, clusters the primary key, which can sometimes be a Good Thing, occasionally be a Bad Thing, but mostly not matter.

As they can't have NULLs, a primary key MUST uniquely identify a row.

Answer 4

Are there any functional difference?

in general the difference between a primary key and a unique constraint is that you can create the latter one on nullable columns. That is, you can create a primary key on columsn that are not explicitly declared NOT NULL, but these will automatically become non-nullable as a result of adding the primary key. Also, you can only have one primary key, but many unique constraints.

Now, in your sample code, both columns are NOT NULL anyway, so functionally there would not be any difference between the primary key and the unique constraint. But this apparent absence of difference applies only to the logical, relational properties of the table. There may still be differences at the storage engine level.

For example, the innodb storage engine uses clustered indexes: the table data is stored in the leaf nodes of the index created for the primary key. So if you have an innodb table without primary key innodb will still create one under the covers, and your unique index will point to enties in the primary key. Other engines like the NDB cluster engine also automatically creates a primary key if you don't define one explicitly, and in that case too, any secondary indexes like your unique index will point to the entires in the primary key. In both cases, these secondary indexes will generally be slower than when they would have been defined as primary key in the first place.

In MySQL, there is another difference relating to primary key and unique constraints. If you want to create and auto_increment column than that column must be part of the primary key (and typically, the primary key would consist of only the auto_increment column)

Apart from these technical differences, there is also a matter of convention to consider. It is considered good practice to alwyas define a primary key - basically you're saying, "this is the canonical way of identifying a row in this table". If you leave it out, it will generate confusion as it may look like you forgot to define one.