Практическая работа 1. 2 Описание проектирования базы данных 2 практическая работа 2 10
 Скачать 2.52 Mb.
|
|
Инструкция CREATE TABLE (Transact-SQL) Для создания таблиц используется оператор "CREATE TABLE", который приводит к созданию пустой таблицы без строк. При создании таблиц задается имя таблицы, описание набора столбцов с их именами, типами и размерами, а также ограничения на хранящуюся в таблице информацию. Имена таблиц в пределах базы данных должны быть уникальны. Каждый столбец в таблице должен иметь имя, уникальное в пределах таблицы, а также либо тип данных, ограничения целостности, либо выражение для вычисления значения столбца. Общий синтаксис CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ AS FileTable ] ( { | [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { partition_scheme_name | filegroup | "default" } ] [ WITH ( [ ; ] column_name [ FILESTREAM ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ] [ ROWGUIDCOL ] [ ::= [ type_schema_name . ] type_name [ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] [ 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 ) } column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( ] | [ 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_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 ( ] [ 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 ) } { [DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { | [ FILETABLE_DIRECTORY = [ FILETABLE_COLLATE_FILENAME = { } ] [ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = [ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = [ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = } { 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 ( { | } TO Рассмотрим и расшируем назначение ключевых слов и аргументов команды CREATE TABLE. Аргументы: database_name Имя базы данных, в которой создается таблица. Параметр database_name должен указывать имя существующей базы данных. Если аргумент database_name не указан, по умолчанию список стоп-слов создается в текущей базе данных. Имя входа для текущего соединения должно быть связано с идентификатором пользователя, существующего в базе данных, указанной аргументом database_name, а этот пользователь должен обладать разрешениями CREATE TABLE. schema_name Имя схемы, которой принадлежит новая таблица. table_name Имя новой таблицы. Имена таблиц должны соответствовать правилам для идентификаторов. Аргумент table_name может состоять не более чем из 128 символов, за исключением имен локальных временных таблиц (имена с одним префиксом номера #), длина которых не должна превышать 116 символов. AS FileTable Создает новую таблицу FileTable. Нет необходимости указывать столбцы, так как таблица FileTable имеет фиксированное схему. Дополнительные сведения о таблицах FileTable см. в разделе Таблицы FileTable (SQL Server). column_name Имя столбца в таблице. Имена столбцов должны соответствовать правилам именования идентификаторов и быть уникальными в рамках таблицы. Аргумент column_name может иметь длину не более 128 символов. Аргумент column_name может быть опущен для столбцов, создаваемых с типом данных timestamp. Если аргумент column_name не указан, столбцу типа timestamp по умолчанию присваивается имя timestamp. computed_column_expression Выражение, определяющее значение вычисляемого столбца. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. Значение столбца вычисляется на основе выражения, использующего другие столбцы той же таблицы. Например, определение вычисляемого столбца может быть следующим: cost AS price * qty. Выражение может быть именем невычисляемого столбца, константой, функцией, переменной или любой их комбинацией, соединенной одним или несколькими операторами. Выражение не может быть вложенным запросом или содержать псевдонимы типов данных. Вычисляемые столбцы могут использоваться в списках выбора, предложениях WHERE, ORDER BY и в любых других местах, в которых могут использоваться обычные выражения, за исключением следующих случаев. Вычисляемый столбец нельзя использовать ни в качестве определения ограничения DEFAULT или FOREIGN KEY, ни вместе с определением ограничения NOT NULL. Однако вычисляемый столбец может использоваться в качестве ключевого столбца индекса или части какого-либо ограничения PRIMARY KEY или UNIQUE, если значение этого вычисляемого столбца определяется детерминистическим выражением и тип данных результата разрешен в столбцах индекса. Вычисляемый столбец не может быть целевым столбцом инструкций INSERT или UPDATE. Примечание Каждая строка таблицы может содержать различные значения столбцов, задействованных в вычисляемом столбце; таким образом, значение вычисляемого столбца не будет одним и тем же в каждой строке. PERSISTED - указывает, что компонент Компонент SQL Server Database Engine будет физически хранить вычисляемые значения в таблице и обновлять их при изменении любого столбца, от которого зависит вычисляемый столбец. Указание PERSISTED для вычисляемого столбца позволяет создать индекс по вычисляемому столбцу, который будет детерминистическим, но неточным. ON { | filegroup | "default" } Указывает схему секционирования или файловую группу, в которой хранится таблица. Если аргумент указан, таблица будет разбита на секции, хранимые в одной или нескольких файловых группах, указанных аргументом <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 Масштаб указанного типа данных. 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, значения в столбцах идентификаторов не приращиваются, если вставку выполняют агенты репликации. Если ограничение сопровождается этим предложением, оно не выполняется, когда агенты репликации выполняют операции вставки, обновления или удаления. 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 в связанной таблице или на столбцы, на которые имеются ссылки в индексе 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 Всем значениям, составляющим внешний ключ, присваивается их значение по умолчанию, когда обновляется соответствующая строка в родительской таблице. 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 ( { <partition_number_expression> | <range> } [ ,...n ] ) Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если таблица не секционирована, аргумент ON PARTITIONS приведет к формированию ошибки. 4.5. Упрощенный синтаксис оператора создания таблицы: <определение_таблицы> ::= 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 ); Выполните 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 ] (лог_выражение) } Рассмотрим отдельные параметры представленных конструкций, связанные с ограничениями целостности данных. Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся ограничение первичного ключа PRIMARY KEY, ограничение внешнего ключа FOREIGN KEY, ограничение уникальности UNIQUE, ограничение значения NULL, ограничение на проверку CHECK. 4.6.1. Ограничение первичного ключа (PRIMARY KEY) Таблица обычно имеет столбец или комбинацию столбцов, значения которых уникально идентифицируют каждую строку в таблице. Этот столбец (или столбцы) называется первичным ключом таблицы и нужен для обеспечения ее целостности. Если в первичный ключ входит более одного столбца, то значения в пределах одного столбца могут дублироваться, но любая комбинация значений всех столбцов первичного ключа должна быть уникальна. При создании первичного ключа SQL Server автоматически создает уникальный индекс для столбцов, входящих в первичный ключ. Он ускоряет доступ к данным этих столбцов при использовании первичного ключа в запросах. Таблица может иметь только одно ограничение PRIMARY KEY, причем ни один из включенных в первичный ключ столбцов не может принимать значение NULL. При попытке использовать в качестве первичного ключа столбец (или группу столбцов), для которого ограничения первичного ключа не выполняются, первичный ключ создан не будет, а система выдаст сообщение об ошибке. Поскольку ограничение PRIMARY KEY гарантирует уникальность данных, оно часто определяется для столбцов-счетчиков. Создание ограничения целостности PRIMARY KEY возможно как при создании, так и при изменении таблицы. Одним из назначений первичного ключа является обеспечение ссылочной целостности данных нескольких таблиц. Естественно, это может быть реализовано только при определении соответствующих внешних ключей в других таблицах. Пример 2. Создание таблицы Товар с ограничением первичного ключа. CREATE TABLE Товар (КодТовара INT IDENTITY(1,1) PRIMARY KEY, Название VARCHAR(50), Цена MONEY, Тип VARCHAR(50), Сорт VARCHAR(50), Город VARCHAR(50), Остаток INT ); Примечание. Прежде чем выполнятьsql-код удалите ранее созданную таблицуТовар из базы данных и обновите ее. Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример2.sql в папке ФИО_студента/Лаб4. Первичные ключи более чем одного поля Ограничение PRIMARY KEY может также быть применено для многочисленных полей, составляющих уникальную комбинацию значений. Предположим, что ваш первичный ключ – это имя, и вы имеете первое имя и последнее имя, сохраненными в двух различных полях ( так что вы можете организовывать данные с помощью любого из них ). Очевидно, что ни первое ни последнее имя нельзя заставить быть уникальным самостоятельно, но мы можем каждую из этих двух комбинаций сделать уникальной. Мы можем применить ограничение таблицы PRIMARY KEY для пар: Пример 3. Создание таблицы Сотрудники с ограничением первичного ключа. CREATE TABLE Сотрудники ( Фамилия char (10), Имя char (10) , Город char (10), PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример3.sql в папке ФИО_студента/пр4. Одна проблема в этом подходе та, что мы можем вынудить появление уникальности - например, введя Иванов Андрей и Иванов А. Это может ввести в заблуждение, потому что ваши служащие могут не знать кто из них кто. Обычно более надежный способ чтобы определять числовое поле которое могло бы отличать одну строку от другой, это иметь первичный ключ, и применять ограничение UNIQUE для двух имен полей. 4.6.2. Использование ограничений для исключения пустых (NULL ) указателей Вы можете использовать команду CREATE TABLE чтобы предохранить поле от разрешения в нем пустых (NULL) указателей с помощью ограничения NOT NULL. Это ограничение накладывается только для разнообразных столбцов. NULL – это специальное обозначение, которое отмечает поле как пустое. NULL может быть полезен, когда имеются случаи, когда вы хотите быть от них гарантированы. Очевидно, что первичные ключи никогда не должны быть пустыми, поскольку это будет подрывать их функциональные возможности. Кроме того, такие поля как имена, требуют в большинстве случаев, определенных значений. Например, вы вероятно захотите иметь информацию о должности занимаемым каждым сотрудником в таблице Сотрудники. Если вы поместите ключевые слова NOT NULL сразу после типа данных (включая размер) столбца, любая попытка поместить значение NULL в это поле будет отклонена. В противном случае, SQL понимает, что NULL раз решен. Например, давайте улучшим наше определение таблицы Сотрудники, не позволяя помещать NULL значения в столбец Должность : Пример 4. Создание таблицы Сотрудники с ограничением пустых значений. CREATE TABLE Сотрудники ( Фамилия char (10), Имя char (10) , Город char (10), ……….Должность ……сhar (10) NOT NULL, PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример4.sql в папке ФИО_студента/пр.4. Важно помнить, что любому столбцу с ограничением NOT NULL должно быть установлено значение в каждом предложении INSERT воздействующем на таблицу. При отсутствии NULL, SQL может не иметь значений для установки в эти столбцы, если конечно значение по умолчанию, описанное ра нее в этой главе, уже не было назначено. Если ваша система поддерживает использование ALTER TABLE чтобы добавлять новые столбцы к уже существующей таблице, вы можете вероятно помещать ограничение столбцов, типа NOT NULL, для этих новых столбцов. Однако, если вы предписываете новому столбцу значение NOT NULL, текущая таблица должна быть пустой!!!!. 4.6.2. Использование ограничений для уникальности значений предыдущей пр. работе мы обсудили использование уникальных индексов чтобы заставить поля иметь различные значения для каждой строки. Эта практика - осталась с прежних времен, когда SQL поддерживал ограничение UNIQUE. Уникальность – это свойство данных в таблице, и поэтому его более логично назвать как ограничение этих данных, а не просто как свойство логического отличия, связывающее объект данных (индекс). Несомненно, уникальные индексы - один из самых простых и наиболее эффективных методов предписания уникальности. По этой причине, некоторые реализации ограничения UNIQUE используют уникальные индексы; то есть они создают индекс, не сообщая вам об этом. Остается фактом, что вероятность беспорядка в базе данных достаточно мала, если вы предписываете уникальность вместе с ограничением. Уникальность как ограничение столбца Время от времени, вы хотите убедиться, что все значения введеные в столбец отличаются друг от друга. Например, первичные ключи достаточно ясно это показывают. Если вы помещаете ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были обьявлены как непустые (NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL значениями как дубликаты. Имеется дальнейшее усовершенствование нашей команды создания таблицы Сотрудники : Пример 5. Создание таблицы Сотрудники с ограничением уникальности. CREATE TABLE Сотрудники ( Фамилия char (10) NOT NULL UNIQUE, Имя char (10) NOT NULL UNIQUE, Город char (10), ……….Должность ……сhar (10) NOT NULL, PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример5.sql в папке ФИО_студента/Лаб4. Когда вы обьявляете поля Фамилия уникальным, убедитесь, что в вашей базе данных не будет двух Ивановых или Петровых. В то же время это не так уж необходимо функциональной точки зрения - потому что поле Имя в качестве первичного ключа, все равно обеспечит отличие этих двух строк - что проще для людей, использующих данные в таблицах, чем помнить, что эти Ивановы не идентичны. Столбцы (не первичные ключи), чьи значения требуют уникальности, называются ключами-кандидатами или уникальными ключами. 4.6.3. Ограничение по умолчанию (DEFAULT) Столбцу может быть присвоено значение по умолчанию. Оно будет актуальным в том случае, если пользователь не введет в столбец никакого иного значения. Отдельно необходимо отметить пользу от использования значений по умолчанию при добавлении нового столбца в таблицу. Если для добавляемого столбца не разрешено хранение значений NULL и не определено значение по умолчанию, то операция добавления столбца закончится неудачей. Когда вы вставляете строку в таблицу без указания значений в ней для каждого поля, SQL должен иметь значение по умолчанию для включения его в определенное поле, или же команда будет отклонена. Наиболее общим значением по умолчанию является - NULL. Это - значение по умолчанию для любого столбца, которому не было дано ограничение NOT NULL или который имел другое назначение по умолчанию. Значение DEFAULT (ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом что и ограничение столбца, хотя, с технической точки зрения, значение DEFAULT не ограничительного свойства - оно не ограничивает значения, которые вы можете вводить, а просто определяет, что может случиться, если вы не введете любое из них. Предположим, что вы работаете в г. Москва и подавляющее большинство ваших сотрудников живут в этом городе. Вы можете указать г. Москва в качестве значения поля Город, по умолчанию, для вашей таблицы Сотрудников: Пример 7. Создание таблицы Сотрудники с значением по умолчанию. CREATE TABLE Сотрудники ( Фамилия char (10) NOT NULL UNIQUE, Имя char (10) NOT NULL UNIQUE, Город char (10) DEFAULT 'Москва', ……….Должность ……сhar (10) NOT NULL, PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример7.sql в папке ФИО_студента/Лаб4. Конечно, вводить значение Москва в таблицу каждый раз, когда назначается новый сотрудник, не такая уж необходимость, и можно просто пре небречь им (не вводя его) даже если оно должно иметь некоторое значение. Другой способ использовать значение по умолчанию – это использовать его как альтернативу для NULL. Так как NULL (фактически) неверен при любом сравнении, ином чем IS NULL, он может быть исключен с помощью большинства предикатов. Иногда, вам нужно видеть пустые значения ваших полей, не обрабатывая их каким-то определенным образом. Вы можете установить значение по умолчанию, типа нуль или пробел, которые функционально меньше по значению чем просто не установленное значение - пустое значение (NULL). Различие между ними и обычным NULL в том, что SQL будет обрабатывать их также как и любое другое значение. 4.6.4. Ограничение проверочное (CHECK) Данное ограничение используется для проверки допустимости данных, вводимых конкретный столбец таблицы, т.е. ограничение CHECK обеспечивает еще один уровень защиты данных. Ограничения целостности CHECK задают диапазон возможных значений для столбца или столбцов. В основе ограничений целостности CHECK лежит использование логических выражений. Допускается применение нескольких ограничений CHECK к одному и тому же столбцу. В этом случае они будут применимы в той последовательности, в которой происходило их создание. Возможно применение одного и того же ограничения к разным столбцам и использование в логических выражениях значений других столбцов. Проверка значений полей Конечно, имеется любое число ограничений, которые можно устанавливать для данных, вводимых в ваши таблицы, чтобы видеть, например, находятся ли данные в соответствующем диапазоне или правильном формате, о чем SQL естественно не может знать заранее. По этой причине, SQL обеспечивает вас ограничением CHECK, которое позволяет вам установить условие, которому должно удовлетворять значение, вводимое в таблицу, прежде чем оно будет принято. Ограничение CHECK состоит из ключевого слова CHECK сопровождаемого предложением предиката, который использует указанное поле. Любая попытка модифицировать или вставить значение поля, которое могло бы сделать этот предикат неверным - будет отклонена. Давайте рассмотрим таблицу Продавцы. Столбец комиссионных выражается десятичным числом и поэтому может быть умножен непосредственно на сумму приобретений в результате чего будет получена сумма комиссионных (в долларах) продавца с установленым справа значком доллара ($). Кто-то может использовать понятие процента, однако ведь, можно об этом и не знать. Если человек введет по ошибке 14 вместо .14 чтобы указать в процентах свои комиссионные, это будет расценено как 14.0, что является законным десятичным значением, и будет нормально воспринято системой. Чтобы предотвратить эту ошибку, мы можем наложить ограничение столбца - CHECK чтобы убедиться, что вводимое значение меньше, чем 1. Пример 8. Создание таблицы Продавцы и с проверкой значений полей CREATE TABLE Продавцы ( КодПродавца integer NOT NULL PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Город char(10), Комиссионные Использование - CHECK, чтобы предопределять допустимое вводимое значение decimal CHECK (Комиссионные < 1 )); Мы можем также использовать ограничение CHECK чтобы защитить от ввода в поле определенных значений, и таким образом предотвратить ошибку. Например, предположим, что единствеными городами в которых мы имели ведомства сбыта являются Лондон, Барселона, Сан Хосе, и Нью Йорк. Если вам известны все продавцы работающие в каждом из этих ведомств, нет необходимости позволять ввод других значений. Если же нет, использова ние ограничения может предотвратить опечатки и другие ошибки. Пример 8. Создание таблицы Продавцы и с проверкой значений полей, чтобы предопределять допустимое вводимое значение CREATE TABLE Продавцы ( КодПродавца integer NOT NULL PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Город char(10) CHECK (Город IN (' Лондон ', 'Барселона', ' Сан Хосе ', ' Нью Йорк ')), Комиссионные decimal CHECK (Комиссионные < 1 )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример8.sql в папке ФИО_студента/Лаб4. Конечно, если вы собираетесь сделать это, вы должны быть уверены что ваша компания не открыла уже новых других ведомств сбыта. Большинство программ баз данных поддерживают команду ALTER TABLE , которая позволяет вам изменять определение таблицы, даже когда она находится в использовании. Однако, изменение или удаление ограничений не всегда возможно для этих команд, даже там где это вроде бы поддерживается. Если вы использовали систему, которая не может удалять ограничения, вы будете должны создавать (CREATE) новую таблицу и передавать информацию из старой таблицы в нее всякий раз, когда вы хотите изменить ограничение. Конечно же Вы не захотите делать это часто, и со временем вообще перестанете это делать. Проверка условий, базирующийся на многочисленых полях Вы можете также использовать CHECK в качестве табличного ограничения. Это полезно в тех случаях, когда вы хотите включить более одного поля строки в условие. Предположим что комиссионные 0.15 и выше, будут разрешены только для продавца из Барселоны. Вы можете указать это со следующим табличным ограничением CHECK : Пример 9. Создание таблицы Продавцы и с проверкой значений полей, базирующийся на многочисленых полях CREATE TABLE Продавцы2 ( КодПродавца integer NOT NULL PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Город char(10) , Комиссионные decimal, CHECK (Комиссионные < 0.15 OR Город='Барселона')); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример9.sql в папке ФИО_студента/Лаб4. Как вы можете видеть, два различных поля должны быть проверены чтобы определить, верен предикат или нет. Имейте в виду, что это - два разных поля одной и той же строки. Хотя вы можете использовать многочис леные поля, SQL не может проверить более одной строки одновременно. Вы не можете, например использовать ограничение CHECK чтобы удостовериться что все комиссионные в данном городе одинаковы. Чтобы сделать это, SQL должен всякий раз просматривая другие строки таблицы, когда вы модифицируете или вставляете строку, видеть, что значение комиссионных указано для текущего города. SQL этого делать не умеет. Фактически, вы могли бы использовать сложное ограничение CHECK для вышеупомянутого, если бы знали заранее, каковы должны быть комиссионные в разных городах. Самостоятельно измените ограничение в примере 9 на следующее: Если комиссионные равны 0.15 , то будут разрешены только для продавца из Лондана Если комиссионные равны 0.14 , то будут разрешены только для продавца из Барселоны Если комиссионные равны 0.13 , то будут разрешены только для продавца из Сан-Хосе Если комиссионные равны 0.12 , то будут разрешены только для продавца из Нью-Йорка Вы получили идею. Чем налагать такой комплекс ограничений, вы могли бы просто использовать представление с предложением WITH CHECK OPTION, которое имеет все эти условия в своем предикате. Пользователи могут обращаться к представлению таблицы вместо самой таблицы. Одним из преимуществ этого будет то, что процедура изменения в ограничении не будет такой болезненной или трудоемкой. Представление с WITH CHECK OPTION - хороший заменитель ограничению CHECK. Пример 10. Создание таблицы Клиент с ограничениями. CREATE TABLE Клиент (КодКлиента INT IDENTITY(1,1) PRIMARY KEY, Фирма VARCHAR(50) NOT NULL, Фамилия VARCHAR(50) NOT NULL, Город VARCHAR(50) NOT NULL, Телефон CHAR(10) NOT NULL CHECK (Телефон LIKE '[1-9][0-9]-[0-9][0-9]-[0-9][0-9]')); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример10.sql в папке ФИО_студента/Лаб4. 4.6.5. Ограничение внешнего ключа (FOREIGN KEY) Ограничение внешнего ключа - это основной механизм для поддержания ссылочной целостности между таблицами реляционной базы данных. Столбец дочерней таблицы, определенный в качестве внешнего ключа в параметре FOREIGN KEY, применяется для ссылки на столбец родительской таблицы, являющийся в ней первичным ключом. Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES. Данные в столбцах, определенных в качестве внешнего ключа, могут принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы. Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно. Первичный ключ может быть определен для столбца с одним именем, в то время как столбец, на который наложено ограничение FOREIGN KEY, может иметь совершенно другое имя. Единственным требованием остается соответствие столбцов по типу и размеру данных. На первичный ключ могут ссылаться не только столбцы других таблиц, но и столбцы, расположенные в той же таблице, что и собственно первичный ключ; это позволяет создавать рекурсивные структуры. Внешний ключ может быть связан не только с первичным ключом другой таблицы. Он может быть определен и для столбцов с ограничением UNIQUE второй таблицы или любых других столбцов, но таблицы должны находиться в одной базе данных. Столбцы внешнего ключа могут содержать значение NULL, однако проверка на ограничение FOREIGN KEY игнорируется. Внешний ключ может быть проиндексирован, тогда сервер будет быстрее отыскивать нужные данные. Внешний ключ определяется как при создании, так и при изменении таблиц. Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии дочерней записи блокируется, равно как и удаление родительской записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO ACTION, принятыми по умолчанию. Для разрешения каскадного воздействия следует использовать параметры ON DELETE CASCADE и ON UPDATE CASCADE. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности: CASCADE - выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы; SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL; SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию; NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена. Те же самые правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется. Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе. При определении таблицы предложение FOREIGN KEY может указываться произвольное количество раз. операторе CREATE TABLE используется необязательная фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать. Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE. Пример 11. Создание таблицы Склад с ограничениями первичного ключа и внешнего ключа. CREATE TABLE Сделка (КодСделки INT IDENTITY(1,1) PRIMARY KEY,
| |||