Cómo crear una columna IDENTITY en una tabla de SQL Server

Resumen: en este tutorial, aprenderás cómo usar la propiedad IDENTITY de SQL Server para agregar una columna de identidad a una tabla.

Introducción a la columna IDENTITY de SQL Server

Para crear una columna de identidad para una tabla, debes utilizar la propiedad IDENTITY de la siguiente manera:

IDENTITY[(semilla, incremento)]

En esta sintaxis:

• La semilla es el valor de la primera fila cargada en la tabla.
• El incremento es el valor incremental agregado al valor de identidad de la fila anterior.

El valor predeterminado de la semilla e incremento es 1, es decir, (1,1). Significa que la primera fila, que se cargó en la tabla, tendrá el valor uno, la segunda fila tendrá el valor de 2 y así sucesivamente.

Supongamos que deseas que el valor de la columna de identidad de la primera fila sea 10 y el valor incremental sea 10, usa la siguiente sintaxis:

IDENTITY (10,10)

Ten en cuenta que SQL Server te permite tener solo una columna de identidad por tabla.

Ejemplo de IDENTITY de SQL Server

Vamos a crear un nuevo esquema llamado hr para practicar:

CREATE SCHEMA hr;

La siguiente declaración crea una nueva tabla usando la propiedad IDENTITY para la columna del número de identificación personal:

CREATE TABLE hr.person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);

Primero, inserta una nueva fila en la tabla de personas:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Estrada','Web', 'G');

El resultado es:

Como se puede ver claramente en el resultado, la primera fila se ha cargado con el valor de uno en la columna person_id.

En segundo lugar, inserta otra fila en la tabla de personas:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jane','Doe','F');

El resultado es:

Como puedes ver claramente en el resultado, la segunda fila tiene el valor de dos en la columna person_id.

Reutilización de valores de identidad

SQL Server no reutiliza los valores de identidad. Si insertas una fila en la columna de identidad y la declaración de inserción falla o se revierte, el valor de identidad se pierde y no se generará nuevamente. Esto da como resultado lagunas en la columna de identidad.

Considera el siguiente ejemplo.

Primero, crea dos tablas más en el esquema de recursos humanos denominadas position y person_position:

CREATE TABLE hr. POSITION (
              position_id INT IDENTITY (1, 1) PRIMARY KEY,
              position_name VARCHAR (255) NOT NULL,
);

CREATE TABLE hr.person_position (
              person_id INT,
              position_id INT,
              PRIMARY KEY (person_id, position_id),
              FOREIGN KEY (person_id) REFERENCES hr.person (person_id),
              FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
);

En segundo lugar, inserta una nueva persona y asígnale un puesto a esta nueva persona insertando una nueva fila en la tabla person_position:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new person
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Joan','Smith','F');

        -- assign the person a position
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0 
            ROLLBACK TRANSACTION; 
    END CATCH
    IF @@TRANCOUNT > 0 
        COMMIT TRANSACTION;
GO

En este ejemplo, la primera instrucción de inserción se ejecutó correctamente. Sin embargo, el segundo falló debido a que no había ninguna posición con id uno en la tabla de posiciones. Debido al error, se revirtió toda la transacción.

Debido a que la primera declaración INSERT consumió el valor de identidad de tres y la transacción se revirtió, el siguiente valor de identidad será cuatro, como se muestra en la siguiente declaración:

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');

Resultado:

En este tutorial, aprendiste a usar la propiedad IDENTITY de SQL Server para crear una columna de identidad para una tabla.