Integer Surrogate Key?

Question

I need something real simple, that for some reason I am unable to accomplish up to this point.

I have also been unable to Google the answer surprisingly. I need to number the entries in different tables uniquely. I am aware of AUTO INCREMENT in MySQL and that it will be involved. My situation would be as follows:

If I had a table like

Table EXAMPLE

   ID - INTEGER
   FIRST_NAME - VARCHAR(45)
   LAST_NAME - VARCHAR(45)
   PHONE - VARCHAR(45)
   CITY - VARCHAR(45)
   STATE - VARCHAR(45)
   ZIP - VARCHAR(45)

This would be the setup for the table where ID is an integer that is auto-incremented every time an entry is inserted into the table. The thing I need is that I do not want to have to account for this field when inserting data into the database. From my understanding this would be a surrogate key, that I can tell the database to automatically increment and I do not have to include it in the INSERT STATEMENT

So, instead of:

 INSERT INTO EXAMPLE VALUES (2,'JOHN','SMITH',333-333-3333,'NORTH POLE'....

I can leave out the first ID column and just write something like:

INSERT INTO EXAMPLE VALUES ('JOHN','SMITH'.....etc)

Notice I wouldn't have to define the ID column...

I know this is a very common task to do, but for some reason I cant get to the bottom of it.

I am using MySQL, just to clarify.

Thanks a lot

Answer 1

Make ID auto_increment as shown here. You don't have to provide the value for an auto_increment field when inserting a row, the DB will provide it for you. Note that in this case you still have to use the insert syntax such that you provide the names of the fields, and you'd just leave out the ID field.

Answer 2

I don't know about mysql, but in SQL Server you would need to specify the fields you were inser'ing into in the insert (which you should do in all cases anyway as a best practice).

have you tried

insert into Example(field1, field2)
values ('John', 'Smith')

Answer 3

This:

INSERT INTO EXAMPLE VALUES ('JOHN','SMITH'.....etc)

...will return a MySQL Error #1136, because the number of columns in the table do not match the number of columns specified in the VALUES clause. So you have two options:

1. Use NULL to fill in the autoincrement column value. IE:

   INSERT INTO EXAMPLE VALUES (NULL, 'JOHN','SMITH'.....etc)
   

2. Specify the list of columns for the table that values are being provided for. IE:

   INSERT INTO EXAMPLE 
     (FIRST_NAME, LAST_NAME, PHONE, CITY, STATE, ZIP)
   VALUES 
     ('JOHN','SMITH'.....etc)