Can we have multiple primary keys in a single table?


No we Cannot.
For example,

CREATE TABLE IF NOT EXISTS `usr` (
`id` int(11) NOT NULL,
`survey_id` int(11) NOT NULL,
`number` int(11) NOT NULL,
`count` int(11) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`,`survey_id`,`number`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Basically, this appears to be creating four primary key on one table.

We could create a number of unique keys in one table, but only one primary key.

Then how is it that your system is allowing the creation of multiple primary keys?

Think of it like it suggest, a ‘KEY’. So the key would be all of the columns specified. In your case you can have multiple rows with the same ‘ID’ and multiple rows with the same ‘survey_id’ but there shall never be two rows that have the same ‘ID’ AND ‘survey_id’.

So in this case it is not saying that the column ‘ID’ must be unique nor is it saying that ‘survey_id’ must be unique, but only the combination.

Like if you write an query

INSERT INTO `usr` (`id`, `survey_id`, `number`, `count`, `date`) VALUES (1, 1, 123456, 50, ‘2013-04-01’);

And after this you again run this query then you are getting an error “#1062 – Duplicate entry ‘1-1-123456-50’ for key ‘PRIMARY'”

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.