Database auto-increment column

Question

Hi,

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

On sql server this is called an identity column

Answer 2

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`)
);

Answer 3

Yes, you can do this. Here is a sample for SQL Server using IDENTITY:

CREATE TABLE MyTable (
  PrimaryKey varchar(10) PRIMARY KEY,
  IdentityColumn int IDENTITY(1,1) NOT NULL,
  DefaultColumn CHAR(1) NOT NULL DEFAULT ('N')
)

INSERT INTO MyTable (PrimaryKey) VALUES ('A')
INSERT INTO MyTable (PrimaryKey) VALUES ('B')
INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('C', 'Y')
INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('D', 'Y')
INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('E', DEFAULT)

--INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('F', NULL) -- ERROR
--> Cannot insert the value NULL into column 'DefaultColumn', table 'tempdb.dbo.MyTable'; column does not allow nulls. INSERT fails.

SELECT * FROM MyTable

Here is an example using SQL Server using functions to roll-your-own incrementing column. This is by means not fault tolerant or the way I would do it. (I'd use the identity feature.) However, it is good to know that you can use functions to return default values.

DROP TABLE MyTable
GO
DROP FUNCTION get_default_for_mytable
GO

CREATE FUNCTION get_default_for_mytable
()
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar int

    -- Add the T-SQL statements to compute the return value here
    SET @ResultVar = COALESCE((SELECT MAX(HomeBrewedIdentityColumn) FROM MyTable),0) + 1

    -- Return the result of the function
    RETURN @ResultVar

END
GO

CREATE TABLE MyTable (
  PrimaryKey varchar(10) PRIMARY KEY,
  IdentityColumn int IDENTITY(1,1) NOT NULL,
  DefaultColumn CHAR(1) NOT NULL DEFAULT ('N'),
  HomeBrewedIdentityColumn int NOT NULL DEFAULT(dbo.get_default_for_mytable())
)
GO

INSERT INTO MyTable (PrimaryKey) VALUES ('A')
INSERT INTO MyTable (PrimaryKey) VALUES ('B')
INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('C', 'Y')
INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('D', 'Y')
INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('E', DEFAULT)

--INSERT INTO MyTable (PrimaryKey, DefaultColumn) VALUES ('F', NULL) -- ERRROR
--> Cannot insert the value NULL into column 'DefaultColumn', table 'tempdb.dbo.MyTable'; column does not allow nulls. INSERT fails.

SELECT * FROM MyTable

Results

PrimaryKey IdentityColumn DefaultColumn HomeBrewedIdentityColumn
---------- -------------- ------------- ------------------------
A          1              N             1
B          2              N             2
C          3              Y             3
D          4              Y             4
E          5              N             5

Answer 4

I think you can have only 1 identity autoincrement column per table, this columns doesn't have to be the primary key but it would mean you have to insert the primary key yourself.

If you already have a primary key which is auto increment then I would try and use this if possible.

If you are trying to get an row ID to range on for querying then I would look at creating a view which has the row ID in it (not SQL 2000 or below).

Could you add in what your primary key is and what you intend to use the auto increment column for and it might help come up with a solution

Answer 5

Oracle and DB2 have sequence but I think you are looking for identity and all major dbms (mysql, sql server, db2, oracle) support it.