CREATE TABLE (Transact-SQL)
Crea una nueva tabla.
.gif "Icono de vínculo a temas")
Sintaxis
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition>
| <column_set_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name | filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ SPARSE ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
]
<column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
(column [ ASC | DESC ] [ ,...n ] )
[
WITH FILLFACTOR = fillfactor
|WITH ( <index_option> [ , ...n ] )
]
[ ON { partition_scheme_name (partition_column_name)
| filegroup | "default" } ]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<table_option> ::=
{
DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
| DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>
Argumentos
database_name
Es el nombre de la base de datos en que se crea la tabla. database_name debe especificar el nombre de una base de datos existente. Si no se especifica, database_name utiliza de manera predeterminada la base de datos actual. El inicio de sesión de la conexión actual debe estar asociado a un identificador de usuario existente en la base de datos especificada por database_name, y ese identificador de usuario debe tener permisos CREATE TABLE.schema_name
Es el nombre del esquema al que pertenece la nueva tabla.table_name
Es el nombre de la nueva tabla. Los nombres de tablas deben seguir las reglas de los identificadores. table_name puede contener un máximo de 128 caracteres excepto para los nombres de tablas temporales locales, los nombres precedidos por un único signo de número (#), que no pueden superar los 116 caracteres.column_name
Es el nombre de una columna de la tabla. Los nombres de columna deben seguir las reglas de los identificadores y deben ser únicos en la tabla. column_name puede contener hasta 128 caracteres. column_name se puede omitir para las columnas creadas con un tipo de datos timestamp. Si no se especifica column_name, el nombre de una columna timestamp es, de manera predeterminada, timestamp.computed_column_expression
Es una expresión que define el valor de una columna calculada. Una columna calculada es una columna virtual que no está almacenada físicamente en la tabla, a menos que la columna esté marcada con PERSISTED. La columna se calcula a partir de una expresión que utiliza 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 ni contener tipos de datos de alias.Las columnas calculadas se pueden utilizar en listas de selección, cláusulas WHERE, cláusulas ORDER BY u otras ubicaciones 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 definición de restricción 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 se permite en columnas de índice.
Por ejemplo, si la tabla contiene las columnas de enteros a y b, la columna calculada a+b puede estar indizada, pero la columna calculada a+DATEPART(dd, GETDATE()) no puede indizarse porque el valor puede cambiar en las siguientes llamadas.
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.
En función de las expresiones utilizadas, la nulabilidad de las columnas calculadas la determina automáticamente el Database Engine (Motor de base de datos). Se considera que el resultado de la mayoría de las expresiones admite valores NULL incluso si únicamente están presentes columnas que no admiten valores NULL, ya que los posibles subdesbordamientos o desbordamientos también dan como resultado valores NULL. Utilice la función COLUMNPROPERTY con la propiedad AllowsNull para determinar la nulabilidad de las columnas calculadas de la tabla. Una expresión que admita valores NULL se puede convertir en una expresión que no los admita si se especifica ISNULL con la constante check_expression, donde la constante es un valor distinto de NULL que se sustituye por cualquier resultado NULL. Se requiere el permiso REFERENCES sobre el tipo para las columnas calculadas basadas en expresiones de tipo definido por el usuario de Common Language Runtime (CLR).
PERSISTED
Especifica que SQL Server Database Engine (Motor de base de datos de SQL Server) almacena físicamente los valores calculados en la tabla y actualiza los valores cuando se actualizan las columnas de las que depende la columna calculada. El hecho de marcar la columna calculada como PERSISTED le permite crear un índice en una columna calculada que es determinista, pero no precisa. Para obtener más información, vea Crear índices en columnas calculadas. Todas las columnas calculadas que se utilizan como columnas de partición de una tabla con particiones se deben marcar explícitamente como PERSISTED. computed_column_expression debe ser determinista cuando se especifica PERSISTED.ON { <partition_scheme> | filegroup | "default" }
Especifica el esquema de partición o el grupo de archivos en que se almacena la tabla. Si se especifica <partition_scheme>, la tabla será una tabla con particiones cuyas particiones se almacenan en un conjunto de uno o más grupos de archivos especificados en <partition_scheme>. Si se especifica filegroup, la tabla se almacena en el grupo de archivos con nombre. 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. El mecanismo de almacenamiento de una tabla según se especifica en CREATE TABLE no se puede modificar posteriormente.ON {<partition_scheme> | 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 con nombre. Si se especifica "default" o si ON no se especifica en ninguna parte, el índice se almacena en el mismo grupo de archivos que la tabla. Si la restricción PRIMARY KEY o UNIQUE crea un índice clúster, las páginas de datos de la tabla se almacenan en el mismo grupo de archivos que el índice. Si se especifica CLUSTERED o la restricción crea un índice clúster, y se especifica un <partition_scheme> distinto del <partition_scheme> o filegroup de la definición de tabla o viceversa, únicamente se respeta la definición de restricción y se omite el resto.
[!NOTA]
En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y se debe delimitar, como en ON "default" u ON [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe ser ON para la sesión actual. Ésta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).
[!NOTA]
Después de crear una tabla con particiones, considere la posibilidad de establecer la opción LOCK_ESCALATION para la tabla en AUTO. Esto puede mejorar la simultaneidad al permitir que los bloqueos se extiendan al nivel de partición (HOBT) en lugar de la tabla. Para obtener más información, vea ALTER TABLE (Transact-SQL).
TEXTIMAGE_ON { filegroup| "default" }
Son palabras clave que indican que las columnas text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) y de tipo definido por el usuario CLR se almacenan en el grupo de archivos especificado.No se permite TEXTIMAGE_ON si no hay columnas de valores grandes en la tabla. No se puede especificar TEXTIMAGE_ON si se especifica <partition_scheme>. Si se especifica "default" o si TEXTIMAGE_ON no se especifica en ninguna parte, las columnas de valores grandes se almacenan en el grupo de archivos predeterminado. El almacenamiento de los datos de columna de valores grandes especificados en CREATE TABLE no se puede modificar posteriormente.
[!NOTA]
En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y se debe delimitar, como en TEXTIMAGE_ON "default" o TEXTIMAGE_ON [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe ser ON para la sesión actual. Ésta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).
FILESTREAM_ON { partition_scheme_name | filegroup | "default" }
Especifica el grupo de archivos para los datos FILESTREAM.Si la tabla contiene datos FILESTREAM y se crean particiones de la tabla, debe incluirse la cláusula FILESTREAM_ON y debe especificarse un esquema de partición de grupos de archivos FILESTREAM. Este esquema de partición debe utilizar la misma función de partición y las mismas columnas de partición que el esquema de partición para la tabla; de lo contrario, se produce un error.
Si la tabla no tiene particiones, no se pueden crear particiones en la columna FILESTREAM. Los datos FILESTREAM para la tabla deben almacenarse en un grupo de archivos único. Este grupo de archivos se especifica en la cláusula FILESTREAM_ON.
Si la tabla no tiene particiones y no se especifica la cláusula FILESTREAM_ON, se utiliza el grupo de archivos FILESTREAM que tenga la propiedad DEFAULT establecida. Si no hay ningún grupo de archivos FILESTREAM, se produce un error.
Al igual que con ON y TEXTIMAGE_ON, el valor establecido utilizando CREATE TABLE para FILESTREAM_ON no se puede cambiar, excepto en los casos siguientes:
Una instrucción CREATE INDEX convierte un montón en un índice clúster. En este caso, se puede especificar un grupo de archivos FILESTREAM diferente, un esquema de partición o NULL.
Una instrucción DROP INDEX convierte un índice clúster en un montón. En este caso, se puede especificar un grupo de archivos FILESTREAM diferente, un esquema de partición o "default".
El grupo de archivos en la cláusula FILESTREAM_ON <grupoDeArchivos>, o cada grupo de archivos FILESTREAM que se menciona en el esquema de partición, debe tener un archivo definido para el grupo de archivos. Este archivo se debe definir utilizando una instrucción CREATE DATABASE o ALTER DATABASE; de lo contrario, se produce un error.
Para consultar temas relacionados con FILESTREAM, vea Diseñar e implementar almacenamiento FILESTREAM.
[ type_schema_name**.** ] type_name
Especifica el tipo de datos de la columna y el esquema al que pertenece. El tipo de datos puede ser uno de los siguientes:Un tipo de datos del sistema.
Un tipo de alias basado en un tipo de datos del sistema de SQL Server. Los tipos de datos de alias se crean con la instrucción CREATE TYPE para poder utilizarlos en una definición de tabla. La asignación NULL o NOT NULL de un tipo de datos de alias puede invalidarse durante la instrucción CREATE TABLE. No obstante, la especificación de longitud no se puede cambiar; la longitud del tipo de datos de alias no se puede especificar en una instrucción CREATE TABLE.
Un tipo definido por el usuario CLR. Los tipos definidos por el usuario CLR se crean con la instrucción CREATE TYPE para poder utilizarlos en una definición de tabla. Para crear una columna en un tipo definido por el usuario CLR, se necesita el permiso REFERENCES para el tipo.
Si no se especifica el parámetro type_schema_name, SQL Server Database Engine (Motor de base de datos de SQL Server) hace referencia a type_name en el siguiente orden:
El tipo de datos del sistema de SQL Server.
El esquema predeterminado del usuario actual en la base de datos actual.
El esquema dbo en la base de datos actual.
precision
Es la precisión del tipo de datos especificado. Para obtener más información acerca de los valores de precisión válidos, vea Precisión, escala y longitud (Transact-SQL).scale
Es la escala del tipo de datos especificado. Para obtener más información acerca de los valores de escala válidos, vea Precisión, escala y longitud (Transact-SQL).max
Sólo se aplica a los tipos de datos varchar, nvarchar y varbinary para almacenar 2^31 bytes de datos de caracteres y binarios, y 2^30 bytes de datos Unicode.CONTENT
Especifica que cada instancia del tipo de datos xml de column_name puede contener varios elementos de nivel superior. CONTENT sólo se aplica al tipo de datos xml y únicamente se puede especificar si también se especifica xml_schema_collection. Si no se especifica, CONTENT es el comportamiento predeterminado.DOCUMENT
Especifica que cada instancia del tipo de datos xml de column_name sólo puede contener un elemento de nivel superior. DOCUMENT sólo se aplica al tipo de datos xml y únicamente se puede especificar si también se especifica xml_schema_collection.xml_schema_collection
Sólo se aplica al tipo de datos xml para asociar una colección de esquemas XML al tipo. Antes de escribir una columna xml en un esquema, el esquema se debe crear primero en la base de datos con CREATE XML SCHEMA COLLECTION.DEFAULT
Especifica el valor suministrado para la columna cuando no se ha proporcionado explícitamente un valor durante la inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna, excepto a las definidas como timestamp o a las que tengan la propiedad IDENTITY. Si se especifica un valor predeterminado para una columna de un tipo definido por el usuario, dicho tipo debe admitir la conversión implícita de constant_expression en el tipo definido por el usuario. Las definiciones DEFAULT desaparecen cuando la tabla se quita. Como valor predeterminado sólo se puede utilizar un valor constante, por ejemplo una cadena de caracteres, una función escalar (función del sistema, definida por el usuario o CLR) o NULL. Para mantener la compatibilidad con 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 que se utiliza como valor predeterminado de una columna.IDENTITY
Indica que la nueva columna es una columna de identidad. Cuando se agrega una fila nueva a la tabla, Database Engine (Motor de base de datos) proporciona un valor incremental único para la columna. Las columnas de identidad se utilizan normalmente con las restricciones PRIMARY KEY como identificadores de fila únicos de la tabla. La propiedad IDENTITY se puede asignar a columnas tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Sólo se puede crear una columna de identidad para cada tabla. Las restricciones DEFAULT y los valores predeterminados enlazados no se pueden utilizar en las columnas de identidad. En este caso, deben especificarse el valor de inicialización y el incremento, o ninguno de esto valores. 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 fila cargada anterior.NOT FOR REPLICATION
En la instrucción CREATE TABLE, la cláusula NOT FOR REPLICATION se puede especificar para la propiedad IDENTITY, las restricciones FOREIGN KEY y las restricciones CHECK. Si se especifica esta cláusula para la propiedad IDENTITY, los valores no se incrementan en las columnas de identidad cuando los agentes de replicación realizan inserciones. Si se especifica esta cláusula para una restricción, dicha restricción no se exige cuando los agentes de replicación realizan operaciones de inserción, actualización o eliminación. Para obtener más información, vea Controlar restricciones, identidades y desencadenadores con NOT FOR REPLICATION.ROWGUIDCOL
Indica que la nueva columna es una columna de GUID de filas. Sólo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL. Si se aplica la propiedad ROWGUIDCOL, se puede hacer referencia a la columna mediante $ROWGUID. 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, vea sp_dbcmptlevel (Transact-SQL). Las columnas de tipos de datos definidos por el usuario no se pueden designar con ROWGUIDCOL.La propiedad ROWGUIDCOL no impone la unicidad de los valores almacenados en la columna. ROWGUIDCOL tampoco genera automáticamente valores para nuevas filas insertadas en la tabla. Para generar valores únicos para cada columna, utilice la función NEWID o NEWSEQUENTIALID en instrucciones INSERT o utilice estas funciones como valor predeterminado de la columna.
SPARSE
Indica que la columna es una columna dispersa. El almacenamiento de columnas dispersas está optimizado para los valores NULL. Las columnas dispersas no se pueden designar como NOT NULL. Para obtener restricciones adicionales y más información sobre columnas dispersas, vea Usar columnas dispersas.FILESTREAM
Es válido sólo para columnas varbinary(max). Especifica el almacenamiento FILESTREAM para los datos de BLOB varbinary(max).La tabla también debe tener una columna del tipo de datos uniqueidentifier que tenga el atributo ROWGUIDCOL. Esta columna no debe permitir valores nulos y debe tener una restricción de columna única UNIQUE o PRIMARY KEY. El valor de GUID para la columna debe ser suministrado por una aplicación cuando se insertan los datos o por una restricción DEFAULT que utiliza la función NEWID ().
No se puede quitar la columna ROWGUIDCOL ni se pueden cambiar las restricciones relacionadas si hay definida una columna FILESTREAM para la tabla. Solamente se puede quitar la columna ROWGUIDCOL cuando se quite la última columna FILESTREAM.
Si se especifica el atributo de almacenamiento FILESTREAM para una columna, todos los valores para dicha columna se almacenan en un contenedor de datos FILESTREAM en el sistema de archivos.
COLLATE 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. collation_name sólo es aplicable a las 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 uno de estos tipos, 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, vea Nombre de intercalación de Windows y Nombre de intercalación de SQL.
Para obtener más información acerca de la cláusula COLLATE, vea COLLATE (Transact-SQL).
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. Para obtener más información, vea Restricciones.constraint_name
Es el nombre de una restricción. Los nombres de restricción deben ser únicos en el esquema al que pertenece la tabla.NULL | NOT NULL
Determina si se permiten valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar de la misma forma que NOT NULL. NOT NULL se puede especificar para las columnas calculadas sólo si se especifica también PERSISTED.PRIMARY KEY
Es una restricción que exige la integridad de entidad para una o varias columnas especificadas a través de un índice único. Sólo se puede crear una restricción PRIMARY KEY para cada tabla.UNIQUE
Es una restricción que proporciona integridad de entidad para una o varias columnas especificadas a través de un índice único. Las tablas pueden tener múltiples restricciones UNIQUE.CLUSTERED | NONCLUSTERED
Indica que se ha creado un índice clúster o no clúster 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.En una instrucción CREATE TABLE, se puede especificar CLUSTERED tan sólo para una restricción. Si se especifica CLUSTERED para una restricción UNIQUE y se especifica también una restricción PRIMARY KEY, el valor predeterminado de PRIMARY KEY es NONCLUSTERED.
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 o columnas de referencia correspondientes 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. Las claves externas de las columnas calculadas también se deben marcar como PERSISTED.[ schema_name**.**] referenced_table_name]
Es el nombre de la tabla a la que hace referencia la restricción FOREIGN KEY y el esquema al que pertenece.**(**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 { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Especifica la acción que tiene lugar en las filas de la tabla creada si dichas filas tienen una relación referencial y la fila a la que se hace referencia se elimina de la tabla primaria. El valor predeterminado es NO ACTION.NO ACTION
El Database Engine (Motor de base de datos) genera un error y se revierte la acción de eliminación de la fila de la tabla primaria.CASCADE
Si esa fila se elimina de la tabla primaria, las filas correspondientes se eliminan de la tabla de referencia.SET NULL
Todos los valores que forman la clave externa se establecen en NULL si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, las columnas de clave externa deben admitir valores NULL.SET DEFAULT
Todos los valores que forman la clave externa se establecen en los valores predeterminados si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, todas las columnas de clave externa deben tener valores predeterminados. Si una columna acepta valores NULL y no se ha establecido un valor predeterminado explícito, NULL se convierte en el valor predeterminado explícito de dicha columna.
No especifique CASCADE si la tabla se va a incluir en una publicación de mezcla que utiliza registros lógicos. Para obtener más información acerca de los registros lógicos, vea Agrupar cambios en filas relacionadas con registros lógicos.
No se puede definir ON DELETE CASCADE si ya existe un desencadenador INSTEAD OF en ON DELETE en la tabla en cuestión.
Por ejemplo, en la base de datos AdventureWorks, la tabla ProductVendor tiene una relación referencial con la tabla Vendor. La clave externa ProductVendor.VendorID hace referencia a la clave principal Vendor.VendorID.
Si se ejecuta una instrucción DELETE en una fila de la tabla Vendor y se especifica la acción ON DELETE CASCADE para ProductVendor.VendorID, el Database Engine (Motor de base de datos) comprueba las filas dependientes de la tabla ProductVendor. Si las hay, las filas dependientes de la tabla ProductVendor se eliminan, así como la fila a la que se hace referencia en la tabla Vendor.
Por el contrario, si se especifica NO ACTION, Database Engine (Motor de base de datos) genera un error y revierte la acción de eliminación de la fila Vendor si hay al menos una fila en la tabla ProductVendor que hace referencia a ella.
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Especifica la acción que se produce en las filas de la tabla modificada cuando esas filas tienen una relación referencial y la fila a la que se hace referencia se actualiza en la tabla primaria. El valor predeterminado es NO ACTION.NO ACTION
El Database Engine (Motor de base de datos) genera un error y se revierte la acción de actualización de la fila de la tabla primaria.CASCADE
Si esa fila se actualiza en la tabla primaria, las filas correspondientes se actualizan en la tabla de referencia.SET NULL
Cuando se actualiza la fila correspondiente en la tabla primaria, todos los valores que componen la clave externa se establecen en NULL. Para que esta restricción se ejecute, las columnas de clave externa deben aceptar valores NULL.SET DEFAULT
Cuando se actualiza la fila correspondiente en la tabla primaria, todos los valores que componen la clave externa se establecen en sus valores predeterminados. Para que esta restricción se ejecute, todas las columnas de clave externa deben tener definiciones predeterminadas. Si una columna acepta valores NULL y no se ha establecido un valor predeterminado explícito, NULL se convierte en el valor predeterminado explícito de dicha columna.
No especifique CASCADE si la tabla se va a incluir en una publicación de mezcla que utiliza registros lógicos. Para obtener más información acerca de los registros lógicos, vea Agrupar cambios en filas relacionadas con registros lógicos.
No se puede definir la acción ON UPDATE CASCADE si ya existe un desencadenador INSTEAD OF en ON UPDATE en la tabla que se está modificando.
Por ejemplo, en la base de datos AdventureWorks, la tabla ProductVendor tiene una relación referencial con la tabla Vendor: la clave externa ProductVendor.VendorID hace referencia a la clave principal Vendor.VendorID.
Si se ejecuta una instrucción UPDATE en una fila de la tabla Vendor y se especifica la acción ON UPDATE CASCADE para ProductVendor.VendorID, Database Engine (Motor de base de datos) comprueba si hay una o varias filas dependientes en la tabla ProductVendor. Si las hay, las filas dependientes de la tabla ProductVendor se actualizan, así como la fila a la que se hace referencia en la tabla Vendor.
Por el contrario, si se especifica NO ACTION, Database Engine (Motor de base de datos) genera un error y revierte la acción de actualización de la fila Vendor si hay como mínimo una fila en la tabla ProductVendor que 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. Las restricciones CHECK de las columnas calculadas también se deben marcar como PERSISTED.logical_expression
Es una expresión lógica que devuelve TRUE o FALSE. Los tipos de datos de alias no pueden formar parte de la expresión.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.partition_scheme_name
Es el nombre del esquema de partición que define los grupos de archivos a los que se van a asignar las particiones de una tabla con particiones. El esquema de partición debe existir en la base de datos.[ partition_column_name**.** ]
Especifica la columna en la que se van a crear las particiones de la tabla con particiones. La columna debe coincidir con la que se especifica en la función de partición que partition_scheme_name utiliza en términos de tipo de datos, longitud y precisión. Una columna calculada que participa en una función de partición se debe marcar como PERSISTED de forma explícita..gif "Nota importante")
ImportanteSe recomienda especificar NOT NULL en la columna de partición de las tablas con particiones y también de las tablas sin particiones que sean orígenes o destinos de operaciones ALTER TABLE...SWITCH. De esta forma, se garantiza que cualquier restricción CHECK en columnas de partición no tenga que comprobar si hay valores NULL. Para obtener más información, vea Transferir datos de forma eficaz con cambios de particiones.
WITH FILLFACTOR **=**fillfactor
Especifica en qué medida el Database Engine (Motor de base de datos) debe llenar cada página de índice que se va a utilizar para almacenar los datos de índice. Los valores de fillfactor especificados por el usuario pueden estar comprendidos entre 1 y 100. Si no se especifica un valor, el valor predeterminado es 0. Los valores del factor de relleno 0 y 100 son idénticos.ImportanteLa documentación de WITH FILLFACTOR = fillfactor como única opción de índice que se aplica a restricciones PRIMARY KEY o UNIQUE se mantiene por compatibilidad con versiones anteriores. No obstante, no se documentará de esta forma en futuras versiones.
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
Es el nombre del conjunto de columnas. Un conjunto de columnas es una representación XML sin tipo que combina todas las columnas dispersas de una tabla en una salida estructurada. Para obtener más información sobre conjuntos de columnas, vea Utilizar conjuntos de columnas.< table_option> ::=
Especifica una o varias opciones de tabla.DATA_COMPRESSION
Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados. Las opciones son las siguientes:NONE
No se comprimen la tabla ni las particiones especificadas.ROW
La tabla o las particiones especificadas se comprimen utilizando la compresión de fila.PAGE
La tabla o las particiones especificadas se comprimen utilizando la compresión de página.
Para obtener más información acerca de la compresión, vea Crear tablas e índices comprimidos.
ON PARTITIONS ( { <expresión_de_número_de_particiones> | <intervalo> } [ ,...n ] )
Especifica las particiones a las que se aplica el valor DATA_COMPRESSION. Si la tabla no tiene particiones, el argumento ON PARTITIONS generará un error. Si no se proporciona la cláusula ON PARTITIONS, la opción DATA_COMPRESSION se aplicará a todas las particiones de una tabla con particiones.<partition_number_expression> se puede especificar de las maneras siguientes:
Proporcione el número de una partición, por ejemplo: ON PARTITIONS (2).
Proporcione los números de partición de varias particiones separados por comas, por ejemplo: ON PARTITIONS (1, 5).
Proporcione intervalos y particiones individuales, por ejemplo: ON PARTITIONS (2, 4, 6 TO 8).
<range> se puede especificar como números de partición separados por la palabra TO, por ejemplo: ON PARTITIONS (6 TO 8).
Para establecer tipos de compresión de datos diferentes para particiones distintas, especifique la opción DATA_COMPRESSION más de una vez, por ejemplo:
WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )<index_option> ::=
Especifica una o varias opciones de índice. Para obtener una descripción completa de estas opciones, vea CREATE INDEX (Transact-SQL).PAD_INDEX = { ON | OFF }
Cuando es ON, el porcentaje de espacio disponible especificado por FILLFACTOR se aplica a las páginas de nivel intermedio del índice. Cuando es OFF o no se especifica ningún valor de FILLFACTOR, las páginas de nivel intermedio se rellenan casi al máximo de su capacidad dejando espacio suficiente para al menos una fila del tamaño máximo que el índice puede contener teniendo en cuenta el conjunto de claves de las páginas intermedias. El valor predeterminado es OFF.FILLFACTOR **=**fillfactor
Especifica un porcentaje que indica cuánto debe llenar Database Engine (Motor de base de datos) el nivel hoja de cada página de índice cuando se crea o se altera un índice. fillfactor debe ser un valor entero entre 1 y 100. El valor predeterminado es 0. Los valores de factor de relleno (fillfactor) 0 y 100 son idénticos.IGNORE_DUP_KEY = { ON | OFF }
Especifica la respuesta de error cuando una operación de inserción intenta insertar valores de clave duplicados en un índice único. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice. La opción no tiene efecto cuando se ejecutan CREATE INDEX, ALTER INDEX o UPDATE. El valor predeterminado es OFF.ON
Se producirá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Sólo las filas que infrinjan la restricción de unicidad darán error.OFF
Se producirá un mensaje de error cuando se inserten valores de clave duplicados en un índice único. Toda la operación INSERT se revertirá.
IGNORE_DUP_KEY no se puede establecer en ON para los índices creados en una vista, los índices que no sean únicos, los índices XML, los índices espaciales y los índices filtrados.
Para ver IGNORE_DUP_KEY, utilice sys.indexes.
En la sintaxis compatible con versiones anteriores, WITH IGNORE_DUP_KEY es equivalente a WITH IGNORE_DUP_KEY = ON.
STATISTICS_NORECOMPUTE = { ON | OFF }
Si es ON, las estadísticas de índices no actualizadas no se vuelven a calcular automáticamente. Si es OFF, se habilita la actualización automática de estadísticas. El valor predeterminado es OFF.ALLOW_ROW_LOCKS = { ON | OFF }
Si es ON, los bloqueos de fila se permiten al tener acceso al índice. Database Engine (Motor de base de datos) determina cuándo se utilizan los bloqueos de fila. Si es OFF, no se utilizan bloqueos de fila. El valor predeterminado es ON.ALLOW_PAGE_LOCKS = { ON | OFF }
Si es ON, los bloqueos de página se permiten al tener acceso al índice. El Database Engine (Motor de base de datos) determina cuándo se utilizan los bloqueos de página. Si es OFF, no se utilizan bloqueos de página. El valor predeterminado es ON.
Notas
SQL Server 2008 puede tener hasta dos mil millones de tablas por base de datos. Una tabla que tiene definido un conjunto de columnas puede tener hasta 30.000 columnas con un máximo de 1024 no dispersas más las columnas calculadas. Las tablas que no tienen conjuntos de columnas se limitan a 1024 columnas. El número de filas y el tamaño total de la tabla están limitados solamente por el espacio de almacenamiento disponible. El número máximo de bytes por fila es de 8.060. Esta restricción se amplía en el caso de las tablas con columnas varchar, nvarchar, varbinary o sql_variant, las cuales hacen que el ancho total definido para la tabla sea superior a 8.060 bytes. La longitud de cada una de estas columnas debe ajustarse al límite de 8.000 bytes, aunque la combinación de sus anchos puede ser superior al límite de 8.060 bytes en una tabla. Para obtener más información, vea Datos de desbordamiento de fila superiores a 8 KB.
Cada tabla puede contener un máximo de 999 índices no clúster y 1 índice clúster. En estos índices se incluyen los índices generados para admitir las restricciones PRIMARY KEY y UNIQUE definidas para la tabla.
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 el índice, se le asignan páginas de extensiones mixtas hasta que tengan páginas suficientes 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.
El Database Engine (Motor de base de datos) no exige el orden en que DEFAULT, IDENTITY, ROWGUIDCOL o las restricciones de columna se especifican en una definición de columna.
Al crear una tabla, la opción QUOTED IDENTIFIER siempre se almacena como ON en los metadatos de la tabla incluso si la opción está establecida en OFF al crear la tabla.
Tablas temporales
Se pueden crear tablas temporales locales y globales. Las tablas temporales locales son visibles sólo en la sesión actual y las tablas temporales globales son visibles para todas las sesiones. No se pueden crear particiones en las tablas temporales.
Coloque un prefijo de signo de número único (#table_name) en los nombres de las tablas temporales locales y un prefijo de signo de número doble (##table_name) en los nombres de las tablas temporales globales.
Las instrucciones SQL hacen referencia a la tabla temporal mediante el valor especificado para table_name en la instrucción CREATE TABLE. Por ejemplo:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
Si se ha creado más de una tabla temporal en un único procedimiento almacenado o lote, deben tener nombres distintos.
Si se crea una tabla temporal local en un procedimiento almacenado o una aplicación que varios usuarios pueden ejecutar al mismo tiempo, el Database Engine (Motor de base de datos) tiene que ser capaz de distinguir las tablas creadas por los distintos usuarios. El Database Engine (Motor de base de datos) realiza esto anexando 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 parámetro table_name especificado para un nombre temporal local no puede superar los 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 el uso de 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.
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 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 activa a la tabla cuando finalizó la sesión que la creó.
Una tabla temporal local creada en un procedimiento almacenado o un desencadenador puede tener el mismo nombre que una tabla temporal creada antes de que se llame al procedimiento almacenado o al desencadenador. No obstante, si una consulta hace referencia a una tabla temporal y hay dos tablas temporales con el mismo nombre, no está definido en cuál 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ó. Sin embargo, en el caso de las modificaciones que se van a resolver en la tabla creada en el procedimiento anidado, la tabla debe tener la misma estructura, con los mismos nombres de columna, que la tabla creada en el procedimiento que realiza la llamada. Esto se muestra en el ejemplo siguiente.
CREATE PROCEDURE dbo.Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (2);
SELECT Test2Col = x FROM #t;
GO
CREATE PROCEDURE dbo.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
Éste 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 se especifica una restricción FOREIGN KEY en una tabla temporal, la instrucción devuelve un mensaje de advertencia que indica que la restricción se ha omitido. La tabla se sigue creando sin las restricciones FOREIGN KEY. En las restricciones FOREIGN KEY no se puede hacer referencia a tablas temporales.
Se recomienda 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 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, vea table (Transact-SQL).
Tablas con particiones
Antes de crear una tabla con particiones mediante CREATE TABLE, debe crear una función de partición para especificar cómo se van a crear las particiones en la tabla. Para crear una función de partición se usa CREATE PARTITION FUNCTION. A continuación, debe crear un esquema de partición para especificar los grupos de archivos que van a contener las particiones indicadas mediante la función de partición. Para crear un esquema de partición se usa CREATE PARTITION SCHEME. La colocación de restricciones PRIMARY KEY o UNIQUE para separar grupos de archivos no se puede especificar para las tablas con particiones. Para obtener más información, vea Tablas e índices con particiones.
Restricciones PRIMARY KEY
Una tabla sólo puede incluir una 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 supere los 999 índices no clúster y 1 índice clúster.
Si no se especifica CLUSTERED o NONCLUSTERED para una restricción PRIMARY KEY, se utiliza CLUSTERED si no hay índices clúster especificados para las restricciones UNIQUE.
Todas las columnas definidas en una restricción PRIMARY KEY se deben definir como NOT NULL. Si no se especifica nulabilidad, la nulabilidad de todas las columnas que participan en una restricción PRIMARY KEY se establece en NOT NULL.
Si la clave principal se define en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario. Para obtener más información, vea Tipos definidos por el usuario de CLR.
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 supere los 999 índices no clúster y 1 índice clúster.
Si se define una restricción única en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario o el orden basado en el operador. Para obtener más información, vea Tipos definidos por el usuario de CLR.
Restricciones FOREIGN KEY
Si se especifica un valor distinto de NULL en la columna de una restricción FOREIGN KEY, el valor debe existir en la columna a que se hace referencia; de lo contrario, se devolverá un 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 entre bases de datos debe implementarse a través de desencadenadores. Para obtener más información, vea CREATE TRIGGER (Transact-SQL).
Las restricciones FOREIGN KEY pueden hacer referencia a otras columnas de la misma tabla. Esto recibe el nombre de autorreferencia.
La cláusula REFERENCES de una restricción FOREIGN KEY de nivel de columna sólo puede incluir una columna de referencia. Esta columna 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 de nivel de tabla debe tener el mismo número de columnas de referencia que la lista de columnas de la restricción. El tipo de datos de cada columna de referencia debe ser también el mismo que el de la columna correspondiente de la lista de columnas.
No se puede especificar CASCADE, SET NULL o SET DEFAULT si una columna del tipo timestamp forma parte de la clave externa o de la clave con referencia.
CASCADE, SET NULL, SET DEFAULT y NO ACTION se pueden combinar en las tablas con relaciones referenciales entre sí. Si el Database Engine (Motor de base de datos) detecta NO ACTION, detiene y revierte las acciones CASCADE, SET NULL y SET DEFAULT relacionadas. Cuando una instrucción DELETE hace que se combinen las acciones CASCADE, SET NULL, SET DEFAULT y NO ACTION, todas las acciones CASCADE, SET NULL y SET DEFAULT se aplican antes de que el Database Engine (Motor de base de datos) compruebe la existencia de NO ACTION.
El Database Engine (Motor de base de datos) no tiene un límite predefinido para el número de restricciones FOREIGN KEY que una tabla que hace referencia a otras tablas puede contener, o para el número de restricciones FOREIGN KEY pertenecientes a otras tablas que hacen referencia a una tabla específica.
No obstante, el número real de restricciones FOREIGN KEY que se puede utilizar está limitado por la configuración del hardware y por el diseño de la base de datos y de la aplicación. Se recomienda que la tabla no contenga más de 253 restricciones FOREIGN KEY y que no más de 253 restricciones FOREIGN KEY hagan referencia a ella. El límite real en cada caso puede variar en función de la aplicación y el hardware. Debe tener en cuenta el costo que supone la exigencia de restricciones FOREIGN KEY al diseñar la base de datos y las aplicaciones.
Las restricciones FOREIGN KEY no se exigen en tablas temporales.
Las restricciones FOREIGN KEY sólo pueden hacer referencia a columnas de restricciones PRIMARY KEY o UNIQUE de la tabla a la que se hace referencia o a columnas en UNIQUE INDEX de dicha tabla.
Si la clave externa se define en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario. Para obtener más información, vea Tipos definidos por el usuario de CLR.
Una columna de tipo varchar(max) puede participar en una restricción FOREIGN KEY sólo si la clave principal a la que hace referencia se define también como tipo varchar(max).
Definiciones DEFAULT
Una tabla sólo puede incluir una definición DEFAULT.
Una definición DEFAULT puede contener valores constantes, funciones, funciones niládicas SQL-92 o NULL. En la siguiente tabla se muestran 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 ni a otras tablas, vistas o procedimientos almacenados.
Las definiciones DEFAULT no se pueden crear en columnas con un tipo de datos timestamp o en columnas con la propiedad IDENTITY.
Las definiciones DEFAULT no se pueden crear para columnas con tipos de datos de alias si estos 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 evaluarse como 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 UPDATE.
Cuando hay una regla y una o más restricciones CHECK para una o varias columnas, se evalúan todas las restricciones.
No se pueden definir restricciones CHECK en columnas text, ntext o image.
Información adicional sobre las restricciones
Un índice creado para una restricción no se puede quitar usando DROP INDEX; la restricción debe quitarse con ALTER TABLE. Un índice creado y utilizado por una restricción se puede volver a generar con DBCC DBREINDEX.
Los nombres de las restricciones deben seguir las reglas de los identificadores, excepto en que el nombre no puede empezar por un signo de número (#). Si no se proporciona el parámetro 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 infracciones de la restricción.
Cuando se infringe una restricción en una instrucción INSERT, UPDATE o DELETE, la instrucción finaliza. Sin embargo, si SET XACT_ABORT se establece en OFF y la instrucción forma parte de una transacción explícita, continúa el procesamiento de la transacción. Si SET XACT_ABORT se establece en ON, se revierte toda la transacción. La instrucción ROLLBACK TRANSACTION también se puede utilizar con la definición de transacción al comprobar la función **@@**ERROR del sistema.
Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, los bloqueos de nivel de fila, página y tabla se permiten al tener acceso al índice. Database Engine (Motor de base de datos) elige el bloqueo apropiado y puede cambiar de escala el bloqueo desde un bloqueo de fila o página a un bloqueo de tabla. Para obtener más información, vea Concentración de bloqueos (motor de base de datos). Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, sólo se permite un bloqueo de nivel de tabla al tener acceso al índice. Para obtener más información sobre la configuración de la granularidad de bloqueo para un índice, vea Personalizar el bloqueo de un índice.
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 los procedimientos almacenados que dependen de una tabla, utilice sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities.
Reglas de nulabilidad en una definición de tabla
La nulabilidad de una columna determina si esa columna puede permitir un valor nulo (NULL) para sus datos. NULL no es lo mismo que cero o en blanco: NULL significa que no se ha especificado ninguna entrada o que se ha proporcionado un valor NULL explícito, y suele implicar que se desconoce el valor o que no es aplicable.
Cuando cree o modifique una tabla con las instrucciones CREATE TABLE o ALTER TABLE, la configuración de la sesión y de la base de datos influirá en la nulabilidad para el tipo de datos utilizado en la definición de columna y, posiblemente, la invalidará. 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 que la columna utilice la nulabilidad predeterminada del tipo de datos. Las columnas dispersas siempre deben permitir valores NULL.
Si la nulabilidad de la columna no se especifica explícitamente, seguirá las reglas que se muestran en la tabla siguiente.
Tipo de datos de columna |
Regla |
|---|---|
Tipo de datos de alias |
Database Engine (Motor de base de datos) utiliza la nulabilidad especificada al crear el tipo de datos. Para determinar la nulabilidad predeterminada del tipo de datos, utilice sp_help. |
Tipo definido por el usuario CLR |
La nulabilidad se determina de acuerdo con la definición de columna. |
Tipo de datos suministrado por el sistema |
Si el tipo de datos suministrado por el sistema sólo tiene una opción, esta tiene prioridad. Los tipos de datos timestamp deben ser NOT NULL. Si la configuración de sesión se establece en ON con SET:
|
Cuando ninguna de las opciones ANSI_NULL_DFLT está establecida para la sesión y la base de datos tiene la configuración predeterminada (ANSI_NULL_DEFAULT es OFF), se asigna el valor predeterminado NOT NULL.
En el caso de una columna calculada, el Database Engine (Motor de base de datos) determinará automáticamente su nulabilidad. Para determinar la nulabilidad en este tipo de columna, utilice la función COLUMNPROPERTY con la propiedad AllowsNull.
[!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 las propiedades o atributos de la conexión establecidos por la aplicación.
Compresión de datos
En las tablas del sistema no se puede habilitar la compresión. Cuando se crea una tabla, la compresión de datos se establece en NONE, a menos que se especifique otra cosa. Si se especifica una lista de particiones o una partición que están fuera del intervalo, se generará un error. Para obtener más información acerca de la compresión de datos, vea Crear tablas e índices comprimidos.
Para evaluar cómo afecta el cambio del estado de compresión a una tabla, un índice o una partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings.
Permisos
Se necesita el permiso CREATE TABLE en la base de datos y el permiso ALTER en el esquema en que se crea la tabla.
Si las columnas de la instrucción CREATE TABLE se definen como un tipo definido por el usuario CLR, se necesita la propiedad del tipo o el permiso REFERENCES.
Si las columnas de la instrucción CREATE TABLE tienen asociada una colección de esquemas XML, se necesita la propiedad de la colección de esquemas XML o el permiso REFERENCES.
Ejemplos
A. Usar restricciones PRIMARY KEY
En el siguiente ejemplo se muestra la definición de columna para una restricción PRIMARY KEY con un índice clúster en la columna EmployeeID de la tabla Employee (lo que permite al sistema suministrar el nombre de la restricción) en la base de datos de ejemplo AdventureWorks.
EmployeeID int
PRIMARY KEY CLUSTERED
B. Usar restricciones FOREIGN KEY
Una restricción FOREIGN KEY se utiliza para hacer referencia a otra tabla. Las claves externas pueden ser claves de una sola columna o de varias columnas. En el siguiente ejemplo se muestra una restricción FOREIGN KEY de una única columna en la tabla SalesOrderHeader que hace referencia a la tabla SalesPerson. Sólo se requiere la cláusula REFERENCES para una restricción FOREIGN KEY de una única columna.
SalesPersonID int NULL
REFERENCES SalesPerson(SalesPersonID)
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 (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)
Las restricciones de claves de varias columnas se crean como restricciones de tabla. En la base de datos AdventureWorks, la tabla SpecialOfferProduct incluye una restricción PRIMARY KEY de varias columnas. En el siguiente ejemplo se muestra cómo hacer referencia a esta clave desde otra tabla; el nombre explícito de restricción es opcional.
CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)
C. Usar restricciones UNIQUE
Las restricciones UNIQUE se utilizan para exigir la unicidad en las columnas de clave no principal. En el siguiente ejemplo se exige la restricción de que la columna Name de la tabla Product debe ser única.
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
D. Usar definiciones DEFAULT
Los valores predeterminados suministran un valor (con las instrucciones INSERT y UPDATE) cuando no se especifica ninguno. Por ejemplo, la base de datos AdventureWorks puede incluir una tabla de búsqueda con los distintos trabajos que los empleados pueden realizar en la compañía. En la columna que describe cada trabajo, el valor predeterminado de cadena de caracteres puede suministrar una descripción si no se ha escrito una descripción de forma explícita.
DEFAULT 'New Position - title not formalized yet'
Además de constantes, las definiciones de DEFAULT pueden incluir funciones. Utilice el siguiente ejemplo para obtener la fecha actual de una entrada.
DEFAULT (getdate())
Un recorrido de las funciones niládicas puede mejorar también la integridad de los datos. Para realizar un seguimiento del usuario que ha insertado una fila, utilice la función niládica para USER. No escriba las funciones niládicas entre paréntesis.
DEFAULT USER
E. Usar restricciones CHECK
En el siguiente ejemplo se muestra una restricción para los valores escritos en la columna CreditRating de la tabla Vendor. La restricción no tiene nombre.
CHECK (CreditRating >= 1 and CreditRating <= 5)
En este ejemplo se muestra una restricción con nombre con una restricción de patrón en los datos de caracteres escritos en la columna de la tabla.
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]')
En este ejemplo se especifica que los valores se deben incluir en una lista específica o seguir un patrón dado.
CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')
F. Mostrar la definición de tabla completa
En el siguiente ejemplo se muestran las definiciones de tablas completas con todas las definiciones de restricciones para la tabla PurchaseOrderDetail creada en la base de datos AdventureWorks. Tenga en cuenta que, para ejecutar el ejemplo, el esquema de tabla se cambia a dbo.
CREATE TABLE [dbo].[PurchaseOrderDetail]
(
[PurchaseOrderID] [int] NOT NULL
REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL
REFERENCES Production.Product(ProductID),
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[DueDate] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT (getdate()),
[LineTotal] AS (([UnitPrice]*[OrderQty])),
[StockedQty] AS (([ReceivedQty]-[RejectedQty])),
CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber]
PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber])
WITH (IGNORE_DUP_KEY = OFF)
)
ON [PRIMARY];
G. Crear una tabla con una columna xml con tipo de una colección de esquemas XML
En el siguiente ejemplo se crea una tabla con una columna xml con tipo de la colección de esquemas XML HRResumeSchemaCollection. La palabra clave DOCUMENT especifica que cada instancia del tipo de datos xml de column_name sólo puede contener un elemento de nivel superior.
USE AdventureWorks;
GO
CREATE TABLE HumanResources.EmployeeResumes
(LName nvarchar(25), FName nvarchar(25),
Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );
H. Crear una tabla con particiones
En el siguiente ejemplo se crea una función de partición para crear cuatro particiones en una tabla o en un índice. A continuación, se crea un esquema de partición en el que se especifican los grupos de archivos que van a contener cada una de las cuatro particiones. Finalmente, en el ejemplo se crea una tabla que utiliza el esquema de partición. En este ejemplo se supone que los grupos de archivos ya existen en la base de datos.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
En función de los valores de la columna col1 de PartitionTable, las particiones se asignan de los siguientes modos.
Grupo de archivos |
test1fg |
test2fg |
test3fg |
test4fg |
|---|---|---|---|---|
Partición |
1 |
2 |
3 |
4 |
Valores |
col 1 <= 1 |
col1 > 1 AND col1 <= 100 |
col1 > 100 AND col1 <= 1,000 |
col1 > 1000 |
I. Usar el tipo de datos uniqueidentifier en una columna
En el siguiente ejemplo se crea una tabla con una columna uniqueidentifier. En el ejemplo se utiliza una restricción PRIMARY KEY para impedir que los usuarios inserten valores duplicados, y se utiliza la función NEWSEQUENTIALID() de la restricción DEFAULT para proporcionar valores a las nuevas filas. Se aplica la propiedad ROWGUIDCOL a la columna uniqueidentifier de modo que se pueda hacer referencia a la misma mediante la palabra clave $ROWGUID.
CREATE TABLE dbo.Globally_Unique_Data
(guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Employee_Name varchar(60)
CONSTRAINT Guid_PK PRIMARY KEY (guid) );
J. Usar una expresión para una columna calculada
En el siguiente ejemplo se muestra el uso de una expresión ((low + high)/2) para calcular la columna calculada myavg.
CREATE TABLE dbo.mytable
( low int, high int, myavg AS (low + high)/2 ) ;
K. Crear una columna calculada en función de una columna de tipo definido por el usuario
En el siguiente ejemplo se crea una tabla con una columna de tipo definido por el usuario utf8string dando por supuesto que el ensamblado del tipo y el propio tipo ya se han creado en la base de datos actual. La segunda columna se define en función de utf8string y utiliza el método ToString() de type(class)utf8string para calcular el valor de la columna.
CREATE TABLE UDTypeTable
( u utf8string, ustr AS u.ToString() PERSISTED ) ;
L. Usar la función USER_NAME para una columna calculada
En el siguiente ejemplo se utiliza la función USER_NAME() en la columna myuser_name.
CREATE TABLE dbo.mylogintable
( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;
M. Crear una tabla que tenga una columna FILESTREAM
En el ejemplo siguiente se crea una tabla que tiene una columna PhotoFILESTREAM. Si una tabla tiene una o varias columnas FILESTREAM, la tabla debe tener una columna ROWGUIDCOL.
CREATE TABLE dbo.EmployeePhoto
(
EmployeeId int NOT NULL PRIMARY KEY,
,Photo varbinary(max) FILESTREAM NULL
,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID()
)
N. Crear una tabla que utiliza la compresión de fila
En el ejemplo siguiente se crea una tabla que usa la compresión de fila.
CREATE TABLE T1
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);
Para obtener más ejemplos de compresión de datos, vea Crear tablas e índices comprimidos.
O. Crear una tabla que tenga columnas dispersas y un conjunto de columnas
En los ejemplos siguientes se muestra cómo crear una tabla que tenga una columna dispersa y una tabla que tenga dos columnas dispersas y un conjunto de columnas. En los ejemplos se utiliza la sintaxis básica. Para obtener ejemplos más complejos, vea Usar columnas dispersas y Utilizar conjuntos de columnas.
Para crear una tabla que tenga una columna dispersa, ejecute el código siguiente.
CREATE TABLE T1
(c1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL ) ;
Para crear una tabla que tenga dos columnas dispersas y un conjunto de columnas denominado CSet, ejecute el código siguiente.
CREATE TABLE T1
(c1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;
Vea también