Mathmech wiki

9 Создание таблиц

7 min read

Вроде текст взяли отсюда

CREATE TABLE example(
id int
);

Нельзя создать таблицу без полей.

Автоинкрементные поля

При описании полей таблицы, поле можно отметить свойством IDENTITY

IDENTITY (m, n) [NOT FOR [[$Базы данных. Определения#репликация|репликация]]]

m - стартовый индекс (умолчание - 1)

n - инкремент (умолчание - 1)

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

MS SQL Server позволяет создавать до 2 биллионов таблиц в базе данных, каждая таблица может содержать до 1024 колонки и общий размер полей в таблице может достигать 8060 байтов на строку (без учета text, ntext и image)

Синтаксис

CREATE TABLE 
    [ database_name.[ owner ] . | owner. ] table_name 
    ( { < column_definition > 
     | column_name AS computed_column_expression 
     | < table_constraint > ::= [ CONSTRAINT constraint_name ]}
     | [ { PRIMARY KEY | UNIQUE } [ ,...n ] 
    ) 
 
[ ON { filegroup | DEFAULT } ] 
[ TEXTIMAGE_ON { filegroup | DEFAULT } ] 
 
< column_definition > ::= { column_name data_type } 
    [ COLLATE < collation_name > ] 
    [ [ DEFAULT constant_expression ] 
    | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
    ] 
    [ ROWGUIDCOL] 
    [ < column_constraint > ] [ ...n ] 
 
< column_constraint > ::= [ CONSTRAINT constraint_name ] 
    { [ NULL | NOT NULL ] 
        | [ { PRIMARY KEY | UNIQUE } 
            [ CLUSTERED | NONCLUSTERED ] 
            [ WITH FILLFACTOR = fillfactor ] 
            [ON {filegroup | DEFAULT} ] ] 
        ] 
        | [ [ FOREIGN KEY ] 
            REFERENCES ref_table [ ( ref_column ) ] 
            [ ON DELETE { CASCADE | NO ACTION } ] 
            [ ON UPDATE { CASCADE | NO ACTION } ] 
            [ NOT FOR REPLICATION ] 
        ] 
        | CHECK [ NOT FOR REPLICATION ] 
        ( logical_expression ) 
    } 
 
< table_constraint > ::= [ CONSTRAINT constraint_name ] 
    { [ { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        { ( column [ ASC | DESC ] [ ,...n ] ) } 
        [ WITH FILLFACTOR = fillfactor ] 
        [ ON { filegroup | DEFAULT } ] 
    ] 
    | FOREIGN KEY 
        [ ( column [ ,...n ] ) ] 
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { CASCADE | NO ACTION } ] 
        [ ON UPDATE { CASCADE | NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] 
        ( search_conditions ) 
    }

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

имя тип параметры

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

Указание кодировки, хранимой в поле

CREATE TABLE TestTable 
(
   Famil varchar(10), 
   Name varchar(10) collate latin1_general_cs_as
)

При создании таблицы можно указать файловую группу, в которой должны храниться данные этой таблицы. Имя группы указывается после ключевого слова ON, за объявлением всех полей. В следующем примере создается таблица, данные которой будут помещены в группу с именем group1:

CREATE TABLE TestTable 
(
   Famil varchar(10), 
   Name varchar(10) collate latin1_general_cs_as
) ON group1

При разработке запросов вы можете использовать ключевое слово IDENTITYCOL в месте с именем колонки в запросе. Это позволяет вам ссылаться на колонку в таблице со свойством Identity, не зная имени колонки. Так как в таблице может быть только одна колонка со свойством Identity, сервер сам сможет найти нужное поле.

Создание таблицы с автоинкрементом, начинающимся с 1 с шагом 1

CREATE TABLE TestTable2
(
 id int IDENTITY(1,1)
)

Создание таблицы с значением по умолчанию

CREATE TABLE TestTable 
(
 id int DEFAULT 1,
 dDate datetime DEFAULT (getdate()),
 vcName varchar(50) DEFAULT 'M'
)

Ограничение на NULL

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 dDate datetime DEFAULT (getdate()) NULL,
 vcName varchar(50) NOT NULL
)

Ограничение check

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 iApartment int CHECK 
    (iApartment>0 AND iApartment<1000)
)

check с адекватным названием

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 iApartment int,
 dDate datetime,
 CONSTRAINT check_iApartment CHECK 
    (iApartment>0 and iApartment<1000),
 CONSTRAINT check_dDate CHECK 
    (dDate<getdate())
)

“сахар”

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 cPol char(1),
 CONSTRAINT check_cPol CHECK 
    (cPol IN ('М', 'Ж'))
)

Проверка целостности ущербной регуляркой

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcPhonenumber varchar(50) NOT NULL
 CONSTRAINT check_vcPhonenumber CHECK
 (vcPhonenumber LIKE 
   '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]')
)

Требование уникальности значений в столбце таблице (UNIQUE)

CREATE TABLE Names
(
 idName int, 
 vcName varchar(50),
 vcLastName varchar(50),
 vcSurName varchar(50),
 dBirthDay datetime, 
 CONSTRAINT cn_unique UNIQUE (idName)
)

Уникальность по группе столбцов

CREATE TABLE Names
(
 idName int , 
 vcName varchar(50),
 vcLastName varchar(50),
 vcSurName varchar(50),
 dBirthDay datetime, 
 CONSTRAINT cn_unique UNIQUE (vcName, vcLastName, vcSurName, dBirthDay)
)

Определение первичного ключа

CREATE TABLE Globally_Unique_Data
(
 guid uniqueidentifier DEFAULT NEWID(),
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (Guid)
)

Определение ключа по 2 атрибутам

CREATE TABLE Товары1
(
  id int IDENTITY(1, 1),
  Товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY 
         (id, [Товар])
)

Связывание таблиц через внешний ключ

CREATE TABLE Names
(
 idName int IDENTITY(1,1), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName),
)
 
CREATE TABLE Phones
(
 idPhone int IDENTITY(1,1), 
 idName int, 
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

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

Таблица может содержать до 253 внешних ключей

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

Создание рекурсивной связи

CREATE TABLE Positions
(
 idPosition int IDENTITY(1,1), 
 idParentPosition int, 
 vcName varchar(30),  
 CONSTRAINT PK_idPosition PRIMARY KEY (idPosition),
 CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition)
   REFERENCES Positions (idPosition)
)

Создание связи один к одному (через общий id)

CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)
 
CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Names (idName)
)

Многие ко многим

CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)
 
CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)
)
 
CREATE TABLE LinkTable -- нахрена PK только - не понмаю
(
 idLinkTable uniqueidentifier DEFAULT NEWID(),
 idName uniqueidentifier, 
 idPhone uniqueidentifier, 
 
 CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),
 
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Phones (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

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

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

Создание локальной временной таблицы:

CREATE TABLE #TestTable 
 (
  id INT PRIMARY KEY
 )

Создание глобальной временной таблицы (существует до завершения сессии, которая создала таблицу):

CREATE TABLE ##TestTable 
 (
  id INT PRIMARY KEY
 )

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

Возможные применения:

  • Фиксация состояния таблицы для отчета
  • Очень сложные запросы, который сложно сделать одним SELECT

Recent Notes

Graph View

Backlinks