Tipo de dato de la llave primaria
Cual es la mejor opción para crear llaves primarias, es decir, las declaras de tipo identity, de tipo char(n), o utilizar la instrucción NewId().
¿Cuál es más adecuado para manejar la llave primaria?
¿Cuál es más adecuado para manejar la llave primaria?
1 respuesta
Respuesta de denciso
1
Sugiero que leas el siguiente segmento de la ayuda en línea de sql server para que decidas que es lo mejor para crear llaves primarias...
Create table
Crea una nueva tabla.
Sintaxis
Create table
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[WITH FILLFACTOR = fillfactor]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
Argumentos
database_name
Nombre de la base de datos en la que se crea la tabla. Database_name debe especificar el nombre de una base de datos existente. Si no se especifica, database_name es, de forma predeterminada, la base de datos actual. El inicio de sesión de la conexión actual debe estar asociado con un Id. De usuario existente en la base de datos especificada por database_name, y ese Id. De usuario debe disponer de privilegios de creación de tablas.
Owner
El nombre del Id. De usuario que es propietario de la nueva tabla. Owner debe ser un Id. De usuario existente en la base de datos especificada por database_name. Owner es, de forma predeterminada, el Id. De usuario asociado con el inicio de sesión de la conexión actual en la base de datos especificada en database_name. Si un miembro de la función fija de servidor sysadmin o un miembro de las funciones fijas de base de datos db_dbowner o db_ddladmin ejecutan la instrucción CREATE TABLE en la base de datos especificada por database_name, owner puede especificar un Id. De usuario distinto del que está asociado con el inicio de sesión de la conexión actual. Si un inicio de sesión asociado a un Id. De usuario que sólo tiene permisos para crear tablas ejecuta la instrucción CREATE TABLE, owner debe especificar el Id. De usuario asociado al inicio de sesión actual. Los miembros de la función fija de servidor sysadmin o los inicios de sesión con el alias de usuario dbo se asocian con el dbo del Id. De usuario; por tanto, de forma predeterminada, las tablas creadas por estos usuarios tienen dbo como propietario. De forma predeterminada, las tablas creadas por los inicios de sesión que no se encuentren en ninguna de estas dos funciones tienen como owner el Id. De usuario asociado con el inicio de sesión.
table_name
Es el nombre de la nueva tabla. Los nombres de tablas se deben ajustar a las reglas para los identificadores. La combinación de owner. Table_name debe ser única en la base de datos. Table_name puede contener un máximo de 128 caracteres, excepto para los nombres de tablas temporales locales (nombres precedidos con un signo numérico simple (#)) que no pueden exceder de 116 caracteres.
column_name
Es el nombre de una columna de la tabla. Los nombres de columna deben cumplir las reglas de los identificadores y deben ser únicos en la tabla. Column_name puede omitirse para las columnas creadas con un tipo de datos timestamp. De forma predeterminada, el nombre de una columna timestamp es timestamp si no se especifica column_name.
computed_column_expression
Es una expresión que define el valor de una columna calculada. Una columna calculada es una columna virtual no almacenada físicamente en la tabla. Se calcula a partir de una expresión mediante otras columnas de la misma tabla. Por ejemplo, una columna calculada puede tener la definición: cost AS price * qty. La expresión puede ser un nombre de columna no calculada, una constante, una función, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta.
Las columnas calculadas se pueden utilizar en las listas de selección, cláusulas WHERE, cláusulas ORDER BY u otros lugares en que se puedan utilizar expresiones regulares, con las siguientes excepciones:
Una columna calculada no puede utilizarse como definición de restricción DEFAULT o FOREIGN KEY ni como NOT NULL. No obstante, es posible utilizar una columna calculada como columna de clave en un índice o como parte de una restricción PRIMARY KEY o UNIQUE, si el valor de la columna calculada está definido mediante una expresión determinista y el tipo de datos del resultado es válido en columnas indizadas.
Por ejemplo, si la tabla tiene las columnas de tipo entero a y b, la columna calculada a+b se puede indizar, pero la columna calculada a+DATEPART(dd, GETDATE()) no, porque el valor puede variar en llamadas posteriores.
Una columna calculada no puede ser el destino de una instrucción INSERT o UPDATE.
Nota Debido a que cada fila de una tabla puede tener distintos valores para las columnas implicadas en una columna calculada, la columna calculada puede no tener el mismo valor para cada fila.
SQL Server determina de forma automática si se aceptan valores NULL en las columnas calculadas, según las expresiones utilizadas. Se considera que el resultado de la mayoría de las expresiones acepta valores NULL incluso si únicamente están presentes las columnas que no aceptan valores NULL, ya que los posibles desbordamientos también darán como resultado valores NULL Utilice la función COLUMNPROPERTY (propiedad AllowsNull) para examinar si las columnas calculadas de una tabla aceptan valores NULL. Una expresión expr que acepta valores NULL puede pasar a no aceptar valores NULL, si se especifica ISNULL(check_expression, constante), donde la constante sea un valor no NULL sustituido por cualquier resultado NULL.
ON {filegroup | DEFAULT}
Especifica el grupo de archivos en el que se almacena la tabla. Si se especifica filegroup, la tabla se almacena en el grupo de archivos mencionado. El grupo de archivos debe existir en la base de datos. Si se especifica DEFAULT o si ON no se especifica en ninguna parte, la tabla se almacena en el grupo de archivos predeterminado.
ON {filegroup | DEFAULT} se puede especificar también en una restricción PRIMARY KEY o UNIQUE. Estas restricciones crean índices. Si se especifica filegroup, el índice se almacena en el grupo de archivos mencionado. Si se especifica DEFAULT, el índice se almacena en el grupo de archivos predeterminado. Si en una restricción no se especifica un grupo de archivos, el índice se almacena en el mismo grupo de archivos que la tabla. Si la restricción PRIMARY KEY o UNIQUE crea un índice agrupado, las páginas de datos de la tabla se almacenan en el mismo grupo de archivos que el índice.
Nota DEFAULT, en el contexto de ON {filegroup | DEFAULT} y TEXTIMAGE_ON {filegroup | DEFAULT}, no es una palabra clave. DEFAULT es un identificador del grupo de archivos predeterminado y se debe delimitar, como en ON "DEFAULT" u ON [DEFAULT] y TEXTIMAGE_ON "DEFAULT" o TEXTIMAGE_ON [DEFAULT].
TEXTIMAGE_ON
Son palabras clave que indican que las columnas text, ntext e image están almacenadas en el grupo de archivos especificado. TEXTIMAGE ON no se admite si no hay columnas text, ntext o image en la tabla. Si no se especifica TEXTIMAGE_ON, las columnas text, ntext e image se almacenan en el mismo grupo de archivos que la tabla.
data_type
Especifica el tipo de datos de la columna. Se permiten los tipos de datos del sistema o definidos por el usuario. Los tipos de datos definidos por el usuario se crean con sp_addtype antes de que puedan utilizarse en una definición de tabla.
La asignación NULL o NOT NULL de un tipo de datos definido por el usuario puede anularse durante la instrucción CREATE TABLE. Sin embargo, la especificación de la longitud no se puede cambiar; en una instrucción CREATE TABLE no se puede especificar la longitud de un tipo de datos definido por el usuario.
DEFAULT
Especifica el valor suministrado para la columna cuando no se ha especificado explícitamente un valor durante la inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna, excepto a las definidas como timestamp o aquellas que tengan la propiedad IDENTITY. Las definiciones DEFAULT se quitan cuando se quita la tabla. Como valor predeterminado sólo se puede utilizar un valor constante (por ejemplo, una cadena de caracteres), una función del sistema (como SYSTEM_USER()), o NULL. Para mantener la compatibilidad con las versiones anteriores de SQL Server, se puede asignar un nombre de restricción a DEFAULT.
constant_expression
Es una constante, NULL o una función del sistema utilizados como el valor predeterminado de una columna.
Identity
Indica que la nueva columna es una columna identidad. Cuando se agrega una nueva fila a la tabla, ¿Microsoft® SQL Server? Proporciona un valor incremental y único a la columna. Las columnas de identidad se utilizan normalmente junto con restricciones PRIMARY KEY para que actúen como identificador exclusivo de fila para la tabla. La propiedad IDENTITY puede asignarse a las columnas tinyint, smallint, int, bigint, decimal(p, 0) o numeric(p, 0). Sólo se puede crear una columna de identidad por tabla. Los valores predeterminados enlazados y las restricciones DEFAULT no se pueden utilizar con una columna identidad. Es necesario especificar la inicialización y el incremento, o no especificar ninguno de los dos. Si no se especifica ninguno, el valor predeterminado es (1,1).
Seed
Es el valor que se utiliza para la primera fila cargada en la tabla.
Increment
Es el valor incremental que se agrega al valor de identidad de la anterior fila cargada.
NOT FOR REPLICATION
Indica que la propiedad IDENTITY no debe exigirse cuando un inicio de sesión duplicado, como sqlrepl, inserta datos en la tabla. Las filas duplicadas deben conservar los valores de clave que se les asignó en la base de datos de publicación; la cláusula NOT FOR REPLICATION asegura que las filas insertadas por un proceso de duplicación no reciban nuevos valores de identidad. Las filas insertadas por otros inicios de sesión continúan recibiendo los nuevos valores de identidad de la forma habitual. Se recomienda que defina también una restricción CHECK con NOT FOR REPLICATION para asegurar que los valores de identidad asignados se encuentran en el intervalo deseado de la base de datos actual.
Rowguidcol
Indica que la nueva columna es una columna de identificador exclusivo global de fila. Únicamente se puede designar una columna uniqueidentifier por cada tabla como la columna ROWGUIDCOL. La propiedad ROWGUIDCOL se puede asignar únicamente a una columna uniqueidentifier. La palabra clave ROWGUIDCOL no es válida si el nivel de compatibilidad de la base de datos es 65 o inferior. Para obtener más información, consulte sp_dbcmptlevel.
La propiedad ROWGUIDCOL no impone la unicidad de los valores almacenados en la columna. Del mismo modo, tampoco genera automáticamente valores para nuevas filas insertadas en la tabla. Para generar valores únicos para cada columna, utilice la función NEWID de las instrucciones INSERT o utilice la función NEWID como el valor predeterminado de la columna.
collation_name
Especifica la intercalación de la columna. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. El argumento collation_name sólo se aplica a columnas de los tipos de datos char, varchar, text, nchar, nvarchar y ntext. Si no se especifica, se asignará a la columna la intercalación del tipo de datos definido por el usuario, si la columna es de un tipo de datos definido por el usuario, o la intercalación predeterminada de la base de datos.
Para obtener más información acerca de los nombres de intercalación de Windows y SQL, consulte COLLATE
constraint
Es una palabra clave opcional que indica el principio de la definición de una restricción PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY o CHECK. Las restricciones son propiedades especiales que exigen la integridad de los datos y pueden crear índices para la tabla y sus columnas.
constraint_name
Es el nombre de una restricción. Los nombres de restricción deben ser únicos en una base de datos.
NULL | NOT NULL
Son palabras clave que determinan si se permiten o no valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar de la misma forma que NOT NULL.
Primary key
Es una restricción que exige la integridad de entidad para una o varias columnas dadas a través de un índice único. Sólo se puede crear una restricción PRIMARY KEY por cada tabla.
Unique
Es una restricción que proporciona la integridad de entidad para una o varias columnas dadas a través de un índice único. Una tabla puede tener varias restricciones UNIQUE.
CLUSTERED | NONCLUSTERED
Son palabras clave que indican que se ha creado un índice agrupado o no agrupado para la restricción PRIMARY KEY o UNIQUE. De forma predeterminada, el valor de las restricciones PRIMARY KEY es CLUSTERED, y el de las restricciones UNIQUE es NONCLUSTERED.
Sólo se puede especificar CLUSTERED para una única restricción de una instrucción CREATE TABLE. Si especifica CLUSTERED para una restricción UNIQUE y especifica también una restricción PRIMARY KEY, el valor predeterminado de PRIMARY KEY es NONCLUSTERED.
[WITH FILLFACTOR = fillfactor]
Especifica cuánto se debe llenar cada página de índice de SQL Server utilizada para almacenar los datos de índice. Los valores de fillfactor especificados por el usuario pueden estar entre 1 y 100; el valor predeterminado es 0. Un factor de relleno inferior crea el índice con más espacio disponible para las nuevas entradas de índice sin tener que asignar nuevo espacio.
foreign key... references
Es una restricción que proporciona integridad referencial para los datos de la columna o columnas. Las restricciones FOREIGN KEY requieren que cada valor de la columna exista en la columna de referencia correspondiente de la tabla a la que se hace referencia. Las restricciones FOREIGN KEY pueden hacer referencia sólo a columnas que sean restricciones PRIMARY KEY o UNIQUE en la tabla de referencia o a columnas a las que se haga referencia en UNIQUE INDEX en la tabla de referencia.
ref_table
Es el nombre de la tabla a la que hace referencia la restricción FOREIGN KEY.
(ref_column[,...n])
Es una columna o lista de columnas de la tabla a la que hace referencia la restricción FOREIGN KEY.
ON DELETE {CASCADE | NO ACTION}
Especifica qué acción tiene lugar en una fila de la tabla creada, si esa fila tiene una relación referencial y la fila a la que hace referencia se elimina en la tabla primaria. El valor predeterminado es NO ACTION.
Si se especifica CASCADE y se elimina una fila de la tabla primaria, también se elimina la fila de la tabla desde donde se hace referencia. Si se especifica NO ACTION, SQL Server genera un error y se deshace la acción de eliminación de la fila en la tabla primaria.
Por ejemplo, en la base de datos Northwind, la tabla Orders tiene una relación de integridad referencial con la tabla Customers. La clave externa Orders. CustomerID hace referencia a la clave principal Customers. CustomerID.
Si se ejecuta una instrucción DELETE en una fila de la tabla Customers y se especifica la acción ON DELETE CASCADE para Orders. CustomerID, SQL Server comprueba las filas dependientes de la tabla Orders. Si las hay, las filas dependientes de la tabla Orders se eliminan, así como la fila a la que se hace referencia en la tabla Customers.
Por otra parte, si se especifica NO ACTION, SQL Server genera un error y deshace la acción de eliminación de la fila Customers si al menos hay una fila en la tabla Orders que haga referencia a la fila Customers.
ON UPDATE {CASCADE | NO ACTION}
Especifica qué acción tiene lugar en una fila de la tabla creada, si esa fila tiene una relación referencial y la fila a la que hace referencia se actualiza en la tabla primaria. El valor predeterminado es NO ACTION.
Si se especifica CASCADE, la fila se actualiza en la tabla de referencia si esa fila se actualiza en la tabla primaria. Si se especifica NO ACTION, SQL Server genera un error y se deshace la acción de actualización en la fila de la tabla primaria.
Por ejemplo, en la base de datos Northwind, la tabla Orders tiene una relación referencial con la tabla Customers: la clave externa Orders. CustomerID hace referencia a la clave principal Customers. CustomerID.
Si se ejecuta una instrucción UPDATE en una fila de la tabla Customers y se especifica la acción ON UPDATE CASCADE para Orders. CustomerID, SQL Server comprueba las filas dependientes de la tabla Orders. Si las hay, las filas dependientes de la tabla Orders se actualizan, así como la fila a la que se hace referencia en la tabla Customers.
Por el contrario, si se especifica NO ACTION, SQL Server emite un error y deshace la acción de actualización de la fila Customers si al menos una fila de la tabla Orders hace referencia a ella.
CHECK
Es una restricción que exige la integridad del dominio al limitar los valores posibles que se pueden escribir en una o varias columnas.
NOT FOR REPLICATION
Palabras clave que se utilizan para impedir que se exija la restricción CHECK durante el proceso de distribución utilizado por la duplicación. Cuando las tablas son suscriptores de una publicación de duplicación, no actualice directamente la tabla de suscripción; en su lugar, actualice la tabla de publicación y deje que la duplicación vuelva a distribuir los datos en la tabla de suscripción. En la tabla de suscripción se puede definir una restricción CHECK para impedir que los usuarios la modifiquen. Sin embargo, a menos que se agregue la cláusula NOT FOR REPLICATION, la restricción CHECK impide también que el proceso de duplicación distribuya las modificaciones de la tabla de publicación a la tabla de suscripción. La cláusula NOT FOR REPLICATION significa que la restricción se exige en las modificaciones de los usuarios, pero no en el proceso de duplicación.
La restricción NOT FOR REPLICATION CHECK se aplica tanto a la imagen anterior como posterior de un registro actualizado para impedir que se agreguen o eliminen registros del intervalo duplicado. Se comprueban todos los borrados e inserciones; si éstos se encuentran en el intervalo duplicado, se rechazan.
Cuando esta restricción se utiliza con una columna de identidad, SQL Server permite que la tabla no tenga que reinicializar los valores de columna de identidad cuando un usuario de duplicación la actualiza.
logical_expression
Es una expresión lógica que devuelve TRUE o FALSE.
Column
Es una columna o lista de columnas, entre paréntesis, que se utiliza en las restricciones de tabla para indicar las columnas que se están utilizando en la definición de la restricción.
[ASC | DESC]
Especifica cómo se ordenan la columna o las columnas que participan en las restricciones de la tabla. El valor predeterminado es ASC.
n
Es un marcador de posición que indica que el elemento anterior se puede repetir n veces.
Observaciones
SQL Server puede tener hasta dos mil millones de tablas por cada base de datos y 1.024 columnas por tabla. El número de filas y el tamaño total de la tabla está limitado solamente por el espacio de almacenamiento disponible. El número máximo de bytes por fila es de 8.060. Si se crean tablas con columnas varchar, nvarchar o varbinary en las que el ancho total definido excede de 8.060 bytes, se crea la tabla, pero aparece un mensaje de advertencia. Al intentar insertar más de 8.060 bytes en tal fila o actualizar una fila de tal forma que su tamaño total de fila exceda de 8.060, se genera un mensaje de error y no se puede realizar la instrucción.
Las instrucciones CREATE TABLE que incluyen una columna sql_variant pueden generar la siguiente advertencia:
The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Esta advertencia se produce porque sql_variant puede tener una longitud máxima de 8016 bytes. Cuando una columna sql_variant contiene valores cercanos a la longitud máxima, puede superar el límite del tamaño máximo de la fila.
Cada tabla puede contener un máximo de 249 índices no agrupados y 1 índice agrupado. Éstos incluyen los índices generados para admitir las restricciones PRIMARY KEY y UNIQUE definidas para la tabla.
SQL Server no impone un orden en que DEFAULT, IDENTITY, ROWGUIDCOL o las restricciones de columna se especifican en una definición de columna.
Tablas temporales
Se pueden crear tablas temporales locales y globales. Las tablas temporales locales son visibles sólo en la sesión actual; las tablas temporales globales son visibles para todas las sesiones.
Coloque un prefijo de signo numérico simple (#table_name) en los nombres de las tablas temporales locales y un prefijo de un signo numérico doble (##table_name) en los nombres de las tablas temporales globales.
Las instrucciones SQL hacen referencia a la tabla temporal mediante el valor especificado por table_name en la instrucción CREATE TABLE:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
Si se crea una tabla temporal local en un procedimiento almacenado o una aplicación que varios usuarios pueden ejecutar al mismo tiempo, SQL Server tiene que ser capaz de distinguir las tablas creadas por los distintos usuarios. SQL Server hace esto al anexar internamente un sufijo numérico a cada nombre de tabla temporal local. El nombre completo de una tabla temporal tal como se almacena en la tabla sysobjects de tempdb consta del nombre de la tabla especificado en la instrucción CREATE TABLE y el sufijo numérico generado por el sistema. Para permitir que se agregue el sufijo, el table_name especificado de un nombre temporal local no puede exceder de 116 caracteres.
Las tablas temporales se quitan automáticamente cuando están fuera de ámbito, a menos que ya se hayan quitado explícitamente mediante DROP TABLE:
Una tabla temporal local creada en un procedimiento almacenado se quita automáticamente cuando se completa el procedimiento almacenado. Cualquiera de los procedimientos almacenados anidados ejecutados por el procedimiento almacenado que creó la tabla puede hacer referencia a la tabla. El proceso que llamó al procedimiento almacenado que creó la tabla no puede hacer referencia a la tabla.
Todas las demás tablas temporales se quitan automáticamente al final de la sesión actual.
Las tablas temporales globales se quitan automáticamente cuando la sesión que creó la tabla finaliza y todas las tareas restantes han dejado de hacer referencia a ellas. La asociación entre una tarea y una tabla se mantiene sólo durante la vida de una única instrucción Transact-SQL. Esto significa que la tabla temporal global se quita al finalizar la última instrucción Transact-SQL que estuviera haciendo referencia activamente a la tabla cuando finalizó la sesión que la creó.
Una tabla temporal local creada en un procedimiento almacenado o desencadenador es distinta de una tabla temporal que tenga el mismo nombre y que se haya creado antes de que se llame al procedimiento almacenado o al desencadenador. Si una consulta hace referencia a una tabla temporal y hay dos tablas temporales con el mismo nombre, no está definido en cual de las dos tablas debe resolverse la consulta. Los procedimientos almacenados anidados pueden crear también tablas temporales con el mismo nombre que la tabla temporal creada por el procedimiento almacenado que la llamó. Todas las referencias al nombre de la tabla que se efectúan en el procedimiento almacenado anidado se resuelven en la tabla creada en el procedimiento almacenado, por ejemplo:
CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO
El siguiente es el conjunto de resultados:
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
Cuando se crean tablas temporales globales o locales, la sintaxis CREATE TABLE admite la definición de restricciones, excepto las restricciones FOREIGN KEY. Si en una tabla temporal se especifica una restricción FOREIGN KEY, la instrucción devuelve un mensaje de advertencia que indica que la restricción se saltó y que la tabla se ha creado sin las restricciones FOREIGN KEY. En las restricciones FOREIGN KEY no se puede hacer referencia a tablas temporales.
Considere la posibilidad de utilizar variables de tabla en lugar de tablas temporales. Las tablas temporales son útiles cuando es necesario crear en ellas índices de forma explícita, o bien cuando los valores de tabla deben ser visibles en varios procedimientos almacenados o funciones. En general, las variables de tabla contribuyen a que el procesamiento de las consultas sea más eficaz. Para obtener más información, consulte table.
Restricciones PRIMARY KEY
Una tabla puede contener una sola restricción PRIMARY KEY.
El índice generado por una restricción PRIMARY KEY no puede hacer que el número de índices de la tabla exceda de 249 índices no agrupados y 1 índice agrupado.
Si no se especifica CLUSTERED o NONCLUSTERED para una restricción PRIMARY KEY, se utiliza CLUSTERED si no hay índices agrupados especificados para las restricciones UNIQUE.
Todas las columnas definidas en una restricción PRIMARY KEY deben establecerse como NOT NULL. Si no se especifica la posibilidad de aceptar NULL, todas las columnas que participan en una restricción PRIMARY KEY tienen su posibilidad de aceptar NULL establecida en NOT NULL.
Restricciones UNIQUE
Si no se especifica CLUSTERED o NONCLUSTERED para una restricción UNIQUE, de forma predeterminada se utiliza NONCLUSTERED.
Cada restricción UNIQUE genera un índice. El número de restricciones UNIQUE no puede hacer que el número de índices de la tabla exceda de 249 índices no agrupados y 1 índice agrupado.
Restricciones FOREIGN KEY
Cuando en la columna de una restricción FOREIGN KEY se introduce un valor distinto de NULL, el valor debe existir en la columna a la que se hace referencia; de lo contrario, se devuelve un mensaje de error de infracción de clave externa.
Las restricciones FOREIGN KEY se aplican a la columna anterior a menos que se especifiquen columnas de origen.
Las restricciones FOREIGN KEY sólo pueden hacer referencia a las tablas de la misma base de datos en el mismo servidor. La integridad referencial de las bases de datos cruzadas debe implementarse a través de desencadenadores. Para obtener más información, consulte CREATE TRIGGER.
Las restricciones FOREIGN KEY pueden hacer referencia a otras columnas de la misma tabla (una auto-referencia).
La cláusula REFERENCES de una restricción FOREIGN KEY en el nivel de columna puede enumerar sólo una columna de referencia, que debe tener el mismo tipo de datos que la columna en la que se define la restricción.
La cláusula REFERENCES de una restricción FOREIGN KEY en el nivel de tabla debe tener el mismo número de columnas de referencia que el número de columnas de la lista de columnas de restricción. El tipo de datos de cada columna de referencia debe ser también el mismo que la columna correspondiente de la lista de columnas.
Es posible que no se pueda especificar CASCADE si una columna del tipo timestamp forma parte de la clave externa o de la clave a la que se hace referencia.
Se puede combinar CASCADE y NO ACTION en tablas que tengan relaciones referenciales entre sí. Si SQL Server encuentra NO ACTION, termina y deshace las acciones CASCADE relacionadas. Cuando una instrucción DELETE hace que se combinen acciones CASCADE y NO ACTION, todas las acciones CASCADE se aplican antes de que SQL Server compruebe si hay cláusulas NO ACTION.
Una tabla puede contener un máximo de 253 restricciones FOREIGN KEY.
Las restricciones FOREIGN KEY no se exigen en tablas temporales.
Una tabla puede hacer referencia a un máximo de 253 tablas distintas en sus restricciones FOREIGN KEY.
Las restricciones FOREIGN KEY sólo pueden hacer referencia a las columnas de las restricciones PRIMARY KEY o UNIQUE de la tabla de referencia o a las columnas en UNIQUE INDEX de la tabla de referencia.
Definiciones DEFAULT
Una columna sólo puede tener una definición DEFAULT.
Una definición DEFAULT puede contener valores constantes, funciones, funciones niládicas SQL-92 o NULL. La tabla muestra las funciones niládicas y los valores que devuelven para el valor predeterminado durante la ejecución de una instrucción INSERT. Función niládica SQL-92 Valor devuelto
CURRENT_TIMESTAMP Fecha y hora actuales.
CURRENT_USER Nombre del usuario que realiza la inserción.
SESSION_USER Nombre del usuario que realiza la inserción.
SYSTEM_USER Nombre del usuario que realiza la inserción.
USER Nombre del usuario que realiza la inserción.
En una definición DEFAULT, constant_expression no puede hacer referencia a otra columna de la tabla o a otras tablas, vistas o procedimientos almacenados.
Las definiciones DEFAULT no se pueden crear sobre columnas con un tipo de datos timestamp o columnas con una propiedad IDENTITY.
Las definiciones DEFAULT no se pueden crear para columnas con tipos de datos definidos por el usuario, si éstos están enlazados a un objeto predeterminado.
Restricciones CHECK
Una columna puede tener cualquier número de restricciones CHECK y la condición puede incluir varias expresiones lógicas combinadas con AND y OR. Varias restricciones CHECK para una columna se validan en el orden en que se crean.
La condición de búsqueda debe dar como resultado una expresión booleana y no puede hacer referencia a otra tabla.
Una restricción CHECK en el nivel de columna sólo puede hacer referencia a la columna restringida, y una restricción CHECK en el nivel de tabla sólo puede hacer referencia a columnas de la misma tabla.
Las restricciones CHECK y las reglas sirven para la misma función de validación de los datos durante las instrucciones INSERT y DELETE.
Cuando hay una regla y una o más restricciones CHECK para una columna o columnas, se evalúan todas las restricciones.
Información adicional de restricción
Un índice creado para una restricción no se puede quitar con la instrucción DROP INDEX; la restricción debe quitarse con la instrucción ALTER TABLE. Un índice creado y utilizado por una restricción puede volverse a generar con la instrucción DBCC DBREINDEX.
Los nombres de restricción deben seguir las reglas de los identificadores, excepto en que el nombre no puede empezar con un signo numérico (#). Si no se especifica constraint_name, se asigna a la restricción un nombre generado por el sistema. El nombre de la restricción aparece en todos los mensajes de error relativos a las infracciones de la restricción.
Cuando se infringe una restricción en una instrucción INSERT, UPDATE o DELETE, la instrucción se finaliza. Sin embargo, si la instrucción es parte de una transacción explícita, continúa el procesamiento de la transacción. La instrucción ROLLBACK TRANSACTION se puede utilizar con la definición de transacción al comprobar la función @@ERROR del sistema.
Si una tabla tiene restricciones FOREIGN KEY o CHECK y desencadenadores, las condiciones de restricción se evalúan antes de que se ejecute el desencadenador.
Para obtener un informe de una tabla y sus columnas, utilice sp_help o sp_helpconstraint. Para cambiar el nombre de una tabla, utilice sp_rename. Para obtener un informe de las vistas y procedimientos almacenados que dependen de una tabla, utilice sp_depends.
Normalmente, el espacio se asigna a las tablas e índices en incrementos de una extensión a la vez. Cuando se crea la tabla o índice, se le asignan páginas de extensiones mixtas hasta que tiene suficientes páginas para llenar una extensión uniforme. Una vez que haya suficientes páginas para llenar una extensión uniforme, se asigna otra extensión cada vez que se llenan las extensiones asignadas actualmente. Para obtener un informe acerca de la cantidad de espacio asignado y utilizado por una tabla, ejecute sp_spaceused.
Reglas que aceptan valores NULL en la definición de una tabla
La aceptación de valores NULL en una columna determina si esa columna permite valores nulos (NULL) como datos de la columna. NULL no es lo mismo que cero o en blanco: significa que no se ha realizado ninguna entrada ni que se ha suministrado explícitamente un valor NULL y, normalmente, implica que el valor es desconocido o no es aplicable.
Cuando cree o altere una tabla con las instrucciones CREATE TABLE o ALTER TABLE, los valores de sesión y de la base de datos influirán y, posiblemente, anularán la posibilidad de aceptar NULL para el tipo de datos utilizado en la definición de una columna. Se recomienda que defina siempre explícitamente una columna como NULL o NOT NULL, en el caso de columnas no calculadas, o si utiliza un tipo de datos definido por el usuario, que permita a la columna emplear la posibilidad de aceptar el valor NULL predeterminada del tipo de datos.
Cuando no se especifica explícitamente, la posibilidad de aceptar el valor NULL de una columna sigue estas reglas:
Si la columna se define con un tipo de datos definido por el usuario:
SQL Server utiliza la posibilidad de aceptar el valor NULL especificada cuando se creó el tipo de datos. Utilice sp_help para obtener la posibilidad de aceptar el valor NULL predeterminada del tipo de datos.
Si la columna se ha definido con un tipo de datos suministrado por el sistema:
Si el tipo de datos suministrado por el sistema sólo tiene una opción, ésta tiene precedencia. Los tipos de datos timestamp deben ser NOT NULL.
Si el valor de sp_dbcmptlevel es 65 o inferior, el valor del tipo de datos bit es, de forma predeterminada, NOT NULL si la columna no tiene un NULL o NOT NULL explícito. Para obtener más información, consulte sp_dbcmptlevel.
Si alguno de los valores de la sesión es ON (activado con la instrucción SET), entonces:
Si ANSI_NULL_DFLT_ON es ON, se asigna NULL.
Si ANSI_NULL_DFLT_OFF es ON, se asigna NOT NULL.
Si hubiera configurado algún valor de la base de datos (cambiado con sp_dboption), entonces:
Si ANSI null default es true (verdadero), se asigna NULL.
Si ANSI null default es false (falso), se asigna NOT NULL.
Cuando ninguna de las opciones ANSI_NULL_DFLT está establecida para la sesión y la base de datos tiene los valores predeterminados (ANSI null default es false), se asigna el valor predeterminado NOT NULL de SQL Server.
Si la columna es una columna calculada, SQL Server determinará automáticamente si se aceptan valores NULL. Utilice la función COLUMNPROPERTY (propiedad AllowsNull) para saber si la columna acepta valores NULL.
Nota El controlador ODBC de SQL Server y el proveedor Microsoft OLE DB para SQL Server tienen un valor predeterminado de ANSI_NULL_DFLT_ON establecido en ON. Los usuarios de ODBC y OLE DB pueden configurar esto en los orígenes de datos ODBC, o con los atributos o propiedades de la conexión establecidos por la aplicación.
Permisos
De forma predeterminada, los permisos de CREATE TABLE son los de los miembros de las funciones fijas de base de datos db_owner y db_ddladmin. Los miembros de la función fija de base de datos db_owner y los miembros de la función fija de servidor sysadmin pueden transferir el permiso CREATE TABLE a otros usuarios.
Ejemplos
A. Utilizar restricciones PRIMARY KEY
El ejemplo siguiente muestra la definición de columna de una restricción PRIMARY KEY con un índice agrupado sobre la columna job_id de la tabla jobs (que permite al sistema suministrar el nombre de la restricción) en la base de datos de ejemplo pubs.
job_id smallint
PRIMARY KEY CLUSTERED
Este ejemplo muestra cómo se puede suministrar un nombre para la restricción PRIMARY KEY. Esta restricción se utiliza en la columna emp_id de la tabla employee. Esta columna se basa en un tipo de datos definido por el usuario.
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
B. Utilizar restricciones FOREIGN KEY
Una restricción FOREIGN KEY se utiliza para hacer referencia a otra tabla. Las claves externas pueden ser claves de una única columna o de varias columnas. El ejemplo siguiente muestra una restricción FOREIGN KEY de una única columna sobre la tabla employee que hace referencia a la tabla jobs. Sólo se requiere la cláusula REFERENCES para una restricción FOREIGN KEY de una única columna.
job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)
También puede utilizar la cláusula FOREIGN KEY de forma explícita y volver a formular el atributo de columna. Observe que no es necesario que el nombre de la columna sea el mismo en ambas tablas.
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
Las restricciones de claves de varias columnas se crean como restricciones de tabla. En la base de datos pubs, la tabla sales incluye una restricción PRIMARY KEY multicolumna. Este ejemplo muestra cómo hacer referencia a esta clave desde otra tabla; el nombre explícito de restricción es opcional.
CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)
C. Utilizar restricciones UNIQUE
Las restricciones UNIQUE se utilizan para exigir la unicidad en las columnas de claves no principales. Una columna de restricción PRIMARY KEY incluye automáticamente una restricción de unicidad; sin embargo, una restricción UNIQUE puede aceptar valores NULL. Este ejemplo muestra una columna llamada pseudonym de la tabla authors. Exige la restricción de que los pseudónimos de los autores sean únicos.
pseudonym varchar(30) NULL
Unique nonclustered
El ejemplo siguiente muestra una restricción UNIQUE creada en las columnas stor_name y city de la tabla stores, donde stor_id es actualmente la restricción PRIMARY KEY; no debe haber dos almacenes iguales en la misma ciudad.
CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)
D Utilizar definiciones DEFAULT
Los valores predeterminados suministran un valor (con las instrucciones INSERT y UPDATE) cuando no se especifica ninguno. En la base de datos pubs, se utilizan muchas definiciones DEFAULT para asegurar que se introducen los datos y marcadores de posición adecuados.
En la tabla jobs, una cadena de caracteres predeterminada suministra una descripción (columna job_desc) cuando la descripción actual no se introduce explícitamente.
DEFAULT 'New Position - title not formalized yet'
En la tabla employee, los empleados pueden trabajar para una imprenta o para la compañía primaria. Cuando no se suministra una compañía de forma explícita, se introduce la compañía primaria (observe que, como se muestra aquí, se pueden anidar comentarios en la definición de la tabla).
DEFAULT ('9952')
/* By default the Parent Company Publisher is the company
to whom each employee reports. */
Además de constantes, las definiciones de DEFAULT pueden incluir funciones. Utilice este ejemplo para obtener la fecha actual de una entrada:
DEFAULT (getdate())
Las funciones niládicas pueden mejorar también la integridad de los datos. Para realizar el seguimiento del usuario que insertó una fila, utilice la función niládica para USER (no escriba las funciones niládicas entre paréntesis):
DEFAULT USER
E. Utilizar restricciones CHECK
Este ejemplo muestra las restricciones realizadas a los valores introducidos en las columnas min_lvl y max_lvl de la tabla jobs. Estas dos restricciones no tienen nombre:
CHECK (min_lvl >= 10)
y
CHECK (max_lvl <= 250)
Este ejemplo muestra una restricción con nombre con una restricción de patrón sobre los datos de caracteres introducidos en la columna emp_id de la tabla employee.
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
Este ejemplo especifica que pub_id debe estar en una lista específica o seguir un modelo dado. Esta restricción afecta a la columna pub_id de la tabla publishers.
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]')
F. Definiciones de tablas completas
El ejemplo siguiente muestra definiciones completas de tablas con las definiciones de restricciones de tres tablas (jobs, employee y publishers) creadas en la base de datos pubs.
/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)
/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)
/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)
G. Utilizar el tipo de datos uniqueidentifier en una columna
Este ejemplo crea una tabla con una columna uniqueidentifier. Utiliza una restricción PRIMARY KEY para impedir que los usuarios inserten valores duplicados y utiliza la función NEWID() de la restricción DEFAULT para proporcionar valores para las nuevas filas.
CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier
CONSTRAINT Guid_Default
DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)
H. Utilizar una expresión para una columna calculada
Este ejemplo ilustra el uso de una expresión ((low + high)/2) para calcular la columna calculada myavg.
CREATE TABLE mytable
(
Low int,
high int,
myavg AS (low + high)/2
)
I. Utilizar la función USER_NAME para una columna calculada
Este ejemplo utiliza la función USER_NAME en la columna myuser_name (mi nombre de usuario).
CREATE TABLE mylogintable
(
date_in datetime,
user_id int,
myuser_name AS USER_NAME()
)
J. Utilizar NOT FOR REPLICATION
Este ejemplo muestra cómo utilizar la propiedad IDENTITY en una tabla que está suscrita a una duplicación. La tabla incluye una restricción CHECK para asegurar que los valores SaleID generados en este sistema no aumentan hasta el intervalo asignado al publicador de duplicación.
CREATE TABLE Sales
(SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
CHECK NOT FOR REPLICATION (SaleID <= 199999),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID)
)
Véase también
Alter table
Columnproperty
Create index
Create rule
Create view
Tipos de datos
Drop index
Drop rule
Drop table
sp_addtype
sp_depends
sp_help
sp_helpconstraint
sp_rename
sp_spaceused
Create table
Crea una nueva tabla.
Sintaxis
Create table
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[WITH FILLFACTOR = fillfactor]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
Argumentos
database_name
Nombre de la base de datos en la que se crea la tabla. Database_name debe especificar el nombre de una base de datos existente. Si no se especifica, database_name es, de forma predeterminada, la base de datos actual. El inicio de sesión de la conexión actual debe estar asociado con un Id. De usuario existente en la base de datos especificada por database_name, y ese Id. De usuario debe disponer de privilegios de creación de tablas.
Owner
El nombre del Id. De usuario que es propietario de la nueva tabla. Owner debe ser un Id. De usuario existente en la base de datos especificada por database_name. Owner es, de forma predeterminada, el Id. De usuario asociado con el inicio de sesión de la conexión actual en la base de datos especificada en database_name. Si un miembro de la función fija de servidor sysadmin o un miembro de las funciones fijas de base de datos db_dbowner o db_ddladmin ejecutan la instrucción CREATE TABLE en la base de datos especificada por database_name, owner puede especificar un Id. De usuario distinto del que está asociado con el inicio de sesión de la conexión actual. Si un inicio de sesión asociado a un Id. De usuario que sólo tiene permisos para crear tablas ejecuta la instrucción CREATE TABLE, owner debe especificar el Id. De usuario asociado al inicio de sesión actual. Los miembros de la función fija de servidor sysadmin o los inicios de sesión con el alias de usuario dbo se asocian con el dbo del Id. De usuario; por tanto, de forma predeterminada, las tablas creadas por estos usuarios tienen dbo como propietario. De forma predeterminada, las tablas creadas por los inicios de sesión que no se encuentren en ninguna de estas dos funciones tienen como owner el Id. De usuario asociado con el inicio de sesión.
table_name
Es el nombre de la nueva tabla. Los nombres de tablas se deben ajustar a las reglas para los identificadores. La combinación de owner. Table_name debe ser única en la base de datos. Table_name puede contener un máximo de 128 caracteres, excepto para los nombres de tablas temporales locales (nombres precedidos con un signo numérico simple (#)) que no pueden exceder de 116 caracteres.
column_name
Es el nombre de una columna de la tabla. Los nombres de columna deben cumplir las reglas de los identificadores y deben ser únicos en la tabla. Column_name puede omitirse para las columnas creadas con un tipo de datos timestamp. De forma predeterminada, el nombre de una columna timestamp es timestamp si no se especifica column_name.
computed_column_expression
Es una expresión que define el valor de una columna calculada. Una columna calculada es una columna virtual no almacenada físicamente en la tabla. Se calcula a partir de una expresión mediante otras columnas de la misma tabla. Por ejemplo, una columna calculada puede tener la definición: cost AS price * qty. La expresión puede ser un nombre de columna no calculada, una constante, una función, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta.
Las columnas calculadas se pueden utilizar en las listas de selección, cláusulas WHERE, cláusulas ORDER BY u otros lugares en que se puedan utilizar expresiones regulares, con las siguientes excepciones:
Una columna calculada no puede utilizarse como definición de restricción DEFAULT o FOREIGN KEY ni como NOT NULL. No obstante, es posible utilizar una columna calculada como columna de clave en un índice o como parte de una restricción PRIMARY KEY o UNIQUE, si el valor de la columna calculada está definido mediante una expresión determinista y el tipo de datos del resultado es válido en columnas indizadas.
Por ejemplo, si la tabla tiene las columnas de tipo entero a y b, la columna calculada a+b se puede indizar, pero la columna calculada a+DATEPART(dd, GETDATE()) no, porque el valor puede variar en llamadas posteriores.
Una columna calculada no puede ser el destino de una instrucción INSERT o UPDATE.
Nota Debido a que cada fila de una tabla puede tener distintos valores para las columnas implicadas en una columna calculada, la columna calculada puede no tener el mismo valor para cada fila.
SQL Server determina de forma automática si se aceptan valores NULL en las columnas calculadas, según las expresiones utilizadas. Se considera que el resultado de la mayoría de las expresiones acepta valores NULL incluso si únicamente están presentes las columnas que no aceptan valores NULL, ya que los posibles desbordamientos también darán como resultado valores NULL Utilice la función COLUMNPROPERTY (propiedad AllowsNull) para examinar si las columnas calculadas de una tabla aceptan valores NULL. Una expresión expr que acepta valores NULL puede pasar a no aceptar valores NULL, si se especifica ISNULL(check_expression, constante), donde la constante sea un valor no NULL sustituido por cualquier resultado NULL.
ON {filegroup | DEFAULT}
Especifica el grupo de archivos en el que se almacena la tabla. Si se especifica filegroup, la tabla se almacena en el grupo de archivos mencionado. El grupo de archivos debe existir en la base de datos. Si se especifica DEFAULT o si ON no se especifica en ninguna parte, la tabla se almacena en el grupo de archivos predeterminado.
ON {filegroup | DEFAULT} se puede especificar también en una restricción PRIMARY KEY o UNIQUE. Estas restricciones crean índices. Si se especifica filegroup, el índice se almacena en el grupo de archivos mencionado. Si se especifica DEFAULT, el índice se almacena en el grupo de archivos predeterminado. Si en una restricción no se especifica un grupo de archivos, el índice se almacena en el mismo grupo de archivos que la tabla. Si la restricción PRIMARY KEY o UNIQUE crea un índice agrupado, las páginas de datos de la tabla se almacenan en el mismo grupo de archivos que el índice.
Nota DEFAULT, en el contexto de ON {filegroup | DEFAULT} y TEXTIMAGE_ON {filegroup | DEFAULT}, no es una palabra clave. DEFAULT es un identificador del grupo de archivos predeterminado y se debe delimitar, como en ON "DEFAULT" u ON [DEFAULT] y TEXTIMAGE_ON "DEFAULT" o TEXTIMAGE_ON [DEFAULT].
TEXTIMAGE_ON
Son palabras clave que indican que las columnas text, ntext e image están almacenadas en el grupo de archivos especificado. TEXTIMAGE ON no se admite si no hay columnas text, ntext o image en la tabla. Si no se especifica TEXTIMAGE_ON, las columnas text, ntext e image se almacenan en el mismo grupo de archivos que la tabla.
data_type
Especifica el tipo de datos de la columna. Se permiten los tipos de datos del sistema o definidos por el usuario. Los tipos de datos definidos por el usuario se crean con sp_addtype antes de que puedan utilizarse en una definición de tabla.
La asignación NULL o NOT NULL de un tipo de datos definido por el usuario puede anularse durante la instrucción CREATE TABLE. Sin embargo, la especificación de la longitud no se puede cambiar; en una instrucción CREATE TABLE no se puede especificar la longitud de un tipo de datos definido por el usuario.
DEFAULT
Especifica el valor suministrado para la columna cuando no se ha especificado explícitamente un valor durante la inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna, excepto a las definidas como timestamp o aquellas que tengan la propiedad IDENTITY. Las definiciones DEFAULT se quitan cuando se quita la tabla. Como valor predeterminado sólo se puede utilizar un valor constante (por ejemplo, una cadena de caracteres), una función del sistema (como SYSTEM_USER()), o NULL. Para mantener la compatibilidad con las versiones anteriores de SQL Server, se puede asignar un nombre de restricción a DEFAULT.
constant_expression
Es una constante, NULL o una función del sistema utilizados como el valor predeterminado de una columna.
Identity
Indica que la nueva columna es una columna identidad. Cuando se agrega una nueva fila a la tabla, ¿Microsoft® SQL Server? Proporciona un valor incremental y único a la columna. Las columnas de identidad se utilizan normalmente junto con restricciones PRIMARY KEY para que actúen como identificador exclusivo de fila para la tabla. La propiedad IDENTITY puede asignarse a las columnas tinyint, smallint, int, bigint, decimal(p, 0) o numeric(p, 0). Sólo se puede crear una columna de identidad por tabla. Los valores predeterminados enlazados y las restricciones DEFAULT no se pueden utilizar con una columna identidad. Es necesario especificar la inicialización y el incremento, o no especificar ninguno de los dos. Si no se especifica ninguno, el valor predeterminado es (1,1).
Seed
Es el valor que se utiliza para la primera fila cargada en la tabla.
Increment
Es el valor incremental que se agrega al valor de identidad de la anterior fila cargada.
NOT FOR REPLICATION
Indica que la propiedad IDENTITY no debe exigirse cuando un inicio de sesión duplicado, como sqlrepl, inserta datos en la tabla. Las filas duplicadas deben conservar los valores de clave que se les asignó en la base de datos de publicación; la cláusula NOT FOR REPLICATION asegura que las filas insertadas por un proceso de duplicación no reciban nuevos valores de identidad. Las filas insertadas por otros inicios de sesión continúan recibiendo los nuevos valores de identidad de la forma habitual. Se recomienda que defina también una restricción CHECK con NOT FOR REPLICATION para asegurar que los valores de identidad asignados se encuentran en el intervalo deseado de la base de datos actual.
Rowguidcol
Indica que la nueva columna es una columna de identificador exclusivo global de fila. Únicamente se puede designar una columna uniqueidentifier por cada tabla como la columna ROWGUIDCOL. La propiedad ROWGUIDCOL se puede asignar únicamente a una columna uniqueidentifier. La palabra clave ROWGUIDCOL no es válida si el nivel de compatibilidad de la base de datos es 65 o inferior. Para obtener más información, consulte sp_dbcmptlevel.
La propiedad ROWGUIDCOL no impone la unicidad de los valores almacenados en la columna. Del mismo modo, tampoco genera automáticamente valores para nuevas filas insertadas en la tabla. Para generar valores únicos para cada columna, utilice la función NEWID de las instrucciones INSERT o utilice la función NEWID como el valor predeterminado de la columna.
collation_name
Especifica la intercalación de la columna. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. El argumento collation_name sólo se aplica a columnas de los tipos de datos char, varchar, text, nchar, nvarchar y ntext. Si no se especifica, se asignará a la columna la intercalación del tipo de datos definido por el usuario, si la columna es de un tipo de datos definido por el usuario, o la intercalación predeterminada de la base de datos.
Para obtener más información acerca de los nombres de intercalación de Windows y SQL, consulte COLLATE
constraint
Es una palabra clave opcional que indica el principio de la definición de una restricción PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY o CHECK. Las restricciones son propiedades especiales que exigen la integridad de los datos y pueden crear índices para la tabla y sus columnas.
constraint_name
Es el nombre de una restricción. Los nombres de restricción deben ser únicos en una base de datos.
NULL | NOT NULL
Son palabras clave que determinan si se permiten o no valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar de la misma forma que NOT NULL.
Primary key
Es una restricción que exige la integridad de entidad para una o varias columnas dadas a través de un índice único. Sólo se puede crear una restricción PRIMARY KEY por cada tabla.
Unique
Es una restricción que proporciona la integridad de entidad para una o varias columnas dadas a través de un índice único. Una tabla puede tener varias restricciones UNIQUE.
CLUSTERED | NONCLUSTERED
Son palabras clave que indican que se ha creado un índice agrupado o no agrupado para la restricción PRIMARY KEY o UNIQUE. De forma predeterminada, el valor de las restricciones PRIMARY KEY es CLUSTERED, y el de las restricciones UNIQUE es NONCLUSTERED.
Sólo se puede especificar CLUSTERED para una única restricción de una instrucción CREATE TABLE. Si especifica CLUSTERED para una restricción UNIQUE y especifica también una restricción PRIMARY KEY, el valor predeterminado de PRIMARY KEY es NONCLUSTERED.
[WITH FILLFACTOR = fillfactor]
Especifica cuánto se debe llenar cada página de índice de SQL Server utilizada para almacenar los datos de índice. Los valores de fillfactor especificados por el usuario pueden estar entre 1 y 100; el valor predeterminado es 0. Un factor de relleno inferior crea el índice con más espacio disponible para las nuevas entradas de índice sin tener que asignar nuevo espacio.
foreign key... references
Es una restricción que proporciona integridad referencial para los datos de la columna o columnas. Las restricciones FOREIGN KEY requieren que cada valor de la columna exista en la columna de referencia correspondiente de la tabla a la que se hace referencia. Las restricciones FOREIGN KEY pueden hacer referencia sólo a columnas que sean restricciones PRIMARY KEY o UNIQUE en la tabla de referencia o a columnas a las que se haga referencia en UNIQUE INDEX en la tabla de referencia.
ref_table
Es el nombre de la tabla a la que hace referencia la restricción FOREIGN KEY.
(ref_column[,...n])
Es una columna o lista de columnas de la tabla a la que hace referencia la restricción FOREIGN KEY.
ON DELETE {CASCADE | NO ACTION}
Especifica qué acción tiene lugar en una fila de la tabla creada, si esa fila tiene una relación referencial y la fila a la que hace referencia se elimina en la tabla primaria. El valor predeterminado es NO ACTION.
Si se especifica CASCADE y se elimina una fila de la tabla primaria, también se elimina la fila de la tabla desde donde se hace referencia. Si se especifica NO ACTION, SQL Server genera un error y se deshace la acción de eliminación de la fila en la tabla primaria.
Por ejemplo, en la base de datos Northwind, la tabla Orders tiene una relación de integridad referencial con la tabla Customers. La clave externa Orders. CustomerID hace referencia a la clave principal Customers. CustomerID.
Si se ejecuta una instrucción DELETE en una fila de la tabla Customers y se especifica la acción ON DELETE CASCADE para Orders. CustomerID, SQL Server comprueba las filas dependientes de la tabla Orders. Si las hay, las filas dependientes de la tabla Orders se eliminan, así como la fila a la que se hace referencia en la tabla Customers.
Por otra parte, si se especifica NO ACTION, SQL Server genera un error y deshace la acción de eliminación de la fila Customers si al menos hay una fila en la tabla Orders que haga referencia a la fila Customers.
ON UPDATE {CASCADE | NO ACTION}
Especifica qué acción tiene lugar en una fila de la tabla creada, si esa fila tiene una relación referencial y la fila a la que hace referencia se actualiza en la tabla primaria. El valor predeterminado es NO ACTION.
Si se especifica CASCADE, la fila se actualiza en la tabla de referencia si esa fila se actualiza en la tabla primaria. Si se especifica NO ACTION, SQL Server genera un error y se deshace la acción de actualización en la fila de la tabla primaria.
Por ejemplo, en la base de datos Northwind, la tabla Orders tiene una relación referencial con la tabla Customers: la clave externa Orders. CustomerID hace referencia a la clave principal Customers. CustomerID.
Si se ejecuta una instrucción UPDATE en una fila de la tabla Customers y se especifica la acción ON UPDATE CASCADE para Orders. CustomerID, SQL Server comprueba las filas dependientes de la tabla Orders. Si las hay, las filas dependientes de la tabla Orders se actualizan, así como la fila a la que se hace referencia en la tabla Customers.
Por el contrario, si se especifica NO ACTION, SQL Server emite un error y deshace la acción de actualización de la fila Customers si al menos una fila de la tabla Orders hace referencia a ella.
CHECK
Es una restricción que exige la integridad del dominio al limitar los valores posibles que se pueden escribir en una o varias columnas.
NOT FOR REPLICATION
Palabras clave que se utilizan para impedir que se exija la restricción CHECK durante el proceso de distribución utilizado por la duplicación. Cuando las tablas son suscriptores de una publicación de duplicación, no actualice directamente la tabla de suscripción; en su lugar, actualice la tabla de publicación y deje que la duplicación vuelva a distribuir los datos en la tabla de suscripción. En la tabla de suscripción se puede definir una restricción CHECK para impedir que los usuarios la modifiquen. Sin embargo, a menos que se agregue la cláusula NOT FOR REPLICATION, la restricción CHECK impide también que el proceso de duplicación distribuya las modificaciones de la tabla de publicación a la tabla de suscripción. La cláusula NOT FOR REPLICATION significa que la restricción se exige en las modificaciones de los usuarios, pero no en el proceso de duplicación.
La restricción NOT FOR REPLICATION CHECK se aplica tanto a la imagen anterior como posterior de un registro actualizado para impedir que se agreguen o eliminen registros del intervalo duplicado. Se comprueban todos los borrados e inserciones; si éstos se encuentran en el intervalo duplicado, se rechazan.
Cuando esta restricción se utiliza con una columna de identidad, SQL Server permite que la tabla no tenga que reinicializar los valores de columna de identidad cuando un usuario de duplicación la actualiza.
logical_expression
Es una expresión lógica que devuelve TRUE o FALSE.
Column
Es una columna o lista de columnas, entre paréntesis, que se utiliza en las restricciones de tabla para indicar las columnas que se están utilizando en la definición de la restricción.
[ASC | DESC]
Especifica cómo se ordenan la columna o las columnas que participan en las restricciones de la tabla. El valor predeterminado es ASC.
n
Es un marcador de posición que indica que el elemento anterior se puede repetir n veces.
Observaciones
SQL Server puede tener hasta dos mil millones de tablas por cada base de datos y 1.024 columnas por tabla. El número de filas y el tamaño total de la tabla está limitado solamente por el espacio de almacenamiento disponible. El número máximo de bytes por fila es de 8.060. Si se crean tablas con columnas varchar, nvarchar o varbinary en las que el ancho total definido excede de 8.060 bytes, se crea la tabla, pero aparece un mensaje de advertencia. Al intentar insertar más de 8.060 bytes en tal fila o actualizar una fila de tal forma que su tamaño total de fila exceda de 8.060, se genera un mensaje de error y no se puede realizar la instrucción.
Las instrucciones CREATE TABLE que incluyen una columna sql_variant pueden generar la siguiente advertencia:
The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Esta advertencia se produce porque sql_variant puede tener una longitud máxima de 8016 bytes. Cuando una columna sql_variant contiene valores cercanos a la longitud máxima, puede superar el límite del tamaño máximo de la fila.
Cada tabla puede contener un máximo de 249 índices no agrupados y 1 índice agrupado. Éstos incluyen los índices generados para admitir las restricciones PRIMARY KEY y UNIQUE definidas para la tabla.
SQL Server no impone un orden en que DEFAULT, IDENTITY, ROWGUIDCOL o las restricciones de columna se especifican en una definición de columna.
Tablas temporales
Se pueden crear tablas temporales locales y globales. Las tablas temporales locales son visibles sólo en la sesión actual; las tablas temporales globales son visibles para todas las sesiones.
Coloque un prefijo de signo numérico simple (#table_name) en los nombres de las tablas temporales locales y un prefijo de un signo numérico doble (##table_name) en los nombres de las tablas temporales globales.
Las instrucciones SQL hacen referencia a la tabla temporal mediante el valor especificado por table_name en la instrucción CREATE TABLE:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
Si se crea una tabla temporal local en un procedimiento almacenado o una aplicación que varios usuarios pueden ejecutar al mismo tiempo, SQL Server tiene que ser capaz de distinguir las tablas creadas por los distintos usuarios. SQL Server hace esto al anexar internamente un sufijo numérico a cada nombre de tabla temporal local. El nombre completo de una tabla temporal tal como se almacena en la tabla sysobjects de tempdb consta del nombre de la tabla especificado en la instrucción CREATE TABLE y el sufijo numérico generado por el sistema. Para permitir que se agregue el sufijo, el table_name especificado de un nombre temporal local no puede exceder de 116 caracteres.
Las tablas temporales se quitan automáticamente cuando están fuera de ámbito, a menos que ya se hayan quitado explícitamente mediante DROP TABLE:
Una tabla temporal local creada en un procedimiento almacenado se quita automáticamente cuando se completa el procedimiento almacenado. Cualquiera de los procedimientos almacenados anidados ejecutados por el procedimiento almacenado que creó la tabla puede hacer referencia a la tabla. El proceso que llamó al procedimiento almacenado que creó la tabla no puede hacer referencia a la tabla.
Todas las demás tablas temporales se quitan automáticamente al final de la sesión actual.
Las tablas temporales globales se quitan automáticamente cuando la sesión que creó la tabla finaliza y todas las tareas restantes han dejado de hacer referencia a ellas. La asociación entre una tarea y una tabla se mantiene sólo durante la vida de una única instrucción Transact-SQL. Esto significa que la tabla temporal global se quita al finalizar la última instrucción Transact-SQL que estuviera haciendo referencia activamente a la tabla cuando finalizó la sesión que la creó.
Una tabla temporal local creada en un procedimiento almacenado o desencadenador es distinta de una tabla temporal que tenga el mismo nombre y que se haya creado antes de que se llame al procedimiento almacenado o al desencadenador. Si una consulta hace referencia a una tabla temporal y hay dos tablas temporales con el mismo nombre, no está definido en cual de las dos tablas debe resolverse la consulta. Los procedimientos almacenados anidados pueden crear también tablas temporales con el mismo nombre que la tabla temporal creada por el procedimiento almacenado que la llamó. Todas las referencias al nombre de la tabla que se efectúan en el procedimiento almacenado anidado se resuelven en la tabla creada en el procedimiento almacenado, por ejemplo:
CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO
El siguiente es el conjunto de resultados:
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
Cuando se crean tablas temporales globales o locales, la sintaxis CREATE TABLE admite la definición de restricciones, excepto las restricciones FOREIGN KEY. Si en una tabla temporal se especifica una restricción FOREIGN KEY, la instrucción devuelve un mensaje de advertencia que indica que la restricción se saltó y que la tabla se ha creado sin las restricciones FOREIGN KEY. En las restricciones FOREIGN KEY no se puede hacer referencia a tablas temporales.
Considere la posibilidad de utilizar variables de tabla en lugar de tablas temporales. Las tablas temporales son útiles cuando es necesario crear en ellas índices de forma explícita, o bien cuando los valores de tabla deben ser visibles en varios procedimientos almacenados o funciones. En general, las variables de tabla contribuyen a que el procesamiento de las consultas sea más eficaz. Para obtener más información, consulte table.
Restricciones PRIMARY KEY
Una tabla puede contener una sola restricción PRIMARY KEY.
El índice generado por una restricción PRIMARY KEY no puede hacer que el número de índices de la tabla exceda de 249 índices no agrupados y 1 índice agrupado.
Si no se especifica CLUSTERED o NONCLUSTERED para una restricción PRIMARY KEY, se utiliza CLUSTERED si no hay índices agrupados especificados para las restricciones UNIQUE.
Todas las columnas definidas en una restricción PRIMARY KEY deben establecerse como NOT NULL. Si no se especifica la posibilidad de aceptar NULL, todas las columnas que participan en una restricción PRIMARY KEY tienen su posibilidad de aceptar NULL establecida en NOT NULL.
Restricciones UNIQUE
Si no se especifica CLUSTERED o NONCLUSTERED para una restricción UNIQUE, de forma predeterminada se utiliza NONCLUSTERED.
Cada restricción UNIQUE genera un índice. El número de restricciones UNIQUE no puede hacer que el número de índices de la tabla exceda de 249 índices no agrupados y 1 índice agrupado.
Restricciones FOREIGN KEY
Cuando en la columna de una restricción FOREIGN KEY se introduce un valor distinto de NULL, el valor debe existir en la columna a la que se hace referencia; de lo contrario, se devuelve un mensaje de error de infracción de clave externa.
Las restricciones FOREIGN KEY se aplican a la columna anterior a menos que se especifiquen columnas de origen.
Las restricciones FOREIGN KEY sólo pueden hacer referencia a las tablas de la misma base de datos en el mismo servidor. La integridad referencial de las bases de datos cruzadas debe implementarse a través de desencadenadores. Para obtener más información, consulte CREATE TRIGGER.
Las restricciones FOREIGN KEY pueden hacer referencia a otras columnas de la misma tabla (una auto-referencia).
La cláusula REFERENCES de una restricción FOREIGN KEY en el nivel de columna puede enumerar sólo una columna de referencia, que debe tener el mismo tipo de datos que la columna en la que se define la restricción.
La cláusula REFERENCES de una restricción FOREIGN KEY en el nivel de tabla debe tener el mismo número de columnas de referencia que el número de columnas de la lista de columnas de restricción. El tipo de datos de cada columna de referencia debe ser también el mismo que la columna correspondiente de la lista de columnas.
Es posible que no se pueda especificar CASCADE si una columna del tipo timestamp forma parte de la clave externa o de la clave a la que se hace referencia.
Se puede combinar CASCADE y NO ACTION en tablas que tengan relaciones referenciales entre sí. Si SQL Server encuentra NO ACTION, termina y deshace las acciones CASCADE relacionadas. Cuando una instrucción DELETE hace que se combinen acciones CASCADE y NO ACTION, todas las acciones CASCADE se aplican antes de que SQL Server compruebe si hay cláusulas NO ACTION.
Una tabla puede contener un máximo de 253 restricciones FOREIGN KEY.
Las restricciones FOREIGN KEY no se exigen en tablas temporales.
Una tabla puede hacer referencia a un máximo de 253 tablas distintas en sus restricciones FOREIGN KEY.
Las restricciones FOREIGN KEY sólo pueden hacer referencia a las columnas de las restricciones PRIMARY KEY o UNIQUE de la tabla de referencia o a las columnas en UNIQUE INDEX de la tabla de referencia.
Definiciones DEFAULT
Una columna sólo puede tener una definición DEFAULT.
Una definición DEFAULT puede contener valores constantes, funciones, funciones niládicas SQL-92 o NULL. La tabla muestra las funciones niládicas y los valores que devuelven para el valor predeterminado durante la ejecución de una instrucción INSERT. Función niládica SQL-92 Valor devuelto
CURRENT_TIMESTAMP Fecha y hora actuales.
CURRENT_USER Nombre del usuario que realiza la inserción.
SESSION_USER Nombre del usuario que realiza la inserción.
SYSTEM_USER Nombre del usuario que realiza la inserción.
USER Nombre del usuario que realiza la inserción.
En una definición DEFAULT, constant_expression no puede hacer referencia a otra columna de la tabla o a otras tablas, vistas o procedimientos almacenados.
Las definiciones DEFAULT no se pueden crear sobre columnas con un tipo de datos timestamp o columnas con una propiedad IDENTITY.
Las definiciones DEFAULT no se pueden crear para columnas con tipos de datos definidos por el usuario, si éstos están enlazados a un objeto predeterminado.
Restricciones CHECK
Una columna puede tener cualquier número de restricciones CHECK y la condición puede incluir varias expresiones lógicas combinadas con AND y OR. Varias restricciones CHECK para una columna se validan en el orden en que se crean.
La condición de búsqueda debe dar como resultado una expresión booleana y no puede hacer referencia a otra tabla.
Una restricción CHECK en el nivel de columna sólo puede hacer referencia a la columna restringida, y una restricción CHECK en el nivel de tabla sólo puede hacer referencia a columnas de la misma tabla.
Las restricciones CHECK y las reglas sirven para la misma función de validación de los datos durante las instrucciones INSERT y DELETE.
Cuando hay una regla y una o más restricciones CHECK para una columna o columnas, se evalúan todas las restricciones.
Información adicional de restricción
Un índice creado para una restricción no se puede quitar con la instrucción DROP INDEX; la restricción debe quitarse con la instrucción ALTER TABLE. Un índice creado y utilizado por una restricción puede volverse a generar con la instrucción DBCC DBREINDEX.
Los nombres de restricción deben seguir las reglas de los identificadores, excepto en que el nombre no puede empezar con un signo numérico (#). Si no se especifica constraint_name, se asigna a la restricción un nombre generado por el sistema. El nombre de la restricción aparece en todos los mensajes de error relativos a las infracciones de la restricción.
Cuando se infringe una restricción en una instrucción INSERT, UPDATE o DELETE, la instrucción se finaliza. Sin embargo, si la instrucción es parte de una transacción explícita, continúa el procesamiento de la transacción. La instrucción ROLLBACK TRANSACTION se puede utilizar con la definición de transacción al comprobar la función @@ERROR del sistema.
Si una tabla tiene restricciones FOREIGN KEY o CHECK y desencadenadores, las condiciones de restricción se evalúan antes de que se ejecute el desencadenador.
Para obtener un informe de una tabla y sus columnas, utilice sp_help o sp_helpconstraint. Para cambiar el nombre de una tabla, utilice sp_rename. Para obtener un informe de las vistas y procedimientos almacenados que dependen de una tabla, utilice sp_depends.
Normalmente, el espacio se asigna a las tablas e índices en incrementos de una extensión a la vez. Cuando se crea la tabla o índice, se le asignan páginas de extensiones mixtas hasta que tiene suficientes páginas para llenar una extensión uniforme. Una vez que haya suficientes páginas para llenar una extensión uniforme, se asigna otra extensión cada vez que se llenan las extensiones asignadas actualmente. Para obtener un informe acerca de la cantidad de espacio asignado y utilizado por una tabla, ejecute sp_spaceused.
Reglas que aceptan valores NULL en la definición de una tabla
La aceptación de valores NULL en una columna determina si esa columna permite valores nulos (NULL) como datos de la columna. NULL no es lo mismo que cero o en blanco: significa que no se ha realizado ninguna entrada ni que se ha suministrado explícitamente un valor NULL y, normalmente, implica que el valor es desconocido o no es aplicable.
Cuando cree o altere una tabla con las instrucciones CREATE TABLE o ALTER TABLE, los valores de sesión y de la base de datos influirán y, posiblemente, anularán la posibilidad de aceptar NULL para el tipo de datos utilizado en la definición de una columna. Se recomienda que defina siempre explícitamente una columna como NULL o NOT NULL, en el caso de columnas no calculadas, o si utiliza un tipo de datos definido por el usuario, que permita a la columna emplear la posibilidad de aceptar el valor NULL predeterminada del tipo de datos.
Cuando no se especifica explícitamente, la posibilidad de aceptar el valor NULL de una columna sigue estas reglas:
Si la columna se define con un tipo de datos definido por el usuario:
SQL Server utiliza la posibilidad de aceptar el valor NULL especificada cuando se creó el tipo de datos. Utilice sp_help para obtener la posibilidad de aceptar el valor NULL predeterminada del tipo de datos.
Si la columna se ha definido con un tipo de datos suministrado por el sistema:
Si el tipo de datos suministrado por el sistema sólo tiene una opción, ésta tiene precedencia. Los tipos de datos timestamp deben ser NOT NULL.
Si el valor de sp_dbcmptlevel es 65 o inferior, el valor del tipo de datos bit es, de forma predeterminada, NOT NULL si la columna no tiene un NULL o NOT NULL explícito. Para obtener más información, consulte sp_dbcmptlevel.
Si alguno de los valores de la sesión es ON (activado con la instrucción SET), entonces:
Si ANSI_NULL_DFLT_ON es ON, se asigna NULL.
Si ANSI_NULL_DFLT_OFF es ON, se asigna NOT NULL.
Si hubiera configurado algún valor de la base de datos (cambiado con sp_dboption), entonces:
Si ANSI null default es true (verdadero), se asigna NULL.
Si ANSI null default es false (falso), se asigna NOT NULL.
Cuando ninguna de las opciones ANSI_NULL_DFLT está establecida para la sesión y la base de datos tiene los valores predeterminados (ANSI null default es false), se asigna el valor predeterminado NOT NULL de SQL Server.
Si la columna es una columna calculada, SQL Server determinará automáticamente si se aceptan valores NULL. Utilice la función COLUMNPROPERTY (propiedad AllowsNull) para saber si la columna acepta valores NULL.
Nota El controlador ODBC de SQL Server y el proveedor Microsoft OLE DB para SQL Server tienen un valor predeterminado de ANSI_NULL_DFLT_ON establecido en ON. Los usuarios de ODBC y OLE DB pueden configurar esto en los orígenes de datos ODBC, o con los atributos o propiedades de la conexión establecidos por la aplicación.
Permisos
De forma predeterminada, los permisos de CREATE TABLE son los de los miembros de las funciones fijas de base de datos db_owner y db_ddladmin. Los miembros de la función fija de base de datos db_owner y los miembros de la función fija de servidor sysadmin pueden transferir el permiso CREATE TABLE a otros usuarios.
Ejemplos
A. Utilizar restricciones PRIMARY KEY
El ejemplo siguiente muestra la definición de columna de una restricción PRIMARY KEY con un índice agrupado sobre la columna job_id de la tabla jobs (que permite al sistema suministrar el nombre de la restricción) en la base de datos de ejemplo pubs.
job_id smallint
PRIMARY KEY CLUSTERED
Este ejemplo muestra cómo se puede suministrar un nombre para la restricción PRIMARY KEY. Esta restricción se utiliza en la columna emp_id de la tabla employee. Esta columna se basa en un tipo de datos definido por el usuario.
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
B. Utilizar restricciones FOREIGN KEY
Una restricción FOREIGN KEY se utiliza para hacer referencia a otra tabla. Las claves externas pueden ser claves de una única columna o de varias columnas. El ejemplo siguiente muestra una restricción FOREIGN KEY de una única columna sobre la tabla employee que hace referencia a la tabla jobs. Sólo se requiere la cláusula REFERENCES para una restricción FOREIGN KEY de una única columna.
job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)
También puede utilizar la cláusula FOREIGN KEY de forma explícita y volver a formular el atributo de columna. Observe que no es necesario que el nombre de la columna sea el mismo en ambas tablas.
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
Las restricciones de claves de varias columnas se crean como restricciones de tabla. En la base de datos pubs, la tabla sales incluye una restricción PRIMARY KEY multicolumna. Este ejemplo muestra cómo hacer referencia a esta clave desde otra tabla; el nombre explícito de restricción es opcional.
CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)
C. Utilizar restricciones UNIQUE
Las restricciones UNIQUE se utilizan para exigir la unicidad en las columnas de claves no principales. Una columna de restricción PRIMARY KEY incluye automáticamente una restricción de unicidad; sin embargo, una restricción UNIQUE puede aceptar valores NULL. Este ejemplo muestra una columna llamada pseudonym de la tabla authors. Exige la restricción de que los pseudónimos de los autores sean únicos.
pseudonym varchar(30) NULL
Unique nonclustered
El ejemplo siguiente muestra una restricción UNIQUE creada en las columnas stor_name y city de la tabla stores, donde stor_id es actualmente la restricción PRIMARY KEY; no debe haber dos almacenes iguales en la misma ciudad.
CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)
D Utilizar definiciones DEFAULT
Los valores predeterminados suministran un valor (con las instrucciones INSERT y UPDATE) cuando no se especifica ninguno. En la base de datos pubs, se utilizan muchas definiciones DEFAULT para asegurar que se introducen los datos y marcadores de posición adecuados.
En la tabla jobs, una cadena de caracteres predeterminada suministra una descripción (columna job_desc) cuando la descripción actual no se introduce explícitamente.
DEFAULT 'New Position - title not formalized yet'
En la tabla employee, los empleados pueden trabajar para una imprenta o para la compañía primaria. Cuando no se suministra una compañía de forma explícita, se introduce la compañía primaria (observe que, como se muestra aquí, se pueden anidar comentarios en la definición de la tabla).
DEFAULT ('9952')
/* By default the Parent Company Publisher is the company
to whom each employee reports. */
Además de constantes, las definiciones de DEFAULT pueden incluir funciones. Utilice este ejemplo para obtener la fecha actual de una entrada:
DEFAULT (getdate())
Las funciones niládicas pueden mejorar también la integridad de los datos. Para realizar el seguimiento del usuario que insertó una fila, utilice la función niládica para USER (no escriba las funciones niládicas entre paréntesis):
DEFAULT USER
E. Utilizar restricciones CHECK
Este ejemplo muestra las restricciones realizadas a los valores introducidos en las columnas min_lvl y max_lvl de la tabla jobs. Estas dos restricciones no tienen nombre:
CHECK (min_lvl >= 10)
y
CHECK (max_lvl <= 250)
Este ejemplo muestra una restricción con nombre con una restricción de patrón sobre los datos de caracteres introducidos en la columna emp_id de la tabla employee.
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
Este ejemplo especifica que pub_id debe estar en una lista específica o seguir un modelo dado. Esta restricción afecta a la columna pub_id de la tabla publishers.
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]')
F. Definiciones de tablas completas
El ejemplo siguiente muestra definiciones completas de tablas con las definiciones de restricciones de tres tablas (jobs, employee y publishers) creadas en la base de datos pubs.
/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)
/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)
/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)
G. Utilizar el tipo de datos uniqueidentifier en una columna
Este ejemplo crea una tabla con una columna uniqueidentifier. Utiliza una restricción PRIMARY KEY para impedir que los usuarios inserten valores duplicados y utiliza la función NEWID() de la restricción DEFAULT para proporcionar valores para las nuevas filas.
CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier
CONSTRAINT Guid_Default
DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)
H. Utilizar una expresión para una columna calculada
Este ejemplo ilustra el uso de una expresión ((low + high)/2) para calcular la columna calculada myavg.
CREATE TABLE mytable
(
Low int,
high int,
myavg AS (low + high)/2
)
I. Utilizar la función USER_NAME para una columna calculada
Este ejemplo utiliza la función USER_NAME en la columna myuser_name (mi nombre de usuario).
CREATE TABLE mylogintable
(
date_in datetime,
user_id int,
myuser_name AS USER_NAME()
)
J. Utilizar NOT FOR REPLICATION
Este ejemplo muestra cómo utilizar la propiedad IDENTITY en una tabla que está suscrita a una duplicación. La tabla incluye una restricción CHECK para asegurar que los valores SaleID generados en este sistema no aumentan hasta el intervalo asignado al publicador de duplicación.
CREATE TABLE Sales
(SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
CHECK NOT FOR REPLICATION (SaleID <= 199999),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID)
)
Véase también
Alter table
Columnproperty
Create index
Create rule
Create view
Tipos de datos
Drop index
Drop rule
Drop table
sp_addtype
sp_depends
sp_help
sp_helpconstraint
sp_rename
sp_spaceused
