Практическая работа 1. Проектирование базы данных
 Скачать 2.91 Mb.
|
|
partition_scheme>. Если указан аргумент filegroup, таблица сохраняется в файловой группе с таким именем. Это должна быть существующая файловая группа в базе данных. Если указано значение "default" или параметр ON не определен вообще, таблица сохраняется в файловой группе по умолчанию. Механизм хранения таблицы, указанный в инструкции CREATE TABLE, изменить в дальнейшем невозможно. TEXTIMAGE_ON { filegroup| "default" } Указывают, что столбцы типов text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), а также определяемых пользователем типов данных CLR (включая geometry и geography) хранятся в указанной файловой группе. FILESTREAM_ON { partition_scheme_name | filegroup | "default" } Задает файловую группу для данных FILESTREAM. Если таблица содержит данные FILESTREAM и является секционированной, необходимо включить предложение FILESTREAM_ON и указать схему секционирования файловых групп файлового потока. В этой схеме секционирования должны использоваться те же функции и столбцы секционирования, что и в схеме секционирования для таблицы; в противном случае возникает ошибка. [ type_schema_name. ] type_name Указывает тип данных столбца и схему, к которой он принадлежит. Тип данных может быть одним из следующих. Системный тип данных. Псевдонимы типа на основе системного типа данных SQL Server. Определяемый пользователем тип данных CLR. Прежде чем определяемые пользователем типы данных CLR можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE. Для создания столбца с определяемым пользователем типом данных CLR требуется разрешение REFERENCES на этот тип. precision Точность указанного типа данных. scale Масштаб указанного типа данных. 84 CONTENT Указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать несколько элементов верхнего уровня. Аргумент CONTENT применим только к данным типа xml. DOCUMENT Указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать только один элемент верхнего уровня. Аргумент DOCUMENT применим только к данным типа xml. DEFAULT Указывает значение, присваиваемое столбцу в случае отсутствия явно заданного значения при вставке. Определения DEFAULT могут применяться к любым столбцам, кроме имеющих тип timestamp или обладающих свойством IDENTITY. Определения DEFAULT удаляются, когда таблица удаляется из памяти. В качестве значения по умолчанию могут использоваться только константы (например, символьные строки), скалярные функции (системные, определяемые пользователем или функции CLR) или значение NULL. 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, значения в столбцах идентификаторов не приращиваются, если вставку выполняют агенты репликации. Если ограничение сопровождается этим предложением, оно не выполняется, когда агенты репликации выполняют операции вставки, обновления или удаления. 85 ROWGUIDCOL Указывает, что новый столбец является столбцом идентификаторов GUID строки. Только один столбец типа uniqueidentifier в таблице может быть назначен в качестве столбца ROWGUIDCOL. Применение свойства ROWGUIDCOL позволяет ссылаться на столбец с помощью ключевого слова $ROWGUID. Свойство ROWGUIDCOL может быть присвоено только столбцу типа uniqueidentifier. Ключевым словом ROWGUIDCOL нельзя обозначать столбцы определяемых пользователем типов данных. SPARSE Указывает, что столбец является разреженным столбцом. Хранилище разреженных столбцов оптимизируется для значений NULL. Для разреженных столбцов нельзя указать параметр NOT NULL. Дополнительные ограничения и сведения о разреженных столбцах см. в разделе Использование разреженных столбцов. FILESTREAM Допустимо только для столбцов типа varbinary(max). Указывает хранилище FILESTREAM для данных BLOB типа varbinary(max). COLLATE collation_name Задает параметры сортировки для столбца. Могут использоваться параметры сортировки Windows или параметры сортировки SQL. Параметр collation_name применим только к столбцам типов данных char, varchar, text, nchar, nvarchar и ntext. Если этот аргумент не указан, столбцу назначаются либо параметры сортировки определяемого пользователем типа, если столбец принадлежит к определяемому пользователем типу данных, либо установленные по умолчанию параметры сортировки для базы данных. CONSTRAINT Необязательное ключевое слово, указывающее на начало определения ограничения PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY или CHECK. constraint_name Имя ограничения. Имена ограничений должны быть уникальными в пределах схемы, к которой принадлежит таблица. NULL | NOT NULL Определяет, допустимы ли для столбца значения NULL. PRIMARY KEY Ограничение, которое обеспечивает целостность сущностей для указанного столбца или столбцов с помощью уникального индекса. Можно создать только одно ограничение PRIMARY KEY для таблицы. UNIQUE Ограничение, которое обеспечивает целостность сущностей для указанного столбца или столбцов с помощью уникального индекса. В таблице может быть несколько ограничений UNIQUE. FOREIGN KEY REFERENCES Ограничение, которое обеспечивает ссылочную целостность данных в этом столбце или столбцах. Ограничения FOREIGN KEY требуют, чтобы каждое значение в столбце существовало в соответствующем связанном столбце или столбцах в связанной таблице. Ограничения FOREIGN KEY могут ссылаться только на столбцы, являющиеся ограничениями PRIMARY KEY или UNIQUE в связанной таблице или на столбцы, на 86 которые имеются ссылки в индексе 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. ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Указывает, какое действие совершается над строками в изменяемой таблице, когда эти строки имеют ссылочную связь и строка родительской таблицы, на которую указывает ссылка, обновляется. Параметр по умолчанию — NO ACTION. NO ACTION Компонент Компонент Database Engine возвращает ошибку, а обновление строки родительской таблицы откатывается. CASCADE Соответствующие строки обновляются в ссылающейся таблице, если эта строка обновляется в родительской таблице. SET NULL Всем значениям, составляющим внешний ключ, присваивается значение NULL, когда обновляется соответствующая строка в родительской таблице. Для выполнения этого ограничения внешние ключевые столбцы должны допускать значения NULL. SET DEFAULT Всем значениям, составляющим внешний ключ, присваивается их значение по умолчанию, когда обновляется соответствующая строка в родительской таблице. 87 CHECK Ограничение, обеспечивающее целостность домена путем ограничения возможных значений, которые могут быть введены в столбец или столбцы. Ограничения CHECK в вычисляемых столбцах должны быть также помечены как PERSISTED. logical_expression Логическое выражение, возвращающее значения TRUE или FALSE.. column Столбец или список столбцов (в скобках), используемый в ограничениях таблицы для указания столбцов, используемых в определении ограничения. [ ASC | DESC ] Указывает порядок сортировки столбца или столбцов, участвующих в ограничениях таблицы. Значение по умолчанию — ASC. partition_scheme_name Имя схемы секционирования, определяющей файловые группы, которым сопоставляются секции секционированной таблицы. Эта схема секционирования должна существовать в базе данных. [ partition_column_name. ] Указывает столбец, по которому будет секционирована таблица. Столбец должен соответствовать по типу данных, длине и точности столбцу, указанному в функции секционирования, используемой аргументом partition_scheme_name. Вычисляемый столбец, участвующий в функции секционирования, должен быть явно обозначен ключевым словом PERSISTED. Важно! WITH FILLFACTOR =fillfactor Указывает, насколько плотно компонент Компонент Database Engine должен заполнять каждую страницу индекса, используемую для хранения данных индекса. column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS Имя набора столбцов. Набор столбцов представляет собой нетипизированное XML- представление, в котором все разреженные столбцы таблицы объединены в структурированные выходные данные. < table_option> ::= Указывает один или более параметров таблицы. DATA_COMPRESSION Задает режим сжатия данных для указанной таблицы, номера секции или диапазона секций. Ниже приведены доступные параметры. ON PARTITIONS ( { | Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если таблица не секционирована, аргумент ON PARTITIONS приведет к формированию ошибки. 4.5. Упрощенный синтаксис оператора создания таблицы: 88 <определение_таблицы> ::= CREATE TABLE [ имя_базы_данных.[владелец]. | владелец. ] имя_таблицы (<элемент_таблицы>[,...n]) где <элемент_таблицы> ::= {<определение_столбца>} | <имя_столбца> AS <выражение> |>ограничение_таблицы< Обычно владельцем таблицы (dbo) является тот, кто ее создал. <Выражение> задает значение для вычисляемого столбца. <определение_столбца> ::= { имя_столбца <тип_данных>} [ [ DEFAULT <выражение> ] | [ IDENTITY (начало, шаг) [NOT FOR REPLICATION]]]] [ROWGUIDCOL][<ограничение_столбца>][...n]] В определении столбца обратим внимание на параметр IDENTITY, который указывает, что соответствующий столбец будет столбцом-счетчиком. Для таблицы может быть определен только один столбец с таким свойством. Можно дополнительно указать начальное значение и шаг приращения. Если эти значения не указываются, то по умолчанию они оба равны 1. Если с ключевым словом IDENTITY указано NOT FOR REPLICATION, то сервер не будет выполнять автоматического генерирования значений для этого столбца, а разрешит вставку в столбец произвольных значений. В качестве ограничений используются ограничения столбца и ограничения таблицы. Различие между ними в том, что ограничение столбца применяется только к определенному полю, а ограничение таблицы - к группам из одного или более полей. Различные типы ограничений рассмотрим позже. Пример создания таблицы без ограничений. Для выполнения данных запросов предварительно откройте среду Micrpsoft SQL Server Managmant Studio, выполните соединение с сервером и откройте базу данных Educator. Нажмите на панели инструментов команду Создать запрос. Пример 4.1. Создание родительской таблицы Товар без ограничений. use Educator CREATE TABLE Товар (КодТовара INT IDENTITY(1,1), Название VARCHAR(50) , Цена MONEY , Тип VARCHAR(50) , Сорт VARCHAR(50) , Город VARCHAR(50) , Остаток INT ); 89 Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример1.sql в папке ФИО_студента/Лаб4. Автоматическая генерация значения столбца КодТовара достигается за счет использования свойства IDENTITY, по умолчанию начальное значение, генерируемое с помощью IDENTITY равно 1, так же как и его приращение. Таким образом, следующее значение будет равно 2. Значения в IDENTITY-столбцах обязательно последовательные, то есть если приращение положительное, то следующее значение всегда больше предыдущего, если приращение отрицательное, то – всегда меньше. Приращение и начальное значение могут быть заданы, однако этот механизм чрезвычайно редко используется в реальных проектах. 4.6. Создание ограничений В качестве ограничений используются ограничения столбца и ограничения таблицы. Различие между ними в том, что ограничение столбца применяется только к определенному полю, а ограничение таблицы - к группам из одного или более полей. <ограничение_столбца>::= [ CONSTRAINT имя_ограничения ] { [ NULL | NOT NULL ] | [ {PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR=фактор_заполнения ] [ ON {имя_группы_файлов | DEFAULT } ] ] ] | [ [ FOREIGN KEY ] REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ]] | CHECK [ NOT FOR REPLICATION](<лог_выражение>) } <ограничение_таблицы>::= [CONSTRAINT имя_ограничения ] { [ {PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] {(имя_столбца [ASC | DESC][,...n])} [WITH FILLFACTOR=фактор_заполнения ] [ON {имя_группы_файлов | DEFAULT } ]] |FOREIGN KEY[(имя_столбца [,...n])] REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [,...n])] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] | NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] (лог_выражение) } 90 Рассмотрим отдельные параметры представленных конструкций, связанные с ограничениями целостности данных. Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся ограничение первичного ключа PRIMARY KEY, ограничение внешнего ключа FOREIGN KEY, ограничение уникальности UNIQUE, ограничение значения NULL, ограничение на проверку CHECK. 4.6.1. Ограничение первичного ключа (PRIMARY KEY) Таблица обычно имеет столбец или комбинацию столбцов, значения которых уникально идентифицируют каждую строку в таблице. Этот столбец (или столбцы) называется первичным ключом таблицы и нужен для обеспечения ее целостности. Если в первичный ключ входит более одного столбца, то значения в пределах одного столбца могут дублироваться, но любая комбинация значений всех столбцов первичного ключа должна быть уникальна. При создании первичного ключа SQL Server автоматически создает уникальный индекс для столбцов, входящих в первичный ключ. Он ускоряет доступ к данным этих столбцов при использовании первичного ключа в запросах. Таблица может иметь только одно ограничение PRIMARY KEY, причем ни один из включенных в первичный ключ столбцов не может принимать значение NULL. При попытке использовать в качестве первичного ключа столбец (или группу столбцов), для которого ограничения первичного ключа не выполняются, первичный ключ создан не будет, а система выдаст сообщение об ошибке. Поскольку ограничение |