CODEWITHSUNDEEP
mysqltriggersinsert-statement
user299648
user299648

Reputation: 2789

Can I have an MySQL database auto-assign a value to a column after an insert statement?

Given a table employee with columns ID(pk) and name

ID|name
--+-----
1 |John
2 |James
3 |Tom

Can I do

INSERT INTO employee (name) VALUES (Jack);

and somehow have the database auto assign the next available ID? Can a trigger do it? If so, how?

Upvotes: 3

Views: 3534

Answers (3)

Chad
Chad

Reputation: 19032

Create your table with an identity autoincrement column:

CREATE TABLE table_name
(
   id INTEGER AUTO_INCREMENT PRIMARY KEY,
   -- other columns to follow
)

Upvotes: 1

reggie
reggie

Reputation: 13721

You can use the AUTO INCREMENT Feature in My Sql. When you create the table, you can do something like this:

CREATE TABLE employee(
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

Or May be use the alter table Query to modify the column ID to have auto incremented values

Upvotes: 2

dcp
dcp

Reputation: 55449

You want auto increment column. You can refer to the documentation here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Upvotes: 2

Related Questions