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:

insert into persons values ( John , Smith , LA , LA );

insert into persons values (null, John , Smith , LA , LA );

insert into persons values (auto, John , Smith , LA , LA );

but no luck

Answer 1

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 2

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 3

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

Answer 4

When loading a row into a table with auto-increment you can specify a value provided that value is NOT less than the highest value in that key. This works the same as a UNIQUE key and will error as if it is a duplicate. Otherwise it will treat the new loaded value as the new highest value for that key.

I am most curious to know why anyone would want to use a primary key without using auto-increment. The auto-increment makes for a most elegant way of avoiding duplicate data in any table. If the rest of the data is identical with another row in that table it can be identified and fixed/deleted. Used in combination with another row which is a unique key (to catch any duplicates) the table becomes very robust.

ianm

友情链接