• Статья

Инструкция CREATE TABLE (Transact-SQL)

Создает новую таблицу в SQL Server 2008 R2.

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

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 ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 
    [ SPARSE ] 

<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>

Аргументы

  • database_name
    Имя базы данных, в которой создается таблица. В качестве аргумента database_name должно быть указано имя существующей базы данных. Если аргумент database_name не указан, по умолчанию список стоп-слов создается в текущей базе данных. Имя входа для текущего соединения должно быть связано с идентификатором пользователя, существующего в базе данных, указанной аргументом database_name, и этот пользователь должен обладать разрешениями CREATE TABLE.

  • schema_name
    Имя схемы, которой принадлежит новая таблица.

  • table_name
    Имя новой таблицы. Имена таблиц должны соответствовать правилам для идентификаторов. Аргумент table_name может состоять не более чем из 128 символов, за исключением имен локальных временных таблиц (имена с префиксом номера #), длина которых не должна превышать 116 символов.

  • column_name
    Имя столбца в таблице. Имена столбцов должны соответствовать правилам для идентификаторов и быть уникальными в данной таблице. Аргумент column_name может содержать от 1 до 128 символов. При создании столбцов с типом данных timestamp аргумент column_name может быть пропущен. Если аргумент column_name не указан, столбцу типа timestamp по умолчанию присваивается имя timestamp.

  • computed_column_expression
    Выражение, определяющее значение вычисляемого столбца. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. Значение столбца вычисляется на основе выражения, использующего другие столбцы той же таблицы. Например, определение вычисляемого столбца может быть следующим: cost AS price * qty. Выражение может быть именем невычисляемого столбца, константой, функцией, переменной или любой их комбинацией, соединенной одним или несколькими операторами. Выражение не может быть вложенным запросом или содержать псевдонимы типов данных.

    Вычисляемые столбцы могут использоваться в списках выбора, предложениях WHERE, ORDER BY и в любых других местах, в которых могут использоваться обычные выражения, за исключением следующих случаев.

    • Вычисляемый столбец нельзя использовать ни в качестве определения ограничения DEFAULT или FOREIGN KEY, ни вместе с определением ограничения NOT NULL. Однако вычисляемый столбец может использоваться в качестве ключевого столбца индекса или части какого-либо ограничения PRIMARY KEY или UNIQUE, если значение этого вычисляемого столбца определяется детерминистическим выражением и тип данных результата разрешен в столбцах индекса.

      Например, если таблица содержит целочисленные столбцы a и b, вычисляемый столбец a+b может быть включен в индекс, а вычисляемый столбец a+DATEPART(dd, GETDATE()) — не может, так как его значение может изменяться при последующих вызовах.

    • Вычисляемый столбец не может быть целевым столбцом инструкций INSERT или UPDATE.

    ПримечаниеПримечание

    Каждая строка таблицы может содержать различные значения столбцов, задействованных в вычисляемом столбце; таким образом, значение вычисляемого столбца не будет одним и тем же в каждой строке.

    Компонент Database Engine автоматически определяет для вычисляемых столбцов допустимость значений NULL на основе использованных выражений. Считается, что результат большинства выражений допускает значение NULL, даже если используются только столбцы, для которых значение NULL запрещено, так как при возможном переполнении или потере точности может получаться значение NULL. Для выяснения возможности вычисляемого столбца таблицы принимать значение NULL используйте функцию COLUMNPROPERTY со свойством AllowsNull. Добиться того, чтобы выражение не допускало значения NULL, можно, указав ISNULL с константой check_expression, где константа представляет собой ненулевое значение, заменяющее любое значение NULL. Для вычисляемых столбцов, основанных на выражениях, содержащих определяемые пользователем типы среды CLR, требуется разрешение REFERENCES на тип.

  • PERSISTED
    Указывает, что компонент SQL Server Database Engine будет физически хранить вычисляемые значения в таблице и обновлять их при изменении любого столбца, от которого зависит вычисляемый столбец. Указание PERSISTED для вычисляемого столбца позволяет создать индекс по вычисляемому столбцу, который будет детерминистическим, но неточным. Дополнительные сведения см. в разделе Создание индексов вычисляемых столбцов. Любые вычисляемые столбцы, используемые в качестве столбцов секционирования в секционированной таблице, необходимо явно пометить признаком PERSISTED. Если указан признак PERSISTED, аргумент computed_column_expression должно быть детерминистическим.

  • ON { <partition_scheme> | filegroup | "default" }
    Указывает схему секционирования или файловую группу, в которой хранится таблица. Если аргумент <partition_scheme> указан, таблица будет разбита на секции, сохраняемые в одной или нескольких файловых группах, указанных аргументом <partition_scheme>. Если указан аргумент filegroup, таблица сохраняется в файловой группе с таким именем. Это должна быть существующая файловая группа в базе данных. Если указано значение "default", или параметр ON не определен вообще, таблица сохраняется в установленной по умолчанию файловой группе. Механизм хранения таблицы, указанный в инструкции CREATE TABLE, изменить в дальнейшем невозможно.

    Параметр ON {<partition_scheme> | filegroup | "default"} может также указываться в ограничении PRIMARY KEY или UNIQUE. С помощью этих ограничений создаются индексы. Если указан аргумент filegroup, индекс сохраняется в файловой группе с таким именем. Если указано значение "default" или параметр ON не определен вообще, индекс сохраняется в той же файловой группе, что и таблица. Если ограничение PRIMARY KEY или UNIQUE создает кластеризованный индекс, страницы данных таблицы сохраняются в той же файловой группе, что и индекс. Если ограничение создает кластеризованный индекс (с помощью параметра CLUSTERED или другим способом), а указанный аргумент <partition_scheme> отличается от аргументов <partition_scheme> и filegroup из определения таблицы, либо наоборот, принимается во внимание только определение ограничения, а все остальное не учитывается.

    ПримечаниеПримечание

    В этом контексте default не является ключевым словом. Это идентификатор установленной по умолчанию файловой группы, который должен иметь разделители, как в выражениях ON "default" или ON [default]. Если указано значение "default", то параметру QUOTED_IDENTIFIER для текущего сеанса должно быть присвоено значение ON. Это параметр по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).
    ПримечаниеПримечание

    После создания секционированной таблицы рассмотрите возможность присвоить параметру LOCK_ESCALATION для таблицы значения AUTO. При этом можно усовершенствовать параллелизм, разрешив укрупнение блокировок до уровня секции (HoBT) вместо таблицы. Дополнительные сведения см. в разделе ALTER TABLE (Transact-SQL).

  • TEXTIMAGE_ON { filegroup| "default" }
    Ключевые слова, указывающие, что столбцы типов text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), а также определяемых пользователем типов данных CLR (включая geometry и geography) хранятся в определенной файловой группе.

    Параметр TEXTIMAGE_ON недопустим, если в таблице нет столбцов с большими значениями. Нельзя указывать параметр TEXTIMAGE_ON одновременно с параметром <partition_scheme>). Если указано значение "default" или параметр TEXTIMAGE_ON не определен вообще, столбцы с большими значениями сохраняются в установленной по умолчанию файловой группе. Способ хранения любых данных столбцов с большими значениями, определенный инструкцией CREATE TABLE, изменить в дальнейшем невозможно.

    ПримечаниеПримечание

    В этом контексте default не является ключевым словом. Это идентификатор установленной по умолчанию файловой группы, который должен иметь разделители, как в выражениях TEXTIMAGE_ON "default" или TEXTIMAGE_ON [default]. Если указано значение "default", то параметру QUOTED_IDENTIFIER для текущего сеанса должно быть присвоено значение ON. Это параметр по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).

  • FILESTREAM_ON { partition_scheme_name | filegroup | "default" }
    Задает файловую группу для данных FILESTREAM.

    Если таблица содержит данные FILESTREAM и является секционированной, необходимо включить предложение FILESTREAM_ON и указать схему секционирования файловых групп файлового потока. В этой схеме секционирования должны использоваться те же функции и столбцы секционирования, что и в схеме секционирования для таблицы; в противном случае возникает ошибка.

    Если таблица не секционирована, столбец FILESTREAM не может быть секционирован. Данные FILESTREAM для таблицы должны храниться в отдельной файловой группе. Эта файловая группа указывается в предложении FILESTREAM_ON.

    Если таблица не является секционированной и предложение FILESTREAM_ON не указано, используется файловая группа FILESTREAM, для которой задано свойство DEFAULT. При отсутствии файловой группы FILESTREAM возникает ошибка.

    • Как и в случае с предложениями ON и TEXTIMAGE_ON, значение, указанное с помощью инструкции CREATE TABLE для предложения FILESTREAM_ON, не может быть изменено, за исключением следующих ситуаций.

    • Инструкция CREATE INDEX преобразует кучу в кластеризованный индекс. В этом случае можно указать другую файловую группу FILESTREAM, схему секционирования или значение NULL.

    • Инструкция DROP INDEX преобразует кластеризованный индекс в кучу. В этом случае можно указать другую файловую группу FILESTREAM, схему секционирования или значение "default".

    Для файловой группы в предложении FILESTREAM_ON <файловая_группа> либо для каждой файловой группы FILESTREAM, упомянутой в схеме секционирования, должен быть определен файл. Этот файл должен быть определен с помощью инструкции CREATE DATABASE или ALTER DATABASE, иначе возникает ошибка.

    Связанные разделы о FILESTREAM см. в разделе Проектирование и реализация хранилища FILESTREAM.

  • [ type_schema_name**.** ] type_name
    Указывает тип данных столбца и схему, к которой он принадлежит. Тип данных может быть одним из следующих.

    • Системный тип данных.

    • Псевдонимы типа на основе системного типа данных SQL Server. Прежде чем псевдонимы типов данных можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE. Состояние признака NULL или NOT NULL для псевдонима типа данных может быть переопределено с помощью инструкции CREATE TABLE. Однако его длину изменить нельзя; длина типа данных-псевдонима не определяется инструкцией CREATE TABLE.

    • Определяемый пользователем тип данных CLR. Прежде чем определяемые пользователем типы данных CLR можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE. Для создания столбца с определяемым пользователем типом данных CLR требуется разрешение REFERENCES на этот тип.

    Если аргумент type_schema_name не указан, компонент SQL Server Database Engine ссылается на аргумент type_name в следующем порядке.

    • системный тип данных SQL Server;

    • в установленной по умолчанию для текущего пользователя схеме в текущей базе данных;

    • схема dbo текущей базы данных.

  • precision
    Точность указанного типа данных. Дополнительные сведения о допустимых значениях точности см. в разделе Точность, масштаб и длина.

  • scale
    Масштаб указанного типа данных. Дополнительные сведения о допустимых значениях масштаба см. в разделе Точность, масштаб и длина.

  • max
    Применяется только к типам данных varchar, nvarchar и varbinary для хранения 2^31 байт символьных и двоичных данных или 2^30 байт данных в Юникоде.

  • CONTENT
    Указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать несколько элементов верхнего уровня. Аргумент CONTENT применим только к данным типа xml и может быть указан только в том случае, если одновременно указан аргумент xml_schema_collection. Если этот параметр не указан, CONTENT принимается в качестве поведения по умолчанию.

  • DOCUMENT
    Указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать только один элемент верхнего уровня. Аргумент DOCUMENT применим только к данным типа xml и может быть указан только в том случае, если одновременно указан аргумент xml_schema_collection.

  • xml_schema_collection
    Применим только к типу данных xml для коллекции схем XML, связанной с этим типом. Перед помещением столбца xml в схему она должна быть создана в базе данных при помощи инструкции CREATE XML SCHEMA COLLECTION.

  • DEFAULT
    Указывает значение, присваиваемое столбцу в случае отсутствия явно заданного значения при вставке. Определения DEFAULT могут применяться к любым столбцам, кроме имеющих тип timestamp или обладающих свойством IDENTITY. Если для столбца определяемого пользователем типа указано значение по умолчанию, этот тип должен поддерживать неявное преобразование выражения constant_expression в определяемый пользователем тип. Определения DEFAULT удаляются, когда таблица удаляется из памяти. В качестве значения по умолчанию могут использоваться только константы (например, символьные строки), скалярные функции (системные, определяемые пользователем или функции CLR) или значение NULL. Для сохранения совместимости с более ранними версиями сервера SQL Server значению DEFAULT может быть присвоено имя ограничения.

  • constant_expression
    Константа, значение NULL или системная функция, используемая в качестве значения столбца по умолчанию.

  • IDENTITY
    Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки компонент Database Engine формирует для этого столбца уникальное последовательное значение. Столбцы идентификаторов обычно используются с ограничением PRIMARY KEY для поддержания уникальности идентификаторов строк в таблице. Свойство IDENTITY присвоено столбцам типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для каждой таблицы можно создать только один столбец идентификаторов. Ограниченные значения по умолчанию и ограничения DEFAULT не могут использоваться в столбце идентификаторов. Необходимо указать как начальное значение, так и приращение, или же не указывать ничего. Если ничего не указано, применяется значение по умолчанию (1,1).

  • seed
    Значение, используемое для самой первой строки, загружаемой в таблицу.

  • increment
    Значение приращения, добавляемое к значению идентификатора предыдущей загруженной строки.

  • NOT FOR REPLICATION
    В инструкции CREATE TABLE предложение NOT FOR REPLICATION может указываться для свойства IDENTITY, а также ограничений FOREIGN KEY и CHECK. Если это предложение указано для свойства IDENTITY, значения в столбцах идентификаторов не приращиваются, если вставку выполняют агенты репликации. Если ограничение сопровождается этим предложением, оно не выполняется, когда агенты репликации выполняют операции вставки, обновления или удаления. Дополнительные сведения см. в разделе Управление ограничениями, идентификаторами и триггерами с помощью параметра «NOT FOR REPLICATION».

  • ROWGUIDCOL
    Указывает, что новый столбец является столбцом идентификаторов GUID строки. В качестве столбца ROWGUIDCOL можно назначить только один столбец uniqueidentifier в таблице. Применение свойства ROWGUIDCOL позволяет ссылаться на столбец с помощью ключевого слова $ROWGUID. Свойство ROWGUIDCOL может быть присвоено только столбцу типа uniqueidentifier. Ключевое слово ROWGUIDCOL недопустимо, если уровень совместимости базы данных равен 65 или ниже. Дополнительные сведения см. в разделе Уровень совместимости инструкции ALTER DATABASE (Transact-SQL). Ключевым словом ROWGUIDCOL нельзя обозначать столбцы определяемых пользователем типов данных.

    Свойство ROWGUIDCOL не обеспечивает уникальности значений, хранимых в столбце. Кроме того, при указании данного свойства автоматического формирования значений для новых строк, вставляемых в таблицу, не выполняется. Для создания уникальных значений в каждом столбце следует использовать в инструкциях INSERT функции NEWID или NEWSEQUENTIALID либо использовать эти функции по умолчанию для столбца.

  • SPARSE
    Указывает, что столбец является разреженным столбцом. Хранилище разреженных столбцов оптимизируется для значений NULL. Для разреженных столбцов нельзя указать параметр NOT NULL. Дополнительные ограничения и сведения о разреженных столбцах см. в разделе Использование разреженных столбцов.

  • FILESTREAM
    Допустимо только для столбцов типа varbinary(max). Указывает хранилище FILESTREAM для данных BLOB типа varbinary(max).

    Таблица также должна содержать столбец типа uniqueidentifier с атрибутом ROWGUIDCOL. Этот столбец не должен допускать значений NULL и должен иметь относящееся к одному столбцу ограничение UNIQUE или PRIMARY KEY. Значение идентификатора GUID для столбца должно быть предоставлено приложением во время вставки данных или ограничением DEFAULT, в котором используется функция NEWID ().

    Столбец ROWGUIDCOL нельзя удалить, а связанные ограничения нельзя изменить, если в таблице определен столбец FILESTREAM. Столбец ROWGUIDCOL можно удалить только после удаления последнего столбца FILESTREAM.

    Если для столбца указан атрибут хранилища FILESTREAM, то все значения для этого столбца хранятся в контейнере данных FILESTREAM в файловой системе.

  • COLLATE collation_name
    Задает параметры сортировки для столбца. Имя параметров сортировки может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Аргумент collation_name применим только к столбцам типов данных char, varchar, text, nchar, nvarchar и ntext. Если этот аргумент не указан, столбцу назначаются либо параметры сортировки определяемого пользователем типа, если столбец принадлежит к определяемому пользователем типу данных, либо установленные по умолчанию параметры сортировки для базы данных.

    Дополнительные сведения об именах параметров сортировки Windows и SQL см. в разделах Имя параметров сортировки Windows и Имя параметров сортировки SQL.

    Дополнительные сведения о предложении COLLATE см. в разделе COLLATE (Transact-SQL).

  • CONSTRAINT
    Необязательное ключевое слово, указывающее на начало определения ограничения PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY или CHECK. Дополнительные сведения см. в разделе Ограничения.

  • constraint_name
    Имя ограничения. Имена ограничений должны быть уникальными в пределах схемы, к которой принадлежит таблица.

  • NULL | NOT NULL
    Определяет, допустимы ли для столбца значения NULL. Параметр NULL не является ограничением в строгом смысле слова, но может быть указан так же, как и NOT NULL. Ограничение NOT NULL может быть указано для вычисляемых столбцов только в случае, если одновременно указан параметр PERSISTED.

  • PRIMARY KEY
    Ограничение, которое обеспечивает целостность сущностей для указанного столбца или столбцов с помощью уникального индекса. Можно создать только одно ограничение PRIMARY KEY для таблицы.

  • UNIQUE
    Ограничение, которое обеспечивает целостность сущностей для указанного столбца или столбцов с помощью уникального индекса. В таблице может быть несколько ограничений UNIQUE.

  • CLUSTERED | NONCLUSTERED
    Указывает, что для ограничения PRIMARY KEY или UNIQUE создается кластеризованный или некластеризованный индекс. Для ограничений PRIMARY KEY по умолчанию создается кластеризованный индекс (CLUSTERED), а для ограничений UNIQUE — некластеризованный (NONCLUSTERED).

    В инструкции CREATE TABLE параметр CLUSTERED можно задать только для одного ограничения. Если для ограничения UNIQUE указан параметр CLUSTERED, и, кроме того, указано ограничение PRIMARY KEY, то для PRIMARY KEY применяется по умолчанию значение NONCLUSTERED.

  • FOREIGN KEY REFERENCES
    Ограничение, которое обеспечивает ссылочную целостность данных в этом столбце или столбцах. Ограничения FOREIGN KEY требуют, чтобы каждое значение в столбце существовало в соответствующем связанном столбце или столбцах в связанной таблице. Ограничения FOREIGN KEY могут ссылаться только на столбцы, являющиеся ограничениями PRIMARY KEY или UNIQUE в связанной таблице или на столбцы, на которые имеются ссылки в индексе UNIQUE INDEX связанной таблицы. Внешние ключи в вычисляемых столбцах должны быть также помечены как PERSISTED.

  • [ schema_name**.**] referenced_table_name]
    Имя таблицы, на которую ссылается ограничение FOREIGN KEY, и схема, к которой она принадлежит.

  • **(**ref_column [ ,... n ] )
    Столбец или список столбцов из таблицы, на которую ссылается ограничение FOREIGN KEY.

  • ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
    Определяет операцию, которая производится над строками создаваемой таблицы, если эти строки имеют ссылочную связь, а строка, на которую имеются ссылки, удаляется из родительской таблицы. Параметр по умолчанию — NO ACTION.

    • NO ACTION
      Компонент Database Engine формирует ошибку, и выполняется откат операции удаления строки из родительской таблицы.

    • CASCADE
      Если из родительской таблицы удаляется строка, соответствующие ей строки удаляются и из ссылающейся таблицы.

    • SET NULL
      Все значения, составляющие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в NULL. Для выполнения этого ограничения столбцы внешних ключей должны допускать значения NULL.

    • SET DEFAULT
      Все значения, составляющие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для выполнения этого ограничения все столбцы внешних ключей должны иметь определения по умолчанию. Если столбец допускает значение NULL и множество значений по умолчанию не задано явно, NULL становится неявным значением по умолчанию для данного столбца.

    Не следует использовать параметр CASCADE, если таблица будет включена в публикацию слиянием, в которой используются логические записи. Дополнительные сведения о логических записях см. в разделе Изменения группирования связанных строк с логическими записями.

    Параметр ON DELETE CASCADE нельзя указывать, если в таблице уже существует триггер ON DELETE.

    Например, в базе данных База данных AdventureWorks2008R2 таблица ProductVendor имеет ссылочную связь с таблицей Vendor. Внешний ключ ProductVendor.BusinessEntityID ссылается на первичный ключ Vendor.BusinessEntityID.

    Если над строкой в таблице Vendor выполняется инструкция DELETE и для внешнего ключа ProductVendor.BusinessEntityID указано действие ON DELETE CASCADE, то компонент Database Engine проверит наличие одной или нескольких зависимых записей в таблице ProductVendor. Если такие существуют, то кроме строки в таблице Vendor будут удалены также и все зависимые строки из таблицы ProductVendor.

    В противном случае, если задан параметр NO ACTION, компонент Database Engine выдает ошибку и производит откат операции по удалению строки из таблицы Vendor, если в таблице ProductVendor существует хотя бы одна строка, ссылающаяся на нее.

  • ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
    Указывает, какое действие совершается над строками в изменяемой таблице, когда эти строки имеют ссылочную связь и строка родительской таблицы, на которую указывает ссылка, обновляется. Параметр по умолчанию — NO ACTION.

    • NO ACTION
      Компонент Database Engine возвращает ошибку, и обновление строки родительской таблицы откатывается.

    • CASCADE
      Соответствующие строки обновляются в ссылающейся таблице, если эта строка обновляется в родительской таблице.

    • SET NULL
      Всем значениям, составляющим внешний ключ, присваивается значение NULL, когда обновляется соответствующая строка в родительской таблице. Для выполнения этого ограничения столбцы внешних ключей должны допускать значения NULL.

    • SET DEFAULT
      Всем значениям, составляющим внешний ключ, присваивается их значение по умолчанию, когда обновляется соответствующая строка в родительской таблице. Для выполнения этого ограничения все столбцы внешних ключей должны иметь определения по умолчанию. Если столбец допускает значение NULL и множество значений по умолчанию не задано явно, NULL становится неявным значением по умолчанию для данного столбца.

    Не следует использовать параметр CASCADE, если таблица будет включена в публикацию слиянием, в которой используются логические записи. Дополнительные сведения о логических записях см. в разделе Изменения группирования связанных строк с логическими записями.

    Действия ON UPDATE CASCADE, SET NULL или SET DEFAULT не могут быть определены, если в изменяемой таблице уже существует триггер INSTEAD OF для условия ON UPDATE.

    Например, в базе данных База данных AdventureWorks2008R2 таблица ProductVendor имеет ссылочную связь с таблицей Vendor: Внешний ключ ProductVendor.BusinessEntity ссылается на первичный ключ Vendor.BusinessEntityID.

    Если при выполнении инструкции UPDATE для строки в таблице Vendor указано ON UPDATE CASCADE для столбца ProductVendor.BusinessEntityID, компонент Database Engine производит изменение зависимых строк в таблице ProductVendor. Если такие существуют, то кроме строки в таблице Vendor будут обновлены также и все зависимые строки из таблицы ProductVendor.

    В противном случае, если задан параметр NO ACTION, компонент Database Engine выдает ошибку и производит откат операции по обновлению строки из таблицы Vendor, если в таблице ProductVendor существует хотя бы одна строка, ссылающаяся на нее.

  • CHECK
    Ограничение, обеспечивающее целостность домена путем ограничения возможных значений, которые могут быть введены в столбец или столбцы. Ограничения CHECK в вычисляемых столбцах должны быть также помечены как PERSISTED.

  • logical_expression
    Логическое выражение, возвращающее значения TRUE или FALSE. Псевдонимы типа данных частью выражения быть не могут.

  • column
    Столбец или список столбцов (в скобках), используемый в ограничениях таблицы для указания столбцов, используемых в определении ограничения.

  • [ ASC | DESC ]
    Указывает порядок сортировки столбца или столбцов, участвующих в ограничениях таблицы. Значение по умолчанию — ASC.

  • partition_scheme_name
    Имя схемы секционирования, определяющей файловые группы, которым сопоставляются секции секционированной таблицы. Эта схема секционирования должна существовать в базе данных.

  • [ partition_column_name**.** ]
    Указывает столбец, по которому будет секционирована таблица. Столбец должен соответствовать по типу данных, длине и точности столбцу, указанному в функции секционирования, используемой аргументом partition_scheme_name. Вычисляемый столбец, участвующий в функции секционирования, должен быть явно обозначен ключевым словом PERSISTED.

    Важное примечаниеВажно!

    Рекомендуется указывать параметр NOT NULL для столбца секционирования секционированных таблиц, а также для несекционированных таблиц, являющихся источниками или целями для операций ALTER TABLE...SWITCH. В результате любые ограничения CHECK, заданные для столбцов секционирования, не будут проверять значения NULL. Дополнительные сведения см. в разделе Эффективная передача данных с использованием переключения секций.

  • WITH FILLFACTOR **=**fillfactor
    Указывает, насколько плотно компонент Database Engine должен заполнять каждую страницу индекса, используемую для хранения данных индекса. Пользователем могут быть заданы значения аргумента fillfactor в диапазоне от 1 до 100. Если значение не задано, по умолчанию принимается значение 0. Значения коэффициентов заполнения 0 и 100 идентичны.

    Важное примечаниеВажно!

    Описание выражения WITH FILLFACTOR = fillfactor как единственного параметра индекса, применимого к ограничениям PRIMARY KEY или UNIQUE, сохранено для обеспечения обратной совместимости, но в будущих выпусках это выражение документировано не будет.

  • column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
    Имя набора столбцов. Набор столбцов представляет собой нетипизированное XML-представление, в котором все разреженные столбцы таблицы объединены в структурированные выходные данные. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.

  • < table_option> ::=
    Указывает один или более параметров таблицы.

  • DATA_COMPRESSION
    Задает режим сжатия данных для указанной таблицы, номера секции или диапазона секций. Ниже приведены доступные параметры.

    • NONE
      Таблица или указанные секции не сжимаются.

    • ROW
      Таблицы или указанные секции сжимаются, используя сжатие строк.

    • PAGE
      Таблицы или указанные секции сжимаются, используя сжатие страниц.

    Дополнительные сведения о сжатии см. в разделе Создание сжатых таблиц и индексов.

  • ON PARTITIONS ( { <выражение_номера_секции> | <диапазон> } [ ,...n ] )
    Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если таблица не секционирована, аргумент ON PARTITIONS приведет к формированию ошибки. Если не указано предложение ON PARTITIONS, параметр DATA_COMPRESSION применяется ко всем секциям секционированной таблицы.

    <Выражение_номера_секции> можно указать одним из следующих способов.

    • Указав номер секции, например ON PARTITIONS (2).

    • Указав номера нескольких секций, разделив их запятыми, например ON PARTITIONS (1, 5).

    • Указав диапазоны секций и отдельные секции, например ON PARTITIONS (2, 4, 6 TO 8).

    <Диапазон> можно указать номерами секций, разделенными ключевым словом TO, например ON PARTITIONS (6 TO 8).

    Чтобы для разных секций задать разные типы сжатия данных, укажите параметр DATA_COMPRESSION несколько раз, например следующим образом.

    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> ::=
    Указывает один или более параметров индекса. Полное описание этих параметров см. в разделе CREATE INDEX (Transact-SQL).

  • PAD_INDEX = { ON | OFF }
    Если указано значение ON, процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если указано значение OFF или значение FILLFACTOR не указано, страницы промежуточного уровня заполняются до приблизительного объема, оставляющего достаточно места для, как минимум, одной строки максимального размера, которого может достигать индекс, при этом учитывается набор ключей на промежуточных страницах. Значение по умолчанию — OFF.

  • FILLFACTOR **=**fillfactor
    Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0. Значения коэффициентов заполнения 0 и 100 идентичны.

  • IGNORE_DUP_KEY = { ON | OFF }
    Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Параметр не работает во время выполнения инструкции CREATE INDEX, ALTER INDEX или UPDATE. Значение по умолчанию — OFF.

    • ON
      Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится предупреждающее сообщение. С ошибкой завершаются только строки, нарушающие ограничение уникальности.

    • OFF
      Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Будет выполнен откат всей операции INSERT.

    IGNORE_DUP_KEY нельзя установить в значение ON для индексов, создаваемых для представлений, неуникальных индексов, XML-индексов, пространственных индексов и фильтруемых индексов.

    Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

    Для обратной совместимости синтаксиса аргумент WITH IGNORE_DUP_KEY эквивалентен аргументу WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Если указано значение ON, автоматический пересчет устаревших статистик индекса не производится. Если указано значение OFF, включается автоматическое обновление статистик. Значение по умолчанию — OFF.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Если указано значение ON, при доступе к индексу допустимы блокировки строк. Необходимость в блокировке строк определяет компонент Database Engine. При значении OFF блокировки строк не используются. Значение по умолчанию — ON.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Если указано значение ON, при доступе к индексу допустимы блокировки страниц. Необходимость в блокировке страниц определяет компонент Database Engine. При значении OFF блокировки страниц не используются. Значение по умолчанию — ON.

Замечания

Сведения о допустимом количестве таблиц, столбцов, ограничений и индексов см. в разделе Задание максимальной вместимости SQL Server.

Пространство таблицам и индексам обычно выделяется по одному экстенту за раз. При создании таблицы или индекса им выделяются страницы из смешанных экстентов, пока число страниц не достигнет размера однородного экстента. Каждый раз, когда число страниц достигает размера однородного экстента, и текущие выделенные экстенты становятся заполненными, выделяется новый экстент. Получить отчет об объеме выделенного и используемого таблицей пространства можно с помощью процедуры sp_spaceused.

Компонент Database Engine не требует указания параметров DEFAULT, IDENTITY, ROWGUIDCOL или ограничения столбцов в определенном порядке при определении столбца.

После создания таблицы параметр QUOTED IDENTIFIER всегда сохраняется в метаданных таблицы в состоянии ON, даже если он был установлен в состояние OFF при создании таблицы.

Временные таблицы

Можно создавать локальные и глобальные временные таблицы. Локальные временные таблицы видимы только во время текущего сеанса, а глобальные — во всех сеансах. Временные таблицы не подлежат секционированию.

Имени локальной временной таблицы должен предшествовать префикс знака номера (#table_name), а имени глобальной временной таблицы — двойной знак номера (##table_name).

Инструкции SQL могут обращаться к временной таблице по заданному в инструкции CREATE TABLE значению аргумента table_name, например:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);

INSERT INTO #MyTempTable VALUES (1);

Если в пределах одной хранимой процедуры или пакета создается более одной временной таблицы, им должны быть присвоены разные имена.

Если локальная временная таблица создается хранимой процедурой или приложением, которые одновременно могут выполняться несколькими пользователями, компонент Database Engine должен иметь возможность различать таблицы, созданные разными пользователями. Компонент Database Engine делает это путем внутреннего присоединения числового суффикса к имени каждой локальной временной таблицы. Полное имя временной таблицы, хранящееся в таблице sysobjects базы данных tempdb, состоит из имени таблицы, заданного инструкцией CREATE TABLE, и сформированного системой числового суффикса. Для обеспечения возможности добавления суффикса значение параметра table_name, определенного как имя локальной временной таблицы, не должно содержать более 116 символов.

Временные таблицы автоматически удаляются при выходе за пределы области определения, если не удалять их явно с помощью инструкции DROP TABLE.

  • Локальная временная таблица, созданная хранимой процедурой, удаляется автоматически при завершении хранимой процедуры. К этой таблице могут обращаться любые вложенные хранимые процедуры, выполняемые хранимой процедурой, создавшей таблицу. Процесс, вызвавший хранимую процедуру, создавшую таблицу, к этой таблице обращаться не может.

  • Все прочие локальные временные таблицы удаляются автоматически в конце текущего сеанса.

  • Глобальные временные таблицы автоматически удаляются при завершении сеанса, создавшего таблицу, и прекращении обращения к ним всех прочих задач. Взаимосвязь между задачей и таблицей поддерживается только на время выполнения отдельной инструкции Transact-SQL. Это означает, что глобальная временная таблица удаляется после выполнения последней инструкции языка Transact-SQL, активно обращавшейся к ней во время завершения создавшего таблицу сеанса.

Локальная временная таблица, созданная хранимой процедурой или триггером, может иметь то же имя, что и временная таблица, созданная до вызова хранимой процедуры или триггера. Однако если запрос обращается к временной таблице и одновременно существует две таблицы с одинаковым именем, не определено, к какой из таблиц будет направлен запрос. Вложенные хранимые процедуры могут также создавать временные таблицы с тем же именем, что и временная таблица, созданная вызывающей хранимой процедурой. Однако для применения изменений к таблице, созданной во вложенной процедуре, эта таблица должна иметь ту же структуру с теми же именами столбцов, что и таблица, созданная в вызывающей процедуре. Указанные операции показаны в следующем примере.

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

Ниже приводится результирующий набор.

(1 row(s) affected)

Test1Col

-----------

1

(1 row(s) affected)

Test2Col

-----------

2

При создании локальных или глобальных временных таблиц синтаксис инструкции CREATE TABLE поддерживает определение всех ограничений, кроме FOREIGN KEY. Если во временной таблице указано ограничение FOREIGN KEY, инструкция возвращает предупредительное сообщение, указывающее на то, что ограничение было пропущено. При этом таблица создается без ограничений FOREIGN KEY. В ограничениях FOREIGN KEY обращение к временным таблицам недопустимо.

Если временная таблица создана с именованным ограничением и в области определяемой пользователем транзакции, то только один пользователь может одновременно выполнить инструкцию, создающую временную таблицу. Например, если хранимая процедура создает временную таблицу с ограничением именованного первичного ключа, то хранимая процедура не может быть одновременно выполнена несколькими пользователями.

Секционированные таблицы

Перед созданием секционированной таблицы с помощью инструкции CREATE TABLE следует вначале создать функцию секционирования, чтобы указать, как должна быть секционирована таблица. Функция секционирования создается с помощью инструкции CREATE PARTITION FUNCTION. Затем необходимо создать схему секционирования, чтобы указать файловые группы, которые будут содержать указанные функцией секционирования секции. Схема секционирования создается с помощью инструкции CREATE PARTITION SCHEME. Для секционированных таблиц нельзя указать ограничения PRIMARY KEY или UNIQUE для разделения файловых групп. Дополнительные сведения см. в разделе Секционированные таблицы и индексы.

Ограничения PRIMARY KEY

  • В таблице возможно наличие только одного ограничения по первичному ключу.

  • Индекс, формируемый ограничением PRIMARY KEY, не может привести к выходу количества индексов в таблице за пределы в 999 некластеризованных индексов и 1 кластеризованный.

  • Если для ограничения PRIMARY KEY не указан параметр CLUSTERED или NONCLUSTERED, применяется параметр CLUSTERED, если для ограничения UNIQUE не определено кластеризованных индексов.

  • Все столбцы с ограничением PRIMARY KEY должны иметь признак NOT NULL. Если допустимость значения NULL не указана, то для всех столбцов c ограничением PRIMARY KEY устанавливается признак NOT NULL.

  • Если первичный ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку. Дополнительные сведения см. в разделе Определяемые пользователем типы данных CLR.

Ограничения UNIQUE

  • Если для ограничения UNIQUE не указан параметр CLUSTERED или NONCLUSTERED, по умолчанию применяется параметр NONCLUSTERED.

  • Каждое ограничение уникальности создает индекс. Количество ограничений UNIQUE не может привести к выходу количества индексов в таблице за пределы в 999 некластеризованных индексов и 1 кластеризованный.

  • Если ограничение уникальности определено на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку или сортировку на основе оператора. Дополнительные сведения см. в разделе Определяемые пользователем типы данных CLR.

Ограничения FOREIGN KEY

  • Если столбцу, имеющему ограничение внешнего ключа, задается значение, отличное от NULL, такое же значение должно существовать и в указываемом столбце; в противном случае будет возвращено сообщение о нарушении внешнего ключа.

  • Если не указаны исходные столбцы, ограничения FOREIGN KEY применяются к предшествующему столбцу.

  • Ограничения FOREIGN KEY могут ссылаться только на таблицы в пределах той же базы данных на том же сервере. Межбазовую ссылочную целостность необходимо реализовать посредством триггеров. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).

  • Ограничения FOREIGN KEY могут ссылаться на другие столбцы той же таблицы. Это называется самовызовом.

  • Предложение REFERENCES ограничения внешнего ключа на уровне столбца может содержать только один ссылочный столбец. Этот столбец должен принадлежать к тому же типу данных, что и столбец, для которого определяется ограничение.

  • Предложение REFERENCES ограничения внешнего ключа на уровне таблицы должно содержать такое же число ссылочных столбцов, какое содержится в списке столбцов в ограничении. Тип данных каждого ссылочного столбца должен также совпадать с типом соответствующего столбца в списке столбцов.

  • Если частью внешнего ключа или ссылочного ключа является столбец типа timestamp, ключевые слова CASCADE, SET NULL и SET DEFAULT указывать нельзя.

  • Ключевые слова CASCADE, SET NULL, SET DEFAULT и NO ACTION можно сочетать в таблицах, имеющих взаимные ссылочные связи. Если компонент Database Engine обнаруживает ключевое слово NO ACTION, оно остановит и произведет откат связанных операций CASCADE, SET NULL и SET DEFAULT. Если инструкция DELETE содержит сочетание ключевых слов CASCADE, SET NULL, SET DEFAULT и NO ACTION, то все операции CASCADE, SET NULL и SET DEFAULT выполняются перед поиском компонентом Database Engine операции NO ACTION.

  • Компонент Database Engine не имеет стандартного предела на количество ограничений FOREIGN KEY, содержащихся в таблице, ссылающейся на другие таблицы, или на количество ограничений FOREIGN KEY в других таблицах, ссылающихся на указанную таблицу.

    Тем не менее фактическое количество ограничений FOREIGN KEY, доступных для использования, ограничивается конфигурацией оборудования, базы данных и приложения. Рекомендуется, чтобы таблица содержала не более 253 ограничений FOREIGN KEY, а также чтобы на нее ссылалось не более 253 ограничений FOREIGN KEY. Предел эффективности в конкретном случае может более или менее зависеть от приложения и оборудования. При разработке базы данных и приложений следует учитывать стоимость принудительных ограничений FOREIGN KEY.

  • Ограничения FOREIGN KEY не применяются к временным таблицам.

  • Ограничения FOREIGN KEY могут ссылаться только на столбцы с ограничениями PRIMARY KEY или UNIQUE в таблице, на которую указывает ссылка, или на столбцы уникального индекса (UNIQUE INDEX) такой таблицы.

  • Если внешний ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку. Дополнительные сведения см. в разделе Определяемые пользователем типы данных CLR.

  • Столбец типа varchar(max) может участвовать в ограничении FOREIGN KEY только при условии, что первичный ключ, на который он ссылается, также принадлежит к типу varchar(max).

Определения DEFAULT

  • Столбец может иметь только определение DEFAULT.

  • Ограничение DEFAULT может содержать значения констант, функции, функции без параметров SQL-92 или значение NULL. В следующей таблице приведены функции без параметров и возвращаемые ими по умолчанию значения в процессе выполнения инструкции INSERT.

    Функция без параметров SQL-92

    Возвращаемое значение

    CURRENT_TIMESTAMP

    Текущие дата и время.

    CURRENT_USER

    Имя пользователя, выполняющего вставку.

    SESSION_USER

    Имя пользователя, выполняющего вставку.

    SYSTEM_USER

    Имя пользователя, выполняющего вставку.

    USER

    Имя пользователя, выполняющего вставку.

  • Значение constant_expression в определении DEFAULT не может ссылаться на другой столбец таблицы, также как и на другие таблицы, представления или хранимые процедуры.

  • Определения DEFAULT нельзя создавать для столбцов с типом данных timestamp или столбцов со свойством IDENTITY.

  • Определения DEFAULT нельзя создавать для столбцов с псевдонимами типов данных, если такой тип привязан к определенному по умолчанию объекту.

Ограничения CHECK

  • Столбец может содержать любое количество ограничений CHECK, а условие может включать несколько логических выражений, соединенных операторами AND и OR. При указании нескольких ограничений CHECK для столбца их проверка производится в порядке создания.

  • Условие поиска должно возвращать логическое выражение и не может ссылаться на другую таблицу.

  • Ограничение CHECK уровня столбца может ссылаться только на ограничиваемый столбец, а ограничение CHECK уровня таблицы — только на столбцы этой таблицы.

    Правила и ограничения CHECK выполняют одну и ту же функцию проверки данных при выполнении инструкций INSERT и UPDATE.

  • Если для столбца или столбцов задано правило либо одно или несколько ограничений CHECK, применяются все ограничения.

  • Ограничения CHECK нельзя определять для столбцов типов text, ntext или image.

Дополнительные сведения об ограничениях

  • Индекс, созданный для ограничения, не может быть удален с помощью инструкции DROP INDEX; необходимо удалить ограничение с помощью инструкции ALTER TABLE. Индекс, созданный для ограничения и используемый им, можно перестроить с помощью инструкции ALTER INDEX.

  • Имена ограничений должны подчиняться правилам для идентификаторов, за исключением того, что не могут начинаться со знака числа (#). Если аргумент constraint_name не указан, то ограничению присваивается имя, формируемое системой. Имя ограничения отображается в любых сообщениях об ошибках, связанных с нарушением ограничения.

  • При нарушении ограничения в инструкции INSERT, UPDATE или DELETE выполнение инструкции прекращается. Однако если параметр SET XACT_ABORT установлен в OFF, а инструкция является частью явной транзакции, выполнение этой транзакции продолжается. Если параметр SET XACT_ABORT установлен в ON, производится откат всей транзакции. С определением транзакции можно также использовать инструкцию ROLLBACK TRANSACTION, установив флажок для системной функции **@@**ERROR.

  • Если присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, страниц и таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы. Дополнительные сведения см. в разделе Укрупнение блокировки (компонент Database Engine). Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц. Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса.

  • Если в таблице содержатся ограничения FOREIGN KEY или CHECK и триггеры, условия ограничений вычисляются перед выполнением триггера.

Получить отчет о таблице и ее столбцам можно с помощью процедуры sp_help или sp_helpconstraint. Для переименования таблицы используется процедура sp_rename. Чтобы получить сведения о представлениях и хранимых процедурах, зависящих от таблицы, используйте функции sys.dm_sql_referenced_entities и sys.dm_sql_referencing_entities.

Правила допустимости значения NULL в рамках определения таблицы

Допустимость значения NULL для столбца зависит от того, разрешено ли значение NULL в качестве допустимого значения данных этого столбца. Значение NULL не равнозначно нулю или пустой строке: значение NULL указывает, что запись не была произведена или было явно указано значение NULL; обычно оно означает, что значение неизвестно либо неприменимо.

При создании или изменении таблицы с помощью инструкции CREATE TABLE или ALTER TABLE настройки базы данных и сеанса влияют на возможность типа данных, указанного в определении столбца, принимать значение NULL и могут переопределять ее. Рекомендуется всегда явно определять столбец как NULL или NOT NULL для невычисляемых столбцов или, если используется пользовательский тип данных, разрешать, чтобы для столбца применялась возможность, установленная для этого типа по умолчанию. Для разреженных столбцов всегда должно быть разрешено значение NULL.

Если возможность столбца принимать значение NULL не задана явно, она определяется согласно правилам, указанным в следующей таблице.

Тип данных столбца

Правило

Псевдоним типа данных

Компонент Database Engine использует допустимость значений NULL, указанную при создании типа данных. Чтобы выяснить допустимость значений NULL по умолчанию для типа данных, используется процедура sp_help.

Определяемый пользователем тип данных CLR

Допустимость значения NULL определяется в соответствии с определением столбца.

Системный тип данных

Если для системного типа данных предусмотрен только один вариант, он и применяется. Для столбцов типа данных timestamp должен быть указан параметр NOT NULL.

Если любые параметры сеанса с помощью инструкции SET установлены в ON:

  • ANSI_NULL_DFLT_ON = ON — применяется NULL.

  • ANSI_NULL_DFLT_OFF = ON — применяется NOT NULL.

  • Если настроены какие-либо параметры базы данных с помощью инструкции ALTER DATABASE:

  • ANSI_NULL_DEFAULT_ON = ON — применяется NULL.

  • ANSI_NULL_DEFAULT_OFF = ON — применяется NOT NULL.

  • Просмотреть параметр базы данных ANSI_NULL_DEFAULT можно в представлении каталога sys.databases

Если для сеанса не установлен ни один из параметров ANSI_NULL_DFLT, а база данных настроена по умолчанию (ANSI_NULL_DEFAULT = OFF), применяется установленное по умолчанию значение NOT NULL.

Если столбец является вычисляемым, допустимость значения NULL для него всегда определяется компонентом Database Engine автоматически. Определить допустимость значения NULL для этого типа столбцов можно с помощью функции COLUMNPROPERTY со свойством AllowsNull.

ПримечаниеПримечание

Как драйвер ODBC для SQL Server, так и поставщик OLE DB для SQL Server (Майкрософт) предусматривают по умолчанию значение параметра ANSI_NULL_DFLT_ON = ON. Пользователи ODBC и OLE DB могут настраивать этот параметр в источниках данных ODBC или с помощью установки атрибутов или свойств соединения в приложении.

Сжатие данных

В системных таблицах не может быть включено сжатие. При создании таблицы параметру сжатия данных присваивается значение NONE, если не указано иное. При указании списка секций или секции вне диапазона формируется ошибка. Дополнительные сведения о сжатии данных см. в разделе Создание сжатых таблиц и индексов.

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры sp_estimate_data_compression_savings.

Разрешения

Требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.

Если какие-либо столбцы в инструкции CREATE TABLE определены как принадлежащие к определяемому пользователем типу данных CLR, необходимо быть владельцем данного типа либо иметь разрешение REFERENCES на него.

Если какие-либо столбцы в инструкции CREATE TABLE имеют связанную коллекцию схем XML, необходимо быть владельцем этого набора схем или иметь разрешение REFERENCES на него.

Примеры

А. Использование ограничений PRIMARY KEY

В следующем примере показано определение столбца для ограничения PRIMARY KEY с кластеризованным индексом на столбце BusinessEntityID таблицы Employee (имя ограничения назначается системой) в образце базы данных База данных AdventureWorks2008R2.

BusinessEntityID int
PRIMARY KEY CLUSTERED

Б. Использование ограничений FOREIGN KEY

Ограничение FOREIGN KEY используется для ссылки на другую таблицу. Внешние ключи могут включать один или несколько столбцов. В следующем примере показано ограничение FOREIGN KEY с одним столбцом в таблице SalesOrderHeader, ссылающееся на таблицу SalesPerson. Для ограничения FOREIGN KEY с одним столбцом требуется только предложение REFERENCES.

SalesPersonID int NULL
REFERENCES SalesPerson(BusinessEntityID)

Кроме того, предложение FOREIGN KEY можно применить явно и заново определить атрибут столбца. Обратите внимание, что имена столбцов в обеих таблицах могут различаться.

FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(BusinessEntityID)

Ограничения по ключам с несколькими столбцами создаются в виде табличных ограничений. В базе данных База данных AdventureWorks2008R2 таблица SpecialOfferProduct включает ограничение PRIMARY KEY с несколькими столбцами. В следующем примере показано, как обращаться к этому ключу из другой таблицы; задавать имя ограничения явно необязательно.

CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
 (ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)

В. Использование ограничений UNIQUE

Ограничения UNIQUE используются для указания уникальности столбцов, не принадлежащих к первичному ключу. В следующем примере применяется ограничение уникальности столбца Name таблицы Product.

Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

Г. Использование определений DEFAULT

Определения DEFAULT (вместе с инструкциями INSERT и UPDATE) позволяют указать значение по умолчанию, используемое, если значение не задано. Например, база данных База данных AdventureWorks2008R2 может включать таблицу подстановки с перечислением различных должностей, которые могут занимать сотрудники компании. В столбце, описывающем каждую должность, значение символьной строки по умолчанию может содержать описание, отображаемое, если фактическое описание должности не было введено явно.

DEFAULT 'New Position - title not formalized yet'

Кроме констант, определения DEFAULT могут включать функции. Следующий пример позволяет получить текущую дату для той или иной записи.

DEFAULT (getdate())

Обработка функциями без параметров также может повысить целостность данных. Чтобы определить пользователя, вставившего строку, используйте функцию без параметров для USER. Не заключайте функции без параметров в скобки.

DEFAULT USER

Д. Использование ограничений CHECK

В следующем примере показано ограничение, применяемое к значениям, вводимым в столбец CreditRating таблицы Vendor. Ограничение не имеет имени.

CHECK (CreditRating >= 1 and CreditRating <= 5)

В этом примере показано именованное ограничение вводимых в столбец таблицы символьных данных по шаблону.

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]')

В этом примере указывается, что значения должны входить в заданный список или соответствовать заданному шаблону.

CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')

Е. Вывод на экран полного определения таблицы

В следующем примере выводятся полные определения таблицы со всеми определениями ограничений для таблицы PurchaseOrderDetail, созданной в базе данных База данных AdventureWorks2008R2. Обратите внимание на то, что для выполнения этого образца схема таблицы заменяется на схему 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;

Ж. Создание таблицы со столбцом, приведенным к типу коллекции схем XML

В следующем примере создается таблица со столбцом xml, приведенным к типу коллекции схем XML HRResumeSchemaCollection. Ключевое слово DOCUMENT указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать только один элемент верхнего уровня.

USE AdventureWorks2008R2;
GO
CREATE TABLE HumanResources.EmployeeResumes 
   (LName nvarchar(25), FName nvarchar(25), 
    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

З. Создание секционированной таблицы

В следующем примере создается функция секционирования для разделения таблицы или индекса на четыре секции. Затем создается схема секционирования, определяющая файловые группы, в которых содержится каждая из четырех секций. Наконец, создается таблица, использующая схему секционирования. В примере предполагается, что в базе данных уже существуют файловые группы.

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

Секции назначаются на основе столбца col1 таблицы PartitionTable следующими способами.

Файловая группа

test1fg

test2fg

test3fg

test4fg

Секция

1

2

3

4

Значение

col 1 <= 1

col1 > 1 AND col1 <= 100

col1 > 100 AND col1 <= 1 000

col1 > 1000

И. Использование типа данных uniqueidentifier в столбце

В следующем примере создается таблица со столбцом типа uniqueidentifier. В этом примере используется ограничение PRIMARY KEY для защиты таблицы от вставки пользователями повторяющихся значений, а также функция NEWSEQUENTIALID() в ограничении DEFAULT для указания значений для новых строк. К столбцу uniqueidentifier применяется свойство ROWGUIDCOL, чтобы на столбец можно было ссылаться с помощью ключевого слова $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) );

К. Использование выражения для вычисляемого столбца

В следующем примере показано использование выражения ((low + high)/2) для вычисления столбца myavg.

CREATE TABLE dbo.mytable 
    ( low int, high int, myavg AS (low + high)/2 ) ;

Л. Создание вычисляемого столбца на основе столбца определяемого пользователем типа

В следующем примере создается таблица с одним столбцом, имеющим определяемый пользовательским тип utf8string, и предполагается, что как сборка, содержащая данный тип, так и сам тип, уже созданы в текущей базе данных. Второй столбец определяется на основе типа utf8string и использует метод ToString() типа type(class)utf8string для вычисления значения столбца.

CREATE TABLE UDTypeTable 
    ( u utf8string, ustr AS u.ToString() PERSISTED ) ;

М. Использование функции USER_NAME для вычисляемого столбца

В следующем примере используется функция USER_NAME() в столбце myuser_name.

CREATE TABLE dbo.mylogintable
    ( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;

Н. Создание таблицы со столбцом FILESTREAM

В следующем примере создается таблица со столбцом FILESTREAMPhoto. Если таблица содержит один или более столбцов FILESTREAM, она должна содержать столбец ROWGUIDCOL.

CREATE TABLE dbo.EmployeePhoto
    (
    EmployeeId int NOT NULL PRIMARY KEY
    ,Photo varbinary(max) FILESTREAM NULL
    ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
        UNIQUE DEFAULT NEWID()
    );

О. Создание таблицы, использующей сжатие строк

В следующем примере создается таблица, использующая сжатие строк.

CREATE TABLE dbo.T1 
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);

Дополнительные примеры сжатия данных см. в разделе Создание сжатых таблиц и индексов.

П. Создание таблицы с разреженными столбцами и набором столбцов

В следующих примерах показано создание таблицы с разреженным столбцом и таблицы с двумя разреженными столбцами и набором столбцов. В примерах используется основной синтаксис. Более сложные примеры см. в разделах Использование разреженных столбцов и Использование наборов столбцов.

В следующем примере создается таблица с разреженным столбцом.

CREATE TABLE dbo.T1
    (c1 int PRIMARY KEY,
    c2 varchar(50) SPARSE NULL ) ;

В этом примере создается таблица с двумя разреженными столбцами и набором столбцов с именем CSet.

CREATE TABLE T1
    (c1 int PRIMARY KEY,
    c2 varchar(50) SPARSE NULL,
    c3 int SPARSE NULL,
    CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;