How can I insert data without specifying a value for a non auto-increment primary key?

Question

I have this table:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)


1. insert into persons values (1, 'John', 'Smith', 'LA', 'LA');
2. insert into persons (p_id, lastname, firstname, address, city) values  (2, 'John', 'Smith', 'LV', 'LV');

How can I insert data into a table, without specifying a value for a primary key (the primary key doesn't have the "auto_increment" attribute).

I tried with:

1. insert into persons values ('John', 'Smith', 'LA', 'LA');
2. insert into persons values (null, 'John', 'Smith', 'LA', 'LA');
3. insert into persons values (auto, 'John', 'Smith', 'LA', 'LA');

but no luck

Answer 1

Primary keys cannot be null, and if you don't set them to auto increment, then how does MySQL know what you want to put in the table? You need to either specify the ID (perhaps by selecting the MAX(P_Id) and adding 1 to it) or set it to auto increment.

Although simply adding an auto increment field is definitely the best approach, you could try something like this:

INSERT INTO Persons (P_Id, FirstName, LastName, Address, City)
VALUES ((SELECT MAX(P_Id) + 1 FROM Persons), 'John', 'Smith', 'LA', 'LA');

This uses a subselect, so if you're using an old version of MySQL it may not work.

Answer 2

I have seen this implemented as

• DEFAULT value on the PK column (say -9999)
• A table that holds the Current_PK value
• An INSERT trigger that changes that PK from -9999 to Current_PK and increments Current_PK

Another way that I have seen is to get the MAX from the table and increment it and put it into the new row.

I must say, that both these methods caused bad data, caused locking and blocking and degraded performance.

In your case, I do not see a way other than actually specifying a value for PK.

Answer 3

Select p_id+1 From Person OrderBy p_id desc limit 1;