Database auto-increment column

Question

Is it possible to create a Database which has 1 column (but not the column of primary key) to be auto-increment? So that when I insert value to the database, i don't need to fill in the value myself, and DB will fill in that value for that column for me (and increment every time I do a new insert)?

Thank you.

Answer 1

Yes, of course it is possible. Just make this column a unique key (not a primary key) and it has to be declared with a special attribute: "IDENTITY" for SQL Server, and "AUTO_INCREMENT" for MySQL (see the example below) . And another column can be a primary key.

On MySQL database the table could be declared like this:

CREATE TABLE `mytable` (
  `Name` VARCHAR(50) NOT NULL,
  `My_autoincrement_column` INTEGER(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Name`),
  UNIQUE KEY `My_autoincrement_column` (`My_autoincrement_column`)
);