CODEWITHSUNDEEP
mysqldatabaseprimary-key
Gaurav Wadhwani
Gaurav Wadhwani

Reputation: 378

""Duplicate Entry" while inserting in a Composite primary keyed table

CREATE TABLE IF NOT EXISTS `mytable` (
  `machine_no` varchar(50) CHARACTER SET ascii NOT NULL,
  `date` datetime NOT NULL,
  `nature` int(11) DEFAULT NULL,
  `start` time NOT NULL,
  PRIMARY KEY (`machine_no`,`date`),
  UNIQUE KEY `date` (`date`),
  UNIQUE KEY `start` (`start`),
  UNIQUE KEY `start_2` (`start`),
  UNIQUE KEY `nature` (`nature`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This table has a composite key.

When I try to insert 2 records with same date/time but different value of machine_no. It says duplicate entry for Date. I dont understand the reason for it. As it a composite key, it should only look for duplicate entry for in both the attributes.

Upvotes: 1

Views: 1465

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Your PRIMARY KEY works fine:

PRIMARY KEY (`machine_no`,`date`),

What causes the issue is the UNIQUE KEY you have:

UNIQUE KEY `date` (`date`),

This does not allow two rows with same datetime to be inserted.

Similarly your other 3 unique keys will probably cause you torubles as well:

UNIQUE KEY `start` (`start`),
UNIQUE KEY `start_2` (`start`),
UNIQUE KEY `nature` (`nature`)

So, make those keys simple (not Unique).

And there is no reason to have two identical keys which differ in name only (start and start_2)

Upvotes: 1

Related Questions