Why is primary key a duplicate

Question

I am building a CRUD app in PHP & MySql. It uses two tables, called users and medical_records:

CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` varchar(255) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(1024) NOT NULL,
`validation_code` text NOT NULL,
`active` tinyint(1) NOT NULL,
`telefon` varchar(255) DEFAULT NULL,
`oras` varchar(255) DEFAULT NULL,
`adresa` text,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE medical_records (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `fo` VARCHAR(255),
  `condition` VARCHAR(255),
  `transfer` VARCHAR(255),
  `memo` text(1024),
  `body_temperature` VARCHAR(255),
  PRIMARY KEY  (`id`),
  CONSTRAINT FK_medical_records_1
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

The "business logic" of these tables is: every user has one (only one) medical record.

I used and tested the application when it only had the users table. Register and login worked fine, so I conclude that the problem's source can't be the application's (PHP) code.

I later doped the users table and added the 2 fresh tables the application now uses (users and medical_records).

At this moment whenever I try to register a second user, I get the error:

QUERY FAILED: Duplicate entry '0' for key 'PRIMARY'

This happens despite the fact that both tables have auto incremented primary keys. What could be the explanation of that?

You say that both tables have auto-incremented primary keys, but I don't see any auto-increment in your `users` table. — ksbg, Jan 24 '17 at 12:16
If this problem persists after you fix the auto increment problem in your first table, please [edit] your question to show the sequence of queries used to register new users. — O. Jones, Jan 24 '17 at 12:20
Truncate with the table and try again. SET FOREIGN_KEY_CHECKS=0; TRUCATE `users`; TRUCATE `medical_records`; SET FOREIGN_KEY_CHECKS=1; — Shiv Kumar Sah, Jan 24 '17 at 12:27

score 2 · Accepted Answer · answered Jan 24 '17 at 12:16

2

the id in the users table is not set to AUTO_INCREMENT. that's why this happens.

answered Jan 24 '17 at 12:16

low_rents

4,481
3
27
55

Silly me! Thank you! :) – Razvan Zamfir Jan 24 '17 at 12:27

Why is primary key a duplicate

1 Answers1