Using Autonumbered Fields

You saw in Chapter 1, "Understanding SQL and Relational Databases," that database systems provide a way of automatically generating numerical values for columns when you add new records to a table. These auto-generated fields can be (and usually are) used in conjunction with the PRIMARY KEY constraint.

To create such a field, you use the IDENTITY keyword with SQL Server, AUTO_INCREMENT for MySQL, GENERATED AS IDENTITY for DB2, and AUTOINCREMENT for Access.

To test this feature, you'll add a separate primary key field to the Friend table, named FriendID, of type INT. You want the database to automatically supply values to this column whenever you insert new rows to the table. You'll then insert a new row and retrieve and display the last auto-generated ID inserted.

SQL Server

In SQL Server, you use the IDENTITY keyword to create an autonumbered column. SQL Server allows you to retrieve the value of the last auto-generated number using the @@IDENTITY variable.

AUTONUMBERED COLUMNS (SQL SERVER)

This statement creates the table with an autonumbered primary key column for SQL Server:

CREATE TABLE Friend (
   FriendID INT IDENTITY PRIMARY KEY NOT NULL,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

With SQL Server, you can optionally specify a seed value (the first value that will be generated), and an increment value (which specifies by how much the auto-generated value increases on each iteration). By default, the seed and the increment are both one, but they can be specified as parameters of IDENTITY if you want to use other values. This is how you'd create the Friend table using a seed of 1,000 and an increment of one:

CREATE TABLE Friend (
   FriendID INT IDENTITY (1000, 1) PRIMARY KEY NOT NULL,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

Note
SQL Server doesn't allow you to supply values for IDENTITY columns manually—once you set a field as IDENTITY, you must let the database supply its values.

Now insert a new row into Friend using this statement:

INSERT INTO Friend (Name, PhoneNo) VALUES ('Mike', '123');

This inserts a new row into the Friend table with a FriendID value equal to the identity seed value. In SQL Server, you can retrieve this value by reading the @@IDENTITY variable:

PRINT @@IDENTITY;

Oracle

As we've mentioned, you need to use triggers in Oracle to perform this. You'll look at this Oracle example in more detail in Chapter 13, "Triggers," but we'll present the basic code here for reference.

A trigger is essentially a stored procedure that executes automatically whenever a particular operation is requested, such as the insertion of a new row into the database. You can set up a sequence, use a trigger to retrieve the next value from the sequence whenever a new row is inserted into the Friend table, and use this value as the auto-generated number.

AUTONUMBERED COLUMNS (ORACLE)

First, create the Friend table as normal:

CREATE TABLE Friend (
   FriendID INT PRIMARY KEY NOT NULL,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

Second, create the sequence and the trigger:

CREATE SEQUENCE SEQ;

CREATE OR REPLACE TRIGGER AUTONUMBER
BEFORE INSERT ON Friend
FOR EACH ROW
BEGIN
   SELECT SEQ.NEXTVAL
   INTO :NEW.FriendID FROM DUAL;
END;
/

You can now insert a new row. You don't need to specify the FriendID value because this will be supplied by the trigger before the row is inserted into the table. If you do supply a value for it, the supplied value will be overridden by the auto-generated value and simply ignored:

INSERT INTO Friend (Name, PhoneNo) VALUES ('Mike', '123');

You can now retrieve the new value by checking the current value of the sequence:

SELECT SEQ.CURRVAL FROM DUAL;

You use the WHERE clause so that you only have one row returned, not one row for every row in the table. The output from this query is as follows:

   CURRVAL

   --------
   1

DB2

In DB2, you can create autonumbered columns by specifying a GENERATED ALWAYS AS IDENTITY clause. If you do this, the database will always supply an automatically generated value for the identity column and throw an error if you attempt to provide another value manually. You can also use GENERATED BY DEFAULT AS IDENTITY, in which case you're allowed to supply your own values.

AUTONUMBERED COLUMNS (DB2)

The DB2 command to create the table is as follows:

CREATE TABLE Friend (
   FriendID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

Next, insert a new row into the table:

INSERT INTO Friend (Name, PhoneNo) VALUES ('Mike', '123');

This inserts a new row into the Friend table with a FriendID value of one. You can now retrieve the value using this query:

SELECT FriendID FROM Friend
WHERE FriendID = IDENTITY_VAL_LOCAL();

You do it this way to ensure that only one row is returned (if you said SELECT IDENTITY_VAL_LOCAL() FROM Friend, then one row would be returned for every row in the Friend table).

The output of this query is as follows:

   FriendID

   ---------
   1

MySQL

With MySQL, you use the AUTO_INCREMENT keyword to create an autonumbered field. MySQL is more permissive than SQL Server, and it allows you to provide your own values for the AUTO_INCREMENT field if you want.

AUTONUMBERED COLUMNS (MYSQL)

This MySQL statement creates the table:

CREATE TABLE Friend (
   FriendID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

Next, insert a new row into the table:

INSERT INTO Friend (Name, PhoneNo) VALUES ('Mike', '123');

You can now display the value of the auto-generated ID field using this query:

SELECT LAST_INSERT_ID();

The output from this is as follows:

   last_insert_id()

   ----------------
   1

Access

With Access, you use the AUTOINCREMENT keyword to create an autonumbered column.

Tip

With Access, AUTOINCREMENT is actually a separate data type rather than a column parameter. As such, when using AUTOINCREMENT you're not allowed to specify yet another data type (such as INT) as you did in the previous examples.

AUTONUMBERED COLUMNS (ACCESS)

This statement creates the table with an autonumbered primary key column for Access:

CREATE TABLE Friend (
   FriendID AUTOINCREMENT PRIMARY KEY NOT NULL,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

Optionally, you can specify as AUTOINCREMENT parameters a seed value (the first value that will be generated) and an increment value (which specifies by how much the auto-generated value increases on each iteration). By default, the seed and the increment are both one. This is how you'd create the Friend table using a seed of 1,000 and an increment of one:

CREATE TABLE Friend (
   FriendID AUTOINCREMENT (1000, 1) PRIMARY KEY NOT NULL,
   Name     VARCHAR(50),
   PhoneNo  VARCHAR(15) DEFAULT 'Unknown Phone');

Note
Access doesn't allow you to manually supply values for AUTOINCREMENT fields.