CODEWITHSUNDEEP
mysqlsqlprimary-keyduplicates
user3808021
user3808021

Reputation:

Duplicate entry for primary key on MySQL

My table just has single column ID (passwords for admin Log-in)

Because this code runs every time that program starts, I can prevent errors occurs on creating database and creating tables by using IF NOT EXIXTS statement.

Since adminLogin table should be initial first time, When user re-run the program, the Duplicate entry for primary key error occurs.

I used IF NOT EXISTS for inserting into table, But there is some another error!

My table:

first screen

Error:

second screen

Upvotes: 0

Views: 18820

Answers (4)

Josh Woodcock
Josh Woodcock

Reputation: 2783

INSERT IGNORE INSERT ... ON DUPLICATE KEY UPDATE ... REPLACE INTO can all work without erroring. Depending on your needs, some may be a better use than others.

Here's a brief pros and cons of each:

INSERT IGNORE: Pros: Easy to write Cons: if data that is being inserted is newer the older data will be preserved

INSERT ... ON DUPLICATE KEY UPDATE ... Pros: Can Insert or update specific columns that could have more recent data Cons: A little harder to write the query for

REPLACE INTO Pros: Can insert and update columns that could have more recent data. Faster than Insert, on duplicate key update. Cons: Overwrites every column even though only some columns may have newer data. IF you are inserting multiple rows replace into could potentially overwrite data for existing rows that you don't really want to overwrite.

Upvotes: 0

Swaden
Swaden

Reputation: 1

You can use INSERT IGNORE instead.

INSERT IGNORE INTO ADMINLOGIN VALUES(200);

Upvotes: 0

feelthhis
feelthhis

Reputation: 357

Another possibility

INSERT INTO adminLogin(ID) VALUES(100) ON DUPLICATE KEY UPDATE ID=ID;

Upvotes: 0

Andrzej Reduta
Andrzej Reduta

Reputation: 767

You are trying to insert same value. PK should be unique.

SET ID as autoincrement. 

CREATE TABLE `table_code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `your_column` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Upvotes: 3

Related Questions