
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:
person_id | first_name | last_name | gender |
---|---|---|---|
1 | Estrada | Web | G |
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:
person_id | first_name | last_name | gender |
---|---|---|---|
1 | Estrada | Web | G |
2 | Jane | Doe | F |
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:
person_id | first_name | last_name | gender |
---|---|---|---|
1 | Estrada | Web | G |
2 | Jane | Doe | F |
4 | Peter | Drucker | F |
En este tutorial, aprendiste a usar la propiedad IDENTITY
de SQL Server para crear una columna de identidad para una tabla.