CODEWITHSUNDEEP
mysqlsqlindexingprimary-key
The Georgia
The Georgia

Reputation: 1075

Declaring MySQL PK as only Unique Key

Can creating a UNIQUE index on an Id as shown in the create table below be enough to make the id a Primary Key? TO be more specific, can you say that the table below has a Primary Key?

test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`role` varchar(32) NOT NULL,
`resources_name` varchar(32) NOT NULL,
`access_name` varchar(32) NOT NULL,
`allowed` int(3) NOT NULL,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `roles_name` (`role`,`resources_name`,`access_name`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8

What query can you use to prove that this has or has no PK?

Upvotes: 2

Views: 101

Answers (2)

nvogel
nvogel

Reputation: 25524

Logically speaking if a relational table has at least one candidate key enforced in it (minimally unique and non-nullable) then de facto it has a "primary" key. There is no absolute need to single out any one key using a special "primary" label because in principle all keys are equal (historically the term "primary key" used to be used for any and all candidate keys and not just one key per table).

There is a constraint in SQL called a PRIMARY KEY constraint. Logically speaking, the SQL PRIMARY KEY isn't much more than syntactical sugar because the NOT NULL UNIQUE syntax achieves essentially the same thing. Technically the PRIMARY KEY constraint doesn't have to refer to the same thing as the relational concept of a "primary key" but clearly if you are going to designate any one key as primary and if you feel you need a syntactical way of indicating that choice then the PRIMARY KEY constraint is the generally recognised way to do it.

So perhaps the best answer is "it depends". It depends to a large extent on your motivation for defining a primary key in the first place. If you intend to single out one key to developers and users of the database then maybe the NOT NULL UNIQUE syntax won't achieve that for you. If you don't find the need to do that using SQL syntax then maybe NOT NULL UNIQUE is just as good a way to define your keys as the PRIMARY KEY constraint is.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

This is either too long or too short for a comment: No.

A primary key and a unique key -- although similar -- are not the same. So, no your table does not have a primary key. The biggest functional difference is that primary keys cannot be NULL whereas unique keys can be.

Primary keys are also typically clustered (if the underlying storage engine supports clustered indexes). This means that the data is actually physically stored on the page in the order of the primary key. Unique keys are just another index with the characteristic of having no repeated values.

EDIT:

Interesting. SHOW COLUMNS documents this behavior:

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table.

I wasn't aware of this.

Upvotes: 0

Related Questions