Sql правила целостности данных


Содержание

Целостность данных

Под целостностью понимают «правильность» данных, содержащихся в БД, по отношению к предметной области. Целостность данных подразделяется на следующие категории.

Целостность на уровне сущности означает обеспечение уникальности экземпляра сущности, т.е., строки в конкретной таблице. Она обеспечивается указанием ограничений целостности PRIMARY KEY или UNIQUE в SQL, или указанием первичного ключа таблицы.

Доменная целостность — достоверность значений атрибута, или в конкретном столбце. Она поддерживается в соответствии с указанием типа данных, формата, правил и ограничений CHECK, а также ограничения на диапазон возможных значений, задаваемого с помощью FOREIGN KEY (внешний ключ), DEFAULT (значение по умолчанию), определений NOT NULL (недопустимость значения NULL).

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

При обеспечении ссылочной целостности СУБД не допускает следующих действий пользователей.

· Добавления или изменения строк в связанной таблице, если в первичной таблице нет соответствующей строки.

· Изменения значений в первичной таблице, которое приводит к появлению потерянных строк в связанной таблице.

· Удаления строк из первичной таблицы, если имеются соответствующие ей строки в связанных таблицах.

Пример таблиц с нарушением ссылочной целостности:

Студент Дисциплина
Студ_ИД Студ_Фио Студ_рейтинг СтудентДисциплина Дсц_ИД Наим
Иванов Студ_ИД Дсц_ИД Оценка ТАУ
Иванов ОБЖ
Иванова

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

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

DML-триггеры выполняются по событиям, вызванным попыткой пользователя изменить данные с помощью языка обработки данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению (View). Триггеры DML используют внутренние логические таблицы, именуемые deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя.

DML-триггеры могут обращаться к другим таблицам. DML-триггеры удобно использовать в следующих случаях:

· для каскадных изменений в связанных таблицах базы данных;

· для предотвращения случайных или неправильных операций INSERT, UPDATE и DELETE.

При редактировании связанных таблиц в зависимости от заданных ограничений поддержки целостности СУБД может:

· выполнить каскадное (CASCADE) удаление/обновление строк в связанных таблицах;

· не выполнять действий (NO ACTION) по редактированию связанных таблиц.

Учебник / Учебное пособие Раздел Страницы
1. Карпова Т.С. Базы данных: модели, разработка, реализация. — СПб.: Питер, 2001.- 304 с.; ил. Глава 7. 121-134
2. Хомоненко А.Д, Цыганков В.М., Мальцев М.Г. Базы данных: Учебник для высших учебных заведений / под ред. проф. Хомоненко А.Д., 6-ое изд. – М.:Бином-Пресс; СПб.:Корона-Век, 2007.-736с. Гл.5 175 -194
Учебник / Учебное пособие Раздел Страницы
1. Коннолли Т., Бегг К., Страчан А. Базы данных: проектирование, реализация и сопровождение. Теория и практика, 2-е изд.: Пер. с англ. — М.: Издательский дом “Вильямс”,. 2001. – 1120 с.: ил. 4.3 – 4.5, 6.1 — 6.8 159 – 168, 223 — 240
2. Дейт К. Дж. Введение в системы баз данных, 6-е издание: Пер. с англ. – К.;М.; СПб.: Издательский дом “Вильямс”,. 1999. – 848 с.: ил. (7-е и 8-е издание). 9.1 – 9.3, 10.1 – 10.3 271 – 274, 288 – 301

Тема 4. Работа с данными. Язык баз данных

Лекция №4. Основные понятия языка баз данных на примере SQL

В рамках данной лекции рассматриваются нижеследующие вопросы:

· Понятие языка программирования баз данных.

· DML: процедурные и декларативные языки; операции низкого и высокого уровней

· Хранимые и формируемые объекты базы данных

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

Языки для работы с базами данных зависят от того, какую модель данных поддерживает СУБД: реляционную, иерархическую или сетевую.

Применительно к СУБД различают языки низкоуровневой обработки, в которых в одной операции осуществляется обработка одной записи, и языки высокоуровневой обработки, в одной операции которых обрабатывается совокупность записей.

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

Язык SQL, или язык структурированных запросов (Structured Query Language), ориентирован на использование в реляционных СУБД. Он прошел несколько этапов своего развития и первоначально создавался с целью получения языка запросов, приближенного к естественному (английскому). Операторы, или предложения (statements), языка имеют сложную структуру, напоминающую конструкцию сложно-подчиненного предложения, в котором имеются главное и подчиненное (clause) предложения.

Язык SQL является машинно-независимым и декларативным, т.е., описывающим свойства искомого объекта, а не алгоритм его поиска, причем описывающим в машинно-независимом текстовом формате. В настоящее время SQL стандартизован, чтобы его понимали серверы различных производителей.

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

· Data Definition Language (DDL). Язык описания данных. Содержит операторы, позволяющие определять структуру и свойства базы данных, объектов в БД и задавать связи между объектами (например, CREATE TABLE, CREATE VIEW).

· Data Manipulation Language (DML). Язык манипулирования данными. Содержит операторы, предназначенные для обработки данных, т.е. для того, чтобы выбирать, добавлять, удалять и модифицировать данные (SELECT, INSERT, DELETE, UPDATE).

· Transaction Control Language (TCL). Язык управления транзакциями. Операторы этого языка применяются для управления транзакциями, или логическими единицами работы, выполняемыми как атомарная операция.

· Data Control Language (DCL). Операторы языка применяются для осуществления административных функций назначения или отмены разрешений на использование базы данных, таблицы в базе данных, а также выполнения операторов SQL.

· Cursor Control Language (CCL). Операторы используются для определения объекта «курсор», подготовки SQL-предложений для выполнения, а также для некоторых других операторов.

Работа по реализации БД начинают с описания ее схемы на языке DDL.

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

Схема представляет структуру БД: состав, структуру, свойства и связи объектов базы данных.

Определение структуры таблицы означает описание ее столбцов, т.е. имен, типов данных и возможных ограничений. Перед созданием таблицы необходимо определить:

· Какую сущность будет представлять таблица, или каково назначение таблицы.

· Число столбцов в таблице и тип данных и формат (если это необходимо) каждого столбца.

· Имеются ли столбцы, которые могут принимать значения NULL.

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

· Какие столбцы должны использоваться в качестве первичных ключей, а какие — в качестве внешних.

Сокращенный синтаксис оператора CREATE TABLE показывает структуру описания таблицы, задание ограничений на таблицу и столбцы, а также виды ограничений.

CREATE TABLE имя_таблицы ( < | >[ ] [ . n ] )

::=имя столбца [ ]

::=[ CONSTRAINT имя ограничения ]

[ CLUSTERED | NONCLUSTERED ] | [ FOREIGN KEY ]

REFERENCES [ имя связанной таблицы [ ( связанный столбец ) ]

Рассмотрим примеры применения операторов языка SQL.

1) Таблицы должны находиться в базе данных. Поэтому сначала необходимо создать БД:

CREATE DATABASE TestData;

2) Создание таблицы с данными о студентах:

CREATE TABLE dbo.Students (StudentID int PRIMARY KEY NOT NULL,

StudentName varchar(25) NOT NULL, Stipend money NULL,

StudentDescription text NULL)

Объекты в БД могут быть хранимыми и формируемыми (т.е. создаваемыми динамически). Таблица обычно является хранимым объектом. На основе одной или нескольких таблиц можно динамически создавать виртуальную таблицу, или представление. При этом СУБД будет сохранять не сформированную таблицу, а оператор, который описывает ее формирование. Представление называется VIEW и соответствующий оператор его создания имеет синтаксис:

CREATE VIEW [ имя представления [ (столбец [ . n ] ) ]


Вернуться на главную страницу. или ЗАКАЗАТЬ РАБОТУ

188.64.174.135 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

Sql правила целостности данных

Ограничения целостности в базах данных SQL Server, CHECK, пользовательские типы данных (user-defined types, UDF), RULE, первичный ключ (PRIMARY KEY), ограничения уникальности (UNIQUE)

Главное средство обеспечение доменной целостности в SQL Server — это ограничение CHECK. Оно может быть определено при создании таблицы или добавлено позднее при помощи команды ALTER TABLE, например:

ALTER TABLE dbo.Employees

CHECK (BirthDate > ’01-01-1900′ AND BirthDate

На графическом экране ограничение можно создать (или получить информацию/изменить/удалить) на графическом экране Enterprise Manager, открыв таблицу в режиме Design Table, а затем нажав на кнопку Manage Constraints. Про ограничение CHECK необходимо сказать, что:

· можно проверять соответствие только константным значениям (диапазону значений). Использовать подзапросы в ограничении нельзя.

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

Практически полный аналог Chec k в SQL Server — это Rule , правило. Фактически этот тот же самый CHECK, но создаваемый как отдельный объект базы данных. В результате созданное правило мы можем привязывать ко множеству столбцов в базе:

CREATE RULE id_chk AS @id BETWEEN 0 and 10000

sp_bindrule id_chk, ‘cust_sample.cust_id’

На графическом экране Enterprise Manager работа с правилами производится из контейнера Rules под контейнером баз данных.

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

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

Средства обеспечения сущностной целостности очевидны: это — первичные ключи и ограничения уникальности. Первичный ключ можно определять при создании таблицы или потом при помощи команды ALTER TABLE:

ALTER TABLE doc_exe

ADD column_b INT IDENTITY

CONSTRAINT column_b_pk PRIMARY KEY

На графическом экране — так же открываем таблицу в режиме Design table и нажимаем на кнопку Manage Indexes/Keys. Оттуда же создаем и ограничение UNIQUE — средство обеспечения уникальности значений без первичного ключа.

Ссылочная целостность, обеспечивается, как уже говорилось, системой первичных и внешних ключей. Создание их — см. создание первичного ключа. Можно определять их как при создании таблицы, так и после, средствами TSQL (ALTER TABLE) или Enterprise Manager.

Ограничения уникальности и проверочные ограничения Unique Constraints and Check Constraints

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Ограничения UNIQUE и CHECK — это два типа ограничений, которые могут использоваться для обеспечения целостности данных в таблицах SQL Server SQL Server . UNIQUE constraints and CHECK constraints are two types of constraints that can be used to enforce data integrity in SQL Server SQL Server tables. Это важные объекты базы данных. These are important database objects.

Эта тема описана в следующих разделах. This topic contains the following sections.

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

Ограничения представляют собой правила, которые принудительно применяются в Компонент SQL Server Database Engine SQL Server Database Engine от имени пользователя. Constraints are rules that the Компонент SQL Server Database Engine SQL Server Database Engine enforces for you. Например, ограничения UNIQUE можно использовать для обеспечения того, чтобы в указанные столбцы, не входящие в состав первичного ключа, не вводились повторяющиеся значения. For example, you can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Хотя уникальность значений ограничения UNIQUE и PRIMARY KEY гарантируют в равной степени, в случае, когда необходимо обеспечить уникальность в столбце или комбинации столбцов, которые не являются первичными ключевыми, вместо ограничения PRIMARY KEY следует использовать ограничение UNIQUE. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

В отличие от PRIMARY KEY, ограничения UNIQUE допускают значение NULL. Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. Однако, как и всякое другое значение столбца с ограничением UNIQUE, NULL может встречаться только один раз. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. На ограничение UNIQUE могут ссылаться ограничения FOREIGN KEY. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

При добавлении ограничения UNIQUE на уже существующий столбец или группу столбцов в таблице, компонент Компонент Database Engine Database Engine по умолчанию проверяет уникальность всех существующих значений в указанных столбцах. When a UNIQUE constraint is added to an existing column or columns in the table, by default, the Компонент Database Engine Database Engine examines the existing data in the columns to make sure all values are unique. При попытке добавить ограничение UNIQUE к столбцу, содержащему повторяющиеся значения, компонент Компонент Database Engine Database Engine возвращает ошибку, а ограничение не добавляется. If a UNIQUE constraint is added to a column that has duplicated values, the Компонент Database Engine Database Engine returns an error and does not add the constraint.

Компонент Компонент Database Engine Database Engine автоматически создает индекс UNIQUE, что обеспечивает выполнение требований уникальности значений для ограничений UNIQUE. The Компонент Database Engine Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Поэтому, при попытке вставки в таблицу строки с повторяющимися данными, компонент Компонент Database Engine Database Engine выдает сообщение об ошибке, в котором сообщается о нарушении ограничения UNIQUE, а строка в таблицу не вставляется. Therefore, if an attempt to insert a duplicate row is made, the Компонент Database Engine Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Для обеспечения выполнения ограничения UNIQUE по умолчанию создается уникальный некластеризованный индекс, если явно не указано создание кластеризованного индекса. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

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

Проверочные ограничения принудительно сохраняют целостность домена, ограничивая значения, которые может принимать один или более столбцов. CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. Проверочное ограничение можно создать с любым логическим выражением, возвращающим значение TRUE или FALSE на основе логических операторов. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. Например, чтобы ограничить интервал значений столбца salary , можно создать ограничение CHECK, позволяющее столбцу принимать значения только в интервале от 15 000 до 100 000 долларов. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. Это ограничение исключает возможность устанавливать размер зарплаты, отличный от обычного. This prevents salaries from being entered beyond the regular salary range. Логическое выражение будет иметь следующий вид: salary >= 15000 AND salary . The logical expression would be the following: salary >= 15000 AND salary .

К одному столбцу можно применять несколько проверочных ограничений. You can apply multiple CHECK constraints to a single column. Кроме того, можно применять одно проверочное ограничение к нескольким столбцам. Для этого ограничение нужно создать на уровне таблицы. You can also apply a single CHECK constraint to multiple columns by creating it at the table level. Например, с помощью проверочного ограничения, распространяющегося на несколько столбцов, можно подтвердить, что в любой строке со значением USA в столбце country_region имеется также двухсимвольное значение в столбце state . For example, a multiple-column CHECK constraint could be used to confirm that any row with a country_region column value of USA also has a two-character value in the state column. Это позволяет выполнить проверку сразу нескольких условий из одного выражения. This allows for multiple conditions to be checked in one location.

Проверочные ограничения подобны ограничениям внешнего ключа, так как они управляют значениями, которые присваиваются столбцу. CHECK constraints are similar to FOREIGN KEY constraints in that they control the values that are put in a column. Однако они по-разному определяют допустимые значения. Ограничения внешнего ключа получают список допустимых значений из другой таблицы, а проверочные ограничения определяют допустимые значения по логическому выражению. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, while CHECK constraints determine the valid values from a logical expression.

Ограничения, которые включают явное или неявное преобразование данных, могут вызывать ошибки в операциях такого рода. Constraints that include implicit or explicit data type conversion may cause certain operations to fail. Например, ограничения, заданные для таблиц, которые являются исходными при переключении секций, могут приводить к ошибкам при использовании оператора ALTER TABLE. SWITCH. For example, such constraints defined on tables that are sources of partition switching may cause an ALTER TABLE. SWITCH operation to fail. Следует избегать преобразования типов данных в определениях ограничений. Avoid data type conversion in constraint definitions.

Ограничения проверочных ограничений Limitations of CHECK Constraints

Проверочные ограничения отклоняют значения, вычисляемые в FALSE. CHECK constraints reject values that evaluate to FALSE. Поскольку значения NULL вычисляются как UNKNOWN, то их наличие в выражениях может переопределить ограничение. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. Например, предположим, что на столбец MyColumn типа int установлено следующее ограничение: MyColumn может содержать только значение 10 (MyColumn=10). For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn=10). При вставке значения NULL в столбец MyColumnкомпонент Компонент Database Engine Database Engine вставит значение NULL и не возвратит ошибку. If you insert the value NULL into MyColumn, the Компонент Database Engine Database Engine inserts NULL and does not return an error.

Проверочное ограничение возвращает TRUE, если для проверяемого условия в любой строке таблицы отсутствует значение FALSE. A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. Проверочное ограничение работает на уровне строки. A CHECK constraint works at the row level. Если в только что созданной таблице отсутствуют строки, то любое проверочное ограничение на эту таблицу считается допустимым. If a table that has just been created does not have any rows, any CHECK constraint on this table is considered valid. В результате могут возвращаться неожиданные результаты, как в следующем примере. This situation can produce unexpected results, as in the following example.

Ограничение CHECK показывает, что в таблице CheckTbl должна быть хотя бы одна строка. The CHECK constraint being added specifies that there must be at least one row in table CheckTbl . Однако поскольку в таблице нет ни одной строки, над которой можно было бы произвести проверку ограничения, инструкция ALTER TABLE завершается успешно. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.

Проверочные ограничения не проверяются во время выполнения инструкций DELETE. CHECK constraints are not validated during DELETE statements. Таким образом, выполнение инструкций DELETE над таблицами с некоторыми типами проверочных ограничений может приводить к неожиданным результатам. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. Например, предположим, что следующие выражения выполняются над таблицей CheckTbl . For example, consider the following statements executed on table CheckTbl .

Инструкция DELETE выполняется успешно, даже если ограничение CHECK определяет, что в таблице CheckTbl должна быть хотя бы 1 строка. The DELETE statement succeeds, even though the CHECK constraint specifies that table CheckTbl must have at least 1 row.

Если таблица опубликована для репликации, то изменения схемы следует проводить при помощи инструкции языка Transact-SQL ALTER TABLE или объектов SMO. If the table is published for replication, you must make schema changes using the Transact-SQL statement ALTER TABLE or SQL Server Management Objects (SMO). При изменении схемы с помощью конструктора таблиц или конструктора диаграмм баз данных конструктор пытается удалить и затем вновь создать таблицу. When schema changes are made using the Table Designer or the Database Diagram Designer, it attempts to drop and recreate the table. Но поскольку удалять опубликованные объекты нельзя, изменения схемы не будут применены. You cannot drop published objects, therefore the schema change will fail.

Как применить правила целостности данных в моей базе данных?

Я проектирую эту коллекцию классов и абстрактных (MustInherit) классов…

Это таблица базы данных, где я собираюсь хранить все это .

Насколько известно базе данных Microsoft SQL Server, это все столбцы, допускающие значение NULL.

Но на самом деле это зависит от класса, который там хранится: LinkNode, HtmlPageNode или CodePageNode.

Правила могут выглядеть так .

Как обеспечить соблюдение таких правил целостности данных в моей базе данных?

ОБНОВЛЕНИЕ: Относительно этого дизайна единого стола .

Я все еще пытаюсь сосредоточиться на окончательной архитектуре.

Я изначально начал с множества небольших таблиц с почти нулевыми нулевыми полями.
Какая схема базы данных лучше всего подходит для моей навигации?

Но потом я узнал, что LINQ to SQL поддерживает только наследование одной таблицы.
Может ли столбец LINQ to SQL IsDiscriminator НЕ наследовать?

Сейчас я пытаюсь справиться с этим с помощью коллекции классов и абстрактных классов.
Пожалуйста, помогите мне с моими абстрактными классами .NET.

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

Похоже, вы пытаетесь использовать шаблон « Наследование отдельных таблиц». Этот шаблон описан в разделе «Объектно-реляционные структурные шаблоны» книги « Шаблоны архитектуры корпоративных приложений» .

Я бы порекомендовал шаблоны наследования таблиц классов или конкретных таблиц, если вы хотите обеспечить целостность данных с помощью ограничений таблиц SQL.

Хотя это не было бы моим первым предложением, вы все равно могли бы использовать Single Table Inheritance и просто применять ограничения через хранимую процедуру.

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

Вы также можете создать уникальную таблицу для каждого класса .


Иметь уникальную таблицу для каждого типа узла.

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

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

Смесь C & B будет ходом событий, которые я беру. Я хотел бы иметь уникальные хранимые процедуры для добавления / редактирования для каждого типа узла (например, Insert_Update_NodeType), а также заставить класс выполнять проверку данных перед сохранением данных.

Лично я всегда настаиваю на том, чтобы код целостности данных помещался в саму таблицу с помощью триггера или ограничения проверки. Причина в том, что вы не можете гарантировать, что только пользовательский интерфейс будет обновлять вставки или удалять записи. Вы также не можете гарантировать, что кто-то может не написать второй sp, чтобы обойти ограничения в оригинальном sp, не понимая фактических правил целостности данных, или даже написать это, потому что он или она не знают о существовании sp с правилами. На таблицы часто влияют пакеты DTS или служб SSIS, динамические запросы из пользовательского интерфейса или через анализатор запросов или окно запросов, или даже запланированные задания, выполняющие код. Если вы не поместите код целостности данных на уровне таблицы, рано или поздно ваши данные не будут иметь целостности.

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

Целостность данных

Читайте также:

  1. C. Этап 3. Подготовка данных
  2. Corporate Information Factory, Корпоративное хранилище данных
  3. D. Очистка данных
  4. Data Mart — Витрины данных
  5. Data Mining (DM) — интеллектуальный анализ данных
  6. Data Warehouse – хранилище данных — ХД — систем обработки данных
  7. I. Создание баз данных
  8. If используется для разветвления процесса обработки данных на два направления.
  9. L ТРИПС регулирует вопросы правовой охраны произведений, созданных с применением новых технологий, а также новейшие способы использования произведений.
  10. OLAP-системы оперативной аналитической обработки данных
  11. XXIV. 1. Понятие программы для ЭВМ, базы данных и топологии интегральной микросхемы и основные правила их охраны
  12. XXVI. 3. Использование программ для ЭВМ, баз данных и топологий ИМС третьими лицами
Илон Маск рекомендует:  ul в HTML

Обеспечение целостности данных и хранимые процедуры

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

В таблицах SQL Server может быть определен ряд свойств различного типа, обеспечивающих целостность данных. К ним относятся типы данных, определения NOT NULL и DEFAULT, свойства IDENTITY, ограничения, правила, триггеры и индексы.

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

Обеспечение целостности данных гарантирует их качество. Предположим, что мы создали в базе данных таблицу Persons. Значение столбца PersonId должно уникально идентифицировать каждую персону, сведения о которой занесены в таблицу. Таким образом, если значение PersonId некоей персоны равно 834, то ни для какой другой персоны это значение не может быть таким же. Далее предположим, что имеется столбец PersonRating, в котором определяется рейтинг персон — в диапазоне от 1 до 10. В этом случае столбец PersonRating не должен допускать ввода ни числа 11, ни каких-либо иных значений, кроме чисел из интервала от 1 до 10. В обоих случаях для обеспечения целостности данных следует применять один из методов, поддерживаемых SQL Server.

Среди методов SQL Server, предназначенных для обеспечения целостности данных, — определения NOT NULL и DEFAULT, свойства IDENTITY, ограничения, правила, триггеры и индексы. Некоторые из них уже упоминались, здесь же приводится их краткое описание. Некоторые свойства таблицы, например определения NOT NULL и DEFAULT, иногда считают ограничениями особого типа. Однако в соответствии с задачами нашего курса, они рассматриваются отдельно от ограничений.

Тип данных — это атрибут, который определяет тип (символьный, целочисленный, двоичный и т. д.) данных, которые могут храниться в поле, параметре или переменной. SQL Server поддерживает несколько системных типов данных, на основе которых можно создавать пользовательские типы данных. Системные типы данных определяют все типы данных, которые можно применять в SQL Server. Типы обеспечивают целостность данных, поскольку вводимые данные должны соответствовать типу, заданному для объекта.

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

Задавая возможность ввода в поле пустых значений, мы определяем, могут ли в этом столбце таблицы храниться пустые значения. Пустое значение отличается от нуля, пробела или символьной строки нулевой длины, например «». Пустое значение означает, что информация не введена, то есть значение не известно или не определено. Возможность ввода в столбец пустых значений задается при определении этого столбца во время создания таблицы или ее модификации. Из-за сложностей, связанных с обработкой пустых значений в SQL Server, в определении столбцов как допускающих, так и не допускающих ввода пустых значений всегда следует использовать ключевые слова NULL или NOT NULL. Если столбец допускает пустые значения, используется ключевое слово NULL, если нет — NOT NULL.

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

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

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

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

Сначала следует создать правило с помощью оператора CREATE RULE. После этого при помощи системной хранимой процедуры sp_bindrule его привязывают к столбцу или пользовательскому типу данных. Подробнее об использовании CREATE RULE или sp_bindrule — в справочнике по Transact-SQL в SQL Server Books Online.

Триггеры — это особый класс хранимых процедур, автоматически запускаемых при выполнении операторов UPDATE, INSERT или DELETE для таблицы или представления. Триггеры представляют собой мощный инструмент, применяемый для автоматической реализации бизнес-логики при модификации данных. Триггеры способны расширить логику проверки целостности, реализуемую ограничениями, умолчаниями и правилами SQL Server (хотя во всех случаях, когда ограничения способны обеспечить необходимую функциональность, следует использовать их вместо триггеров).

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

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

SQL Server поддерживает четыре типа целостности данных: сущностную, доменную и ссылочную целостность, а также целостность, определяемую пользователем.

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

Этот тип целостности определяет строку таблицы как уникальный экземпляр некоторой сущности. Сущностная целостность обеспечивает целостность столбца с идентификатором или первичного ключа таблицы (посредством индексов, ограничений UNIQUE, PRIMARY KEY или свойств IDENTITY).

Этот тип целостности гарантирует наличие в некотором столбце только допустимых значений. Можно обеспечивать доменную целостность, ограничивая тип (посредством типов данных), формат (с помощью ограничений CHECK и правил) или диапазон допустимых значений (с помощью ограничений FOREIGN KEY и CHECK, определений DEFAULT, определений NOT NULL и правил).

Этот тип целостности обеспечивает сохранность связей между таблицами при добавлении или удалении записей. В SQL Server ссылочная целостность основана на связях между внешними и первичными ключами или между внешними и уникальными ключами (через ограничения FOREIGN KEY и CHECK). Ссылочная целостность гарантирует согласованность значений ключа в связанных таблицах. Подобная согласованность требует отсутствия ссылок на несуществующие значения и согласованного изменения ссылок на ключ во всей базе данных при изменении самого ключа.

При обеспечении ссылочной целостности SQL Server предотвращает следующие действия пользователей:

• добавление записей в связанную таблицу, если нет необходимой записи в главной таблице;

• изменение значений в главной таблице, в результате которого в связанной таблице останутся «зависшие» записи;

• удаление записей из главной таблицы при наличии связанных записей во внешней таблице.

Конструкцию FROM необходимо помещать в каждом операторе SELECT, который извлекает данные из таблиц или представлений. Эта конструкция позволяет задать список таблиц и представлений, на столбцы которых ссылаются список выбора и конструкция WHERE. Этим таблицам и представлениям могут быть присвоены псевдонимы в конструкции AS. Конструкция FROM, кроме того, позволяет соединять таблицы, задавая условия соединения в конструкции JOIN.

Конструкция FROM представляет собой список имен таблиц, представлений и конструкций JOIN, разделенных запятыми. В следующем примере в операторе SELECT конструкция FROM задает таблицу Persons:

SELECT * FROM Persons

Конструкцию FROM также используют и для определения соединений между двумя таблицами или представлениями. О соединениях более подробно рассказано в следующем разделе.

Конструкции WHERE, GROUP BY и HAVING

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

SELECT PersonId, LastName, FirstName

WHERE BirthPlace = ‘Krasnoyarsk’

Конструкция HAVING, как правило (но не обязательно), используется вместе с конструкцией GROUP BY. Конструкция HAVING задает дополнительные фильтры, которые применяются после завершения фильтрации, определяемой конструкцией WHERE. В следующем сценарии в операторе SELECT использованы конструкции WHERE, GROUP BY и HAVING:

SELECT OrdD1OrderId AS OrderId,

SUM(OrdD1.Quantity) AS «Units Sold»,

SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue

FROM [Order Details] AS OrdD1

WHERE OrdD1OrderId IN (SELECT DISTINCT OrdD2.OrderId

FROM [Order Details] AS OrdD2

WHERE OrdD2.UnitPrice > $1000)

GROUP BY OrdD1.OrderId

HAVING SUM(OrdD1.Quantity) > 50

Здесь конструкция WHERE возвращает заказы, стоимость которых больше $1000, а далее конструкция HAVING ограничивает результат, отбирая заказы на более чем 50 единиц товара. Конструкция GROUP BY ограничивает строки для каждого конкретного значения поля OrderId.

Конструкция GROUP BY

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

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

Если в операторе SELECT имеется конструкция GROUP BY, SQL Server налагает ограничения на элементы списка выбора. В списке выбора могут быть лишь те группирующие столбцы и выражения, которые возвращают только одно значение для каждого значения группирующих столбцов, например агрегатные функции (векторные агрегаты), одним из параметров которых является имя столбца.

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

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

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

Понимание верной последовательности, в которой применяются конструкции WHERE, GROUP BY и HAVING, помогает создавать достаточно эффективные запросы:

• конструкция WHERE фильтрует строки, которые являются результатом операций, заданных в конструкции FROM;


• выходная информация конструкции WHERE группируется с помощью конструкции GROUP BY;

• строки сгруппированного результата фильтруются средствами конструкции HAVING.

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

Конструкция ORDER BY

Конструкция ORDER BY сортирует результат запроса по одному или нескольким полям. Сортировка может быть как по возрастанию (ASC), так и по убыванию (DESC). Если не задан ни один из видов сортировки, по умолчанию предполагается ASC. Если в конструкции ORDER BY названо несколько столбцов, выполняется вложенная сортировка.

Следующий оператор сортирует строки таблицы Persons сначала по фамилии (по убыванию), затем по имени (по возрастанию, для каждой персоны) и, наконец, по возрасту (также по возрастанию, поскольку не задан параметр DESC).

SELECT PersonId, LastName, FirstName, Age

ORDER BY LastName DESC, FirstName, Age

Пакеты, хранимые процедуры и триггеры

Пакет — это группа из одного или нескольких операторов Transact-SQL, которые приложение одновременно посылает на SQL Server для исполнения. SQL Server компилирует операторы пакета в единую исполнимую единицу (план исполненияExecution Plan). После этого по очереди выполняются операторы этого плана.

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

У ошибки периода выполнения, например арифметического переполнения или нарушения ограничения, возможно одно из следующих последствий:

• большинство ошибок периода выполнения останавливают исполнение текущего и последующих операторов пакета;

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

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

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

При обработке пакетов действуют следующие правила:

• операторы CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER и CREATE VIEW не могут соседствовать в пакетах с другими операторами. Пакет должен начинаться с оператора CREATE. Все следующие за ним операторы будут интерпретированы как часть определения, созданного первым оператором CREATE;

• в пределах одного и того же пакета нельзя модифицировать таблицу и обращаться к новым столбцам;

• если оператор EXECUTE — первый оператор пакета, ключевое слово EXECUTE не требуется. Но оно необходимо, когда оператор EXECUTE не является первым оператором пакета.

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

Для того чтобы задать пакет, имеется несколько способов.

• Все операторы SQL, которые приложение отправляет на сервер как единицу исполнения, составляют единый пакет и генерируют один план исполнения.

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

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

• Строка, исполняемая системной хранимой процедурой sp_executesql, — это пакет, при компиляции которого получается один план исполнения.

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

Если оператор пакета вызывает триггер, то план исполнения триггера выполняется отдельно от плана исполнения исходного пакета.

Например, пакет, который содержит следующие четыре оператора, генерирует пять планов исполнения:

• оператор EXECUTE, исполняющий хранимую процедуру;

• вызов процедуры sp_executesql для обработки строки;

• оператор EXECUTE, обрабатывающий строку;

• оператор UPDATE, ссылающийся на таблицу, у которой есть триггер на обновление.

EXEC sp_executesql N’SELECT * FROM AdventureWorks.HumanResources.Employee

SET PersonName = ‘kuku’

Хранимая процедура — это группа операторов Transact-SQL, которая компилируется один раз и после этого может выполняться многократно. Такая функциональность повышает производительность, поскольку отпадает необходимость в перекомпиляции операторов Transact-SQL.

Триггер — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно. При создании триггера определяются условия его исполнения: триггер должен быть исполнен при определенной модификации данных в заданной таблице или столбце.

Операторы CREATE PROCEDURE и CREATE TRIGGER не могут располагаться в нескольких пакетах. Другими словами, хранимая процедура или триггер всегда создаются в одном пакете и компилируются в план исполнения.

SQL Server хранит только исходный текст хранимых процедур и триггеров. Когда хранимая процедура или триггер исполняется первый раз, исходный текст компилируется в план исполнения. Если до того, как план исполнения устареет и будет удален из памяти, хранимая процедура или триггер исполняется снова, реляционный механизм обнаруживает существующий план и использует его повторно. Если план устарел и удален из памяти, создается новый план исполнения. Этот процесс напоминает обработку SQL Server всех операторов SQL. Увеличение производительности при применении хранимых процедур и триггеров объясняется постоянством их SQL-операторов, что позволяет SQL Server использовать для них существующие планы исполнения.

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

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

Дата добавления: 2014-01-11 ; Просмотров: 1919 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Правила целостности данных

Главная особенность SQL-технологий наличие у сервера СУБД специальных средств контроля целостности данных, не зависящих от клиентских программ и привязанных непосредственно к таблицам. Т.е. принципиально не важно, каким образом осуществляется доступ к базе данных: через SQL-консоль, через ODBC-драйвера из приложения Windows, через WWW-connector из Internet-браузера или через DBI-интерфейс Perl. В любом из этих случаев, за контролем целостности данных следит сервер, и при нарушении правил целостности данных сервер известит клиента об ошибке.

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

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

SQL-серверы, как правило, поддерживают следующие ограничители.

NOT NULL — проверка на непустое значение. NULL — специальное понятие в СУБД, которое означает «пусто». «Пусто» и «0(ноль)» не равны друг другу!

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

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

SQL-технология позволяет на уровне столбца задавать домены значений, т.е. строго определенные наборы или диапазоны значений, для помещаемых в столбец данных. В частности можно реализовывать ограничения ссылочной целостности (referential integrity constraint) и проверки фиксированного условия. Ограничение ссылочной целостности не позволяет значениям из столбца одной таблицы принимать значения кроме как из присутствующих в столбце другой таблицы. Это делается при помощи ограничителей FOREIGN KEY (внешний ключ) и REFERENCES (указатель ссылки). Таблица, содержащая FOREIGN KEY, считается родительской таблицей. Таблица, содержащая REFERENCES, считается дочерней таблицей. Внешний ключ и указатель ссылки могут находиться в одной таблице, т.е. родительская таблица одновременно является дочерней.

FOREIGN KEY — внешний ключ. Назначает столбец или комбинацию столбцов в текущей (родительской) таблице в качестве внешнего ключа для ссылки из других таблиц.

REFERENCES — указатель ссылки (или родительский ключ). Указывает на столбец (комбинацию столбцов) в родительской таблице, ограничивающую значения в текущей (дочерней) таблице.

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

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

CHECK — проверка фиксированного условия. В данном ограничителе явно указывается условие, которое должно выполняться для вставляемого или модифицируемого значения в столбце. Например: check (user in ‘ALEX’,’JUSTAS’) — в столбце user могут содержаться только значения ‘ALEX’ и ‘JUSTAS’, попытка вставки значения ‘SHTIRLITZ’ будет интерпретирована как ошибочная , check (user_salary between 1000 and 5000) — столбец user_salary может принимать целочисленные значения в диапазоне от 1000 до 5000 и т.д. При формировании условий с некоторыми ограничениями могут использоваться функции, например check (user = upper(user)), в данном случае имя пользователя должно вводиться только в верхнем регистре. Есть и ограничения, например, CHECK не может содержать подзапросы (SELECT).

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

Триггеры — это сохраненная откомпилированная процедура, которая связана с определенной таблицей. Триггеры, в отличие от ограничителей, могут выполнять сколь угодно сложные манипуляции над данными. Помимо операций модификации и вставки, триггеры могут срабатывать и при удалении данных из таблицы. Можно также задавать порядок срабатывания триггера относительно операции, т.е. выполниться ли триггер перед операцией вставки/модификации/удаления значения из столбца (или всей строки) или непосредственно после такой операции.

Некоторые типовые применения триггеров:

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

· Генерация значений в столбцах на основе значений в других столбцах при вставке/модификации строки данных.

· Манипуляции над зависимыми таблицами в особенности, если они находятся на других узлах распределенной базы данных, чего нельзя сделать при помощи ограничителей.


В случае необходимости триггеры можно запрещать, а затем разрешать. Запрещение триггеров применяется обычно при массовых загрузках данных в таблицы извне, с целью уменьшения времени загрузки. Понятие триггера как выполнение кода по событию в том же Oracle используется весьма широко. В частности, оно является основным при разработке клиентских программ при помощи SQL*Forms. Триггеры пишутся на процедурных расширениях SQL.

Дата добавления: 2020-02-27 ; просмотров: 396 ; ЗАКАЗАТЬ НАПИСАНИЕ РАБОТЫ

Реализация ограничений целостности реляционной БД средствами языка SQL.

Реализация ограничений целостности реляционной базы данных

Целостность данных – это поддержка точности и корректности данных, хранящихся в БД.
Правила поддержки ограничений целостности и надежности определяются используемой моделью данных и предметной областью.
Правила должны быть описаны в физическом проекте БД и реализованы либо средствами СУБД, либо приложения.
Поддержка целостности реляционной БД рассматривается в 3-х аспектах.

Илон Маск рекомендует:  Что такое код socket_iovec_fetch

1 Целостность таблицы. Обязательно должны поддерживаться:
— уникальность строк таблицы. Должен быть определен первичный ключ таблицы, и значение его должно быть определено;
— все уникальные (потенциальные) ключи, выявленные в ходе анализа предметной области.
Эти ограничения реализуются в командах создания и модификации таблиц. Например, в языке SQL это команды Create Table, Alter Table. В этих командах для описания полей — первичных ключей используется конструкция primary key, для описания полей – уникальных ключей конструкция unique, обязательность значений полей задается конструкцией not null.

2 Декларативные ограничения данных. Так называют ограничения реляционной базы данных, объявленные предметной областью и выявленные в ходе её анализа. Задача проектировщика БД — адекватно отобразить их в БД.
Самые распространенные ограничения предметной области – это ограничения на свойства объекта предметной области, далее атрибута отношения или поля таблицы:
— обязательность значения поля;
— тип, длина, диапазон значения поля (например, значение должно быть целым и положительным), вхождение значения в заданный список и т.п.
Такие ограничения рекомендуется задавать на уровне домена в командах Create Domain, Alter Domain. Также они могут быть заданы в командах создания и модификации таблиц — Create Table, Alter Table при описании поля таблицы.

Эти ограничения также реализуются в командах создания и модификации таблиц (Create table, Alter table) при описании поля таблицы:
Value and |
Value [Not] In ( [, ,]) |
Value Is [Not] Null |
Value [Not] Like [Escape ] |
Value [Not] Containing |
Value [Not] Starting [With] |

3 Ссылочная целостность. Каждая таблица проектируемой БД должна быть связана с другими посредством соответствующих первичных и внешних ключей, т.е. быть либо родительской (главной) по отношению к другим таблицам, либо дочерней (подчиненной), либо той и другой для разного уровня связей.
Назначение внешнего ключа — связывать каждую строку дочерней таблицы с соответствующей строкой родительской таблицы. Значение внешнего ключа может иметь и пустое значение (Null), если он реализует необязательную связь, выявленную в предметной области.
В качестве значения внешнего ключа может выступать значение и любого уникального (потенциального) ключа. Чтобы в физическом проекте реализовать поддержку ссылочной целостности, необходимо знать ситуации, когда она может быть нарушена:
1 группа ситуаций:
а) добавление строки в дочернюю таблицу
б) изменение значения ВК дочерней таблицы (перенос связи на другой объект)
В этом случае значение атрибута внешнего ключа новой строки должно соответствовать конкретному значению, присутствующему в одной из строк родительской таблицы, либо должно быть равно пустому значению (Null). В противном случае целостность будет нарушена;
2 группа ситуаций:
а) удаление строки из родительской таблицы. Ссылочная целостность будет нарушена, если в дочернем отношении существуют строки, ссылающиеся на удаляемую в родительской таблице строку. В этом случае может быть использована одна из следующих стратегий:
б) обновление первичного ключа в строке родительской таблицы. Редкая ситуация, рассматриваются все возможные стратегии, как и в случае 3).
1) No Action – удаление строки из родительской таблицы запрещено, если в дочерней таблице есть хотя бы одна ссылающаяся на неё строка;
2) Cascade (каскадное взаимодействие) – при удалении строки из родительской таблицы автоматически удаляются все ссылающиеся на нее строки дочерней таблицы. Если при этом любая из удаляемых строк дочерней таблицы выступает в качестве родительской для дочерних таблиц следующего уровня, то операция удаления применяется ко всем строкам дочерней таблицы этой связи и т.д. – удаление распространяется каскадно на все дочерние таблицы;
3) Set Null – при удалении строки из родительской таблицы во всех ссылающихся на неё строках дочерней таблицы в атрибутах внешнего ключа записывается пустое значение (Null);
4) Set Default – при удалении строки родительской таблицы значение атрибутов внешнего ключа ссылающейся на неё строки дочерней таблицы автоматически замещаются значениями по умолчанию, определенными при создании дочерней таблицы;
5) No Check – при удалении строки из родительской таблицы никаких действий по сохранению ссылочной целостности не предпринимается;
Связи между таблицами (ссылочная целостность) могут быть заданы либо путем явного описания внешних ключей в структурах таблиц (что является более предпочтительным, как и любое другое явное описание), либо ссылочная целостность может поддерживаться с помощью триггеров.
Например, для СУБД InterBase, если связь между двумя таблицами определена в команде Create Table при помощи конструкции foreign key, задающей явно поле – внешний ключ, ссылающийся на соответствующее поле — первичный ключ (конструкция references), то СУБД запрещает изменять значение первичного ключа, если на нее ссылаются какая-либо строка из дочерней таблицы, и удалять запись в родительской таблице, если на неё есть ссылающаяся запись из дочерней таблицы. Таким образом, связь, описанная в команде Create Table, блокирует каскадные изменения и удаления в родительской и дочерней таблицах, т.е. по умолчанию СУБД InterBase использует стратегию No Action.

Реализация средствами языка SQL
Команды Create Table, Alter Table для дочерней таблицы.
Конструкции
Foreign Key References
Если явно задается внешний ключ, то разные СУБД могут использовать разные стратегии поддержки ссылочной целостности.

CHECK CONSTRAINT в MS SQL — Грабли по которым мы прошлись

Данная статья будет про то, как одна дружная команда веб разработчиков, не имея в своём составе опытного SQL разработчика, добавила Check Constraint в таблицу и прошлась по нескольким простым, но не сразу очевидным граблям. Будут разобраны особенности синтаксиса T-SQL, а также нюансы работы ограничений (СONSTRAINT’ов), не зная которые, можно потратить не мало времени на попытки понять, почему что-то работает не так. Так же будет затронута особенность работы SSDT, а именно как генерируется миграционный скрипт, при необходимости добавить или изменить ограничения (CONSTRAINT’ы).

Дабы читатель поскорей понял, стоит читать статью или нет, я сначала рассмотрю абстрактную задачу, по ходу решения которой будут заданы вопросы «А почему так?». Если вы сразу будете знать ответ, то смело бросайте чтение и переходите к следующей статье.

Разработаем гарем?

«Гарем» — система, которая будет позволять вести учёт людей в «храме любви».
Для простоты разработки примем следующее:

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

Для хранения людей создаётся таблица Persons:

В последний момент, приходит озарение, что на уровне схемы базы мы не гарантируем существование только одного мужчины в гареме. Решаем это исправить путём добавления проверочного ограничения (check constraint):

основанного на скалярной пользовательской функции (scalar-valued Function):

«А почему так?» №1.

При попытке вставить абсолютно валидные данные (как женщин, так и мужчин), понимаем, что мы всё поломали. Insert валится со следующей ошибкой:

«А почему так?» №2.

После того как побороли проблему вставки данных, решаем задеплоиться в QA окружение. Создаём миграционный скрипт при помощи SSDT, быстро просматриваем его. Находим какую-то не очень понятную строку (выделена красной рамкой).

Из комментария в инструкции PRINT кажется, что это запуск проверки ограничения на уже существующих строках. Но при создании ограничения мы же указали, что существующие строки проверять не нужно («Check Existing Data On Creation Or Re-Enabling» был установлен в “No”). Поэтому начинаем гуглить и находим «полезный» пост. Прочитав ответ и все комментарии к нему, обретаем глубокую уверенность, что эта инструкция включает проверку при вставке новых строк, а не валидирует существующие, т. е. нам обязательно нужно оставить эту строку, иначе ограничение вообще никогда не будет проверяться.
С гордостью за проделанную работу, отправляем скрипт, ждёмс… Спустя Х часов приходит отчёт, что наш миграционный скрипт успешно провалился. Смотрим отчёт.

Понимаем, что именно подозрительная инструкция провалила миграцию.

«А почему так?» №1 – Объяснение.

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

«А почему так?» №2 – Объяснение.

Тут всё оказалось не столь прозрачно. Сначала пришлось всё-таки разобраться в истинном назначении упавшей инструкции. И, к превеликому нашему удивлению, мы поняли, что она делает именно то, что сказано в комментарии, а не то, что описано в найденном «полезном» посте (разбор синтаксиса будет ниже).
Узнав это, было логично предположить, что при создании миграционного скрипта была выбрана база, в которой на CK_Persons значение «Check Existing Data On Creation Or Re-Enabling» было “Yes”, а не “No”. Но эта теория провалилась. Меняя это значение и генерируя новый скрипт, стало понятно, что SSDT, вообще игнорируют это значение. Начали грешить на наличие бага в SSDT.
Очередной этап поисков навёл нас на следующий пост, из которого мы уже поняли, что это «фича, а не баг».
Согласно дизайна SSDT, при создании скрипта всегда создаётся ограничение, которое включено, т.е. проверяется для всех будущих INSERT/UPDATE. За это отвечает первая инструкция ALTER в нашем миграционном скрипте.
Вторая же инструкция ALTER (выделена красной рамкой) отвечает за валидацию существующих данных и является опциональной. За то будет ли эта инструкция добавлена в миграционный скрип, отвечает специальная опция генерации скрипта:

Включив её, мы для каждого нового миграционного скрипта активируем валидацию существующих данных, т.е. в него будет вставлена опциональная инструкция (второй ALTER). Иначе, инструкция попросту отсутствует и на существующих данных проверка не выполняется. Как это не прискорбно получается, но SSDT генерирует миграционный скрипт по принципу всё или ничего. Можно либо для всех вновь добавляемых ограничений включить проверку на существующих данных, либо для всех её пропустить. Для более тонкой настройки поведения придётся править скрипт вручную.

Ограничения (Constraints) в MS SQL

Как уже говорилось выше, в данной статье осталось разобраться с премудростями синтаксиса создания и обновления проверочных ограничений. Но прежде чем мы приступим, давайте для полноты картины вспомним немного общей информации об ограничениях в MS SQL Server.
Ограничения – механизм, который позволяет задавать набор правил, направленных на поддержание целостности данных. Правила могут быть заданы как на уровне колонки в таблице, так и на уровне всей таблицы.
MS SQL Server поддерживает следующие виды ограничений:

  • NULL / NOT NULL ограничение – задаётся на уровне какого-то столбца и определяет, может ли хранится значение NULL в колонке.
  • UNIQUE ограничение – позволяет обеспечить уникальность значений в одном или нескольких столбцах.
  • PRIMARY KEY ограничение – практически тоже самое, что и UNIQUE ограничение, но в отличие от него, PRIMARY KEY не позволяет хранить NULL.
  • CHECK ограничение – позволяет задать некое логическое условие, которое должно быть истинным (TRUE) при вставке или обновлении данных в таблице. Может быть задано как на уровне одного столбца, так и на уровне таблицы.
  • FOREIGN KEY ограничение – позволяет обеспечить ссылочную связность двух таблиц. При вставке значения в колонку (или колонки) с FOREIGN KEY ограничением, будет производится проверка на наличие такого же значения в таблице, на которую указывает FOREIGN KEY. Если значения нет, то обновление или вставка строки завершается с ошибкой. Исключением может быть только значение NULL, если на колонке не задано ограничение NOT NULL. Кроме того, ссылаться можно только на колонку с уникальными значениями, т.е. с UNIQUE или PRIMARY KEY ограничением. Так же можно задать поведение, на случай обновления или удаления строки, в «отцовской» таблице:
    • NO ACTION – отцовскую таблицу запрещено менять
    • CASCADE – подчинённые строки будут обновлены или удалены, в зависимости от выполняемого действием над отцовской таблицей
    • SET NULL – значение в подчинённой таблице будет установлено в NULL
    • SET DEFAULT — значение в подчинённой таблице будет установлено в значение по умолчанию.

Теперь немного подробней о CHECK CONSTRAINT’ах. Рассмотрим ограничение, которое было упомянуто выше. Ниже представлено окно свойств этого ограничения в Management Studio:

Основными свойствами являются:

  • Expression – любое допустимое T-SQL выражение в котором можно ссылаться на значения в проверяемой строке по имени столбцов
  • Name – имя, уникально идентифицирующее ограничение в пределах базы данных
  • Check Existing Data On Creation Or Re-Enabling – если ограничение создаётся на уже существующей таблице, то это значение “No” позволяет не пропустить валидацию существующих строк; в виду того, что существующую проверку можно временно выключить, то данное свойство так же определяет будет ли проводиться валидация имеющихся строк при включении ограничения.
  • Enforce For INSERTs And UPDATEs – включает (Yes) или выключает (No) ограничение
  • Enforce For Replication – позволяет пропустить проверку при вставке или обновлении строк агентом репликации

Вся эта информация доступна нам так же из системного представления (view) sys.check_constraints. Оно содержит по одной строке для каждого CHECK CONSTRAINT в базе данных. Мы его иногда используем в миграционных скриптах, когда нужно убедится в существовании или в отсутствии какого-либо ограничения.

Примеры использования sys.check_constraints

Можно получить ответ в более привычном формате, воспользовавшись оператором UNPIVOT:

Особенности работы CHECK CONSTRAINT:
  • Срабатывает только при INSERT и UPDATE операциях, при выполнении DELETE условие не проверяется
  • Если проверочное условие равно NULL, то считается, что CHECK CONSTRAINT не нарушен

Синтаксис CHECK CONSTRAINT

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

“…ADD CONSTRAINT. ”
(создание)
“ALTER…CONSTRAINT…”
(изменение)
Name +
Expression +
Check Existing Data On Creation Or Re-Enabling + +
Enforce For INSERTs And UPDATEs +
Enforce For Replication +

Добавление нового CHECK CONSTRAINT

  • В квадратных скобках «[ ]» – указываются опциональные конструкции и могут быть опущены из конечного выражения
  • В фигурных скобках «< >» — указывается список возможных конструкций, из которых необходимо выбрать одну
  • Вертикальная черта «|» — отделяет элементы в фигурных скобках, среди которых необходимо выбрать единственный элемент

Опциональные секции:

  1. [ WITH < CHECK | NOCHECK >] – в случае отсутствия применяется значение WITH CHECK
  2. [ NOT FOR REPLICATION ] – если конструкция указана, то ограничение не проверяется при вставке или обновлении данных в момент репликации; если конструкция пропущена –ограничение проверяется.

Примечание: шаблон приведён для случая создания ограничения на существующей таблице. Также можно создать ограничение в момент создания таблицы, тогда команда будет начинаться со слова CREATE и до слова WITH будет идти описание колонок таблицы.

Примеры команд будут приведены для простейшей таблицы Employees, которая выглядит следующим образом:

Изменение существующего CHECK CONSTRAINT

Для обновления существующего проверочного ограничения используется конструкция ALTER TABLE. Для изменения доступны только следующие свойства:

  • Check Existing Data On Creation Or Re-Enabling
  • Enforce For INSERTs And UPDATEs

Опциональные секции:

  1. [ WITH < CHECK | NOCHECK >] – в случае отсутствия применяется значение WITH NOCHECK
  2. [, …n] – позволяет задать имя более чем одного ограничения, к которым будут применены изменения; использование слова ALL изменения применятся ко всем проверочным ограничениям на таблице

Примечание 1: хоть имя и нельзя переименовать при помощи синтаксиса ALTER TABLE, это всё же возможно сделать, используя системную хранимую процедуру sp_rename.
Примечание 2: при необходимости изменить свойства «Expression» или «Enforce For Replication», необходимо сначала удалить существующее ограничение, а потом заново его создать с нужными значениями этих свойств.

Недокументированное поведение

Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте msdn.
Что бы это увидеть, необходимо рассмотреть все возможные комбинации состояний в сочетании со всеми возможными вариантами команд. Тогда будет видно, что в 5-ти случаях получаемое значение свойства «Check Existing Data» не соответствует ожиданиям.

Состояние до выполнения команды T-SQL команда Состояние после выполнения команды
Check Existing Data Enforce For INSERTs And UPDATEs Check Existing Data Enforce For INSERTs And UPDATEs
No No NOCHECK No No
No Yes NOCHECK No No
Yes Yes NOCHECK No No
No No CHECK No Yes
No Yes CHECK No Yes
Yes Yes CHECK Yes* Yes
No No WITH NOCHECK NOCHECK No No
No Yes WITH NOCHECK NOCHECK No No
Yes Yes WITH NOCHECK NOCHECK No No
No No WITH NOCHECK CHECK No Yes
No Yes WITH NOCHECK CHECK No Yes
Yes Yes WITH NOCHECK CHECK Yes* Yes
No No WITH CHECK NOCHECK No** No
No Yes WITH CHECK NOCHECK No** No
Yes Yes WITH CHECK NOCHECK No** No
No No WITH CHECK CHECK Yes Yes
No Yes WITH CHECK CHECK Yes Yes
Yes Yes WITH CHECK CHECK Yes Yes

(*) Значение свойства «Check Existing Data» может быть переведено из значения «Yes» в значение «No», только если текущее значение свойства «Enforce For INSERTs And UPDATEs» отличается от заданного в команде.

(**) «Check Existing Data» может быть «Yes», только если ограничение включено (Enforce For INSERTs And UPDATEs = “Yes”). Т. е. в команде WITH CHECK NOCHECK часть WITH CHECK будет проигнорирована и «Check Existing Data» не будет установлено в «Yes». Это так же объясняет почему в качестве начальных состояний есть только 3 варианта для каждой команды (а не 4).

Удаление существующего CHECK CONSTRAINT


Команда очень проста и не требует дополнительных объяснений. Ещё шаблон:

Глава 19. ПОДДЕРЖКА ЦЕЛОСТНОСТИ ВАШИХ ДАННЫХ

Глава 19. ПОДДЕРЖКА ЦЕЛОСТНОСТИ ВАШИХ ДАННЫХ

РАНЕЕ В ЭТОЙ КНИГЕ, МЫ УКАЗЫВАЛИ НА ОПРЕДЕЛЕННЫЕ связи которые существуют между некоторыми полями наших типовых таблиц. Поле snum таблицы Заказчиков, например, соответствует полю snum в таблице Продавцов и таблице Порядков. Поле cnum таблицы Заказчиков также соответствует полю cnum таблицы Порядков. Мы назвали этот тип связи — справочной целостностью; и в ходе обсуждения, вы видели как ее можно использовать.

В этой главе, вы будете исследовать справочную целостность более подробно и выясним все относительно ограничений которые вы можете использовать чтобы ее поддерживать. Вы также увидете, как предписывается это ограничение когда вы используете команды модификации DML. Поскольку справочная целостность включает в себя связь полей или групп полей, часто в разных таблицах, это действие может быть несколько сложнее чем другие ограничения. По этой причине, хорошо иметь с ней полное знакомство, даже если вы не планируете создавать таблицы. Ваши команды модификации могут стать эффективнее с помощью ограничения справочной целостности (как и с помощью других ограничений, но ограничение справочной целостности может воздействовать на другие таблицы кроме тех в которых оно определено), а определенные функции запроса, такие как обьединения, являются многократно структурированы в терминах связей справочной целостности (как подчеркивалось в Главе 8 ).

ВНЕШНИЙ КЛЮЧ И РОДИТЕЛЬСКИЙ КЛЮЧ

Когда все значения в одном поле таблицы представлены в поле другой таблицы, мы говорим что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей. Например, каждый из заказчиков в таблице Заказчиков имеет поле snum которое указывает на продавца назначенного в таблице Продавцов. Для каждого порядка в таблице Порядков, имеется один и только этот продавец и один и только этот заказчик. Это отображается с помощью полей snum и cnum в таблице Порядков.

Когда одно поле в таблице ссылается на другое, оно называется — внешним ключом; а поле на которое оно ссылается, называется — родительским ключом. Так что поле snum таблицы Заказчиков — это внешний ключ, а поле snum на которое оно ссылается в таблице Продавцов — это родительский ключ.

Аналогично, поля cnum и snum таблицы Порядков — это внешние ключи которые ссылаются к их родительским ключам с именами в таблице Заказчиков и таблице Продавцов. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми, это — только соглашение которому мы следуем чтобы делать соединение более понятным.

МНОГО-СТОЛБЦОВЫЕ ВНЕШНИЕ КЛЮЧИ

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

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

СМЫСЛ ВНЕШНЕГО И РОДИТЕЛЬСКОГО КЛЮЧЕЙ

Когда поле — является внешним ключом, оно определеным образом связано с таблицей на которую он ссылается. Вы, фактически, говорите — «каждое значение в этом поле (внешнем ключе ) непосредственно привязано к значению в другом поле (родительском ключе ).» Каждое значение (каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это так, то фактически ваша система, как говорится, будет в состоянии справочной целостности.

Вы можете увидеть это на примере. Внешний ключ snum в таблице Заказчиков имеет значение 1001 для строк Hoffman и Clemens. Предположим что мы имели две строки в таблице Продавцов со значением в поле snum=1001. Как мы узнаем, к которому из двух продавцов были назначены заказчики Hoffman и Clemens ? Аналогично, если нет никаких таких строк в таблице Продавцов, мы получим Hoffman и Clemens назначенными к продавцу которого не существует!

Понятно, что каждое значение во внешнем ключе должно быть представлено один, и только один раз, в родительском ключе.

Фактически, данное значение внешнего ключа может ссылаться только к одному значению родительского ключа не предполагая обратной возможности: т.е. любое число внешних ключей может ссылать к единственному значению родительского ключа. Вы можете увидеть это в типовых таблицах наших примеров. И Hoffman и Clemens назначены к Peel, так что оба их значения внешнего ключа совпадают с одним и тем же родительским ключом, что очень хорошо. Значение внешнего ключа должно ссылаться только к одному значению родительского ключа, зато значение родительского ключа может ссылаться с помощью любого колличества значений внешнего ключа. В качестве иллюстрации, значения внешнего ключа из таблицы Заказчиков, совпавшие с их родительским ключом в Продавцов таблице, показываются на Рисунке 19.1. Для удобства мы не учитывали поля не относящиеся к этому примеру.

ОГРАНИЧЕНИЕ FOREIGN KEY

SQL поддерживает справочную целостность с ограничением FOREIGN KEY.

Хотя ограничение FOREIGN KEY — это новая особенность в SQL, оно еще не обеспечивает его универсальности. Кроме того, некоторые его реализации, более сложны чем другие. Эта функция должна ограничивать значения которые вы можете ввести в вашу базу данных чтобы заставить внешний ключ и родительский ключ соответствовать принципу справочной целостности. Одно из действий ограничения Внешнего Ключа — это отбрасывание значений для полей ограниченных как внешний ключ который еще не представлен в родительском ключе. Это ограничение также воздействует на вашу способность изменять или удалять значения родительского ключа (мы будем обсуждать это позже в этой главе ).

КАК МОЖНО ПОЛЯ ПРЕДСТАВИТЬ В КАЧЕСТВЕ ВНЕШНИХ КЛЮЧЕЙ

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

Рисунок 19.1: Внешний Ключ таблицы Заказчиков с родительским ключом

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

ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫ

Синтаксис ограничения таблицы FOREIGN KEY:

FOREIGN KEY REFERENCES

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

Второй список столбцов — это список столбцов которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:

* Они должны иметь одинаковое число столбцов.

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

Создадим таблицу Заказчиков с полем snum определенным в качестве внешнего ключа ссылающегося на таблицу Продавцов:

CREATE TABLE Customers

( cnum integer NOT NULL PRIMARY KEY

FOREIGN KEY (snum) REFERENCES Salespeople

Имейте в виду, что при использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения которые Вы указываете во внешнем ключе и родительском ключе, должны быть в состоянии справочной целостности. Иначе команда будет отклонена. Хотя ALTER TABLE очень полезна из-за ее удобства, вы должны будете в вашей системе, по возможности каждый раз, сначала формировать структурные принципы, типа справочной целостности.

ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ СТОЛБЦОВ

Вариант ограничения столбца ограничением FOREIGN KEY — по другому называется — ссылочное ограничение (REFERENCES), так как он фактически не содержит в себе слов FOREIGN KEY, а просто использует слово REFERENCES, и далее имя родительского ключа, подобно этому:

CREATE TABLE Customers

( cnum integer NOT NULL PRIMARY KEY,

snum integer REFERENCES Salespeople (snum));

Вышеупомянутое определяет Customers.snum как внешний ключ у которого родительский ключ — это Salespeople.snum. Это эквивалентно такому ограничению таблицы:

FOREIGN KEY (snum) REGERENCES Salespeople (snum)

НЕ УКАЗЫВАТЬ СПИСОК СТОЛБЦОВ ПЕРВИЧНЫХ КЛЮЧЕЙ

Используя ограничение FOREIGN KEY таблицы или столбца, вы можете не указывать список столбцов родительского ключа если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двумя ключами все еще применим. Например, если мы поместили ограничение PRIMARY KEY в поле snum таблицы Продавцов, мы могли бы использовать его как внешний ключ в таблице Заказчиков (подобно предыдущему примеру) в этой команде:

CREATE TABLE Customers

( cnum integer NOT NULL PRIMARY KEY,

snum integer REFERENCES Salespeople);

Это средство встраивалось в язык, чтобы поощрять вас использовать первичные ключи в качестве родительских ключей.

КАК СПРАВОЧНАЯ ЦЕЛОСТНОСТЬ ОГРАНИЧИВАЕТ ЗНАЧЕНИЯ РОДИТЕЛЬСКОГО КЛЮЧА

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

Этого не достаточно для родительского ключа в случае выполнения такого требования как при объявлении внешнего ключа. SQL должен быть уверен что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно вы должны убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобии ограничения NOT NULL.

ПЕРВИЧНЫЙ КЛЮЧ КАК УНИКАЛЬНЫЙ ВНЕШНИЙ КЛЮЧ

Ссылка ваших внешних ключей только на первичные ключи, как мы это делали в типовых таблицах, — хорошая стратегия. Когда вы используете внешние ключи, вы связываете их не просто с родительскими ключами на которые они ссылаются; вы связываете их с определенной строкой таблицы где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации которая бы не была уже представлена во внешнем ключе. Смысл, например, поля snum как внешнего ключа в таблице Заказчиков — это связь которую он обеспечивает, не к значению поля snum на которое он ссылается, а к другой информации в таблице Продавцов, такой например как, имена продавцов, их местоположение, и так далее. Внешний ключ — это не просто связь между двумя идентичными значениями; это — связь, с помощью этих двух значений, между двумя строками таблицы указанной в запросе.

Это поле snum может использоваться чтобы связывать любую информацию в строке из таблицы Заказчиков со ссылочной строкой из таблицы Продавцов — например чтобы узнать — живут ли они в том же самом городе, кто имеет более длинное имя, имеет ли продавец кроме данного заказчика каких-то других заказчиков, и так далее.

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

ОГРАНИЧЕНИЯ ВНЕШНЕГО КЛЮЧА

Внешний ключ, в частности, может содержать только те значения которые фактически представлены в родительском ключе или пустые(NULL). Попытка ввести другие значения в этот ключ будет отклонена.

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

ЧТО СЛУЧИТСЯ, ЕСЛИ ВЫ ВЫПОЛНИТЕ КОМАНДУ МОДИФИКАЦИИ

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

CREATE TABLE Salespeople

(snum integer NOT NULL PRIMARY KEY,


sname char(10) NOT NULL,

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

FOREIGN KEY (snum) REFERENCES Salespeople,

UNIQUE (cnum, snum) ;

CREATE TABLE Orders

(cnum integer NOT NULL PRIMARY KEY,

odate date NOT NULL,

cnum integer NOT NULL

snum integer NOT NULL

FOREIGN KEY (cnum, snum) REFERENCES

CUSTOMERS (cnum, snum);

ВКЛЮЧЕНИЕ ОПИСАНИЙ ТАБЛИЦЫ

Имеется несколько атрибутов таких определений о которых нужно поговорить. Причина по которой мы решили сделать поля cnum и snum в таблице Порядков, единым внешним ключом — это гарантия того, что для каждого заказчика содержащегося в порядках, продавец кредитующий этот порядок — тот же что и указаный в таблице Заказчиков. Чтобы создать такой внешний ключ, мы были бы должны поместить ограничение таблицы UNIQUE в два поля таблицы Заказчиков, даже если оно необязательно для самой этой таблицы. Пока поле cnum в этой таблица имеет ограничение PRIMARY KEY, оно будет уникально в любом случае, и следовательно невозможно получить еще одну комбинацию поля cnum с каким-то другим полем.

Создание внешнего ключа таким способом поддерживает целостность базы данных, даже если при этом вам будет запрещено внутреннее прерывание по ошибке и кредитовать любого продавца, иного чем тот который назначен именно этому заказчику. С точки зрения поддержания целостности базы данных, внутренние прерывания (или исключения ) конечно же нежелательны. Если вы их допускаете и в то же время хотите поддерживать целостность вашей базы данных, вы можете обьявить поля snum и cnum в таблице Порядков независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно. Фактически, использование поля snum в таблице Порядков, как мы это делали, необязательно, хотя это полезно было сделать для разнообразия. Поле cnum связывая каждый порядок заказчиков в таблице Заказчиков, в таблице Порядков и в таблице Заказчиков, должно всегда быть общим чтобы находить правильное поле snum для данного порядка (не разрешая никаких исключений).

Это означает что мы записываем фрагмент информации — какой заказчик назначен к какому продавцу — дважды, и нужно будет выполнять дополнительную работу чтобы удостовериться, что обе версии согласуются. Если мы не имеем ограничения внешнего ключа как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет проверять вручную (вместе с запросом ), чтобы удостовериться что именно соответствующий продавец кредитовал каждую соответствующую продажу.

Наличие такого типа информационной избыточности в вашей базе данных, называется деморализация (denormalization ), что не желательно в идеальной реляционной базе данных, хотя практически и может быть разрешена. Деморализация может заставить некоторые запросы выполняться быстрее, поскольку запрос в одной таблице выполняется всегда значительно быстрее чем в обьединении.

Как такие ограничения воздействуют на возможность и невозможность Вами использовать команды модификации DML? Для полей, определенных как внешние ключи, ответ довольно простой: любые значения которые вы помещаете в эти поля с командой INSERT или UPDATE должны уже быть представлены в их родительских кючах. Вы можете помещать пустые(NULL) значения в эти поля, несмотря на то что значения NULL не позволительны в родительских ключах, если они имеют ограничение NOT NULL. Вы можете удалять (DELETE ) любые строки с внешними ключами не используя родительские ключи вообще. Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, еще проще, но возможно несколько более ограничен: любое значение родительского ключа ссылаемого с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков пока он еще имеет порядки в таблице Порядков. В зависимости от того, как вы используете эти таблицы, это может быть или желательно или хлопотно. Однако — это конечно лучше чем иметь систему, которая позволит вам удалить заказчика с текущими порядками и оставить таблицу Порядков ссылающейся на несуществующих заказчиков.

Смысл этой системы оганичения в том, что создатель таблицы Порядков, используя таблицу Заказчиков и таблицу Продавцов как родительские ключи может наложить значительные ограничения на действия в этих таблицах. По этой причине, вы не сможете использовать таблицу которой вы не распоряжаетесь (т.е. не вы ее создавали и не вы являетесь ее владельцем), пока владелец(создатель) этой таблицы специально не передаст вам на это право (что объясняется в Главе 22).

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

* Вы можете ограничить, или запретить, изменение (способом ANSI ), обозначив, что изменения в родительском ключе — ограничены.

* Вы можете сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, что называется — каскадным изменением.

* Вы можете сделать изменение в родительском ключе, и установить внешний ключ в NULL, автоматически (полагая, что NULLS разрешен во внешнем ключе ), что называется — пустым изменением внешнего ключа.

Даже в пределах этих трех категорий, вы можете не захотеть обрабатывать все команды модификации таким способом. INSERT, конечно, к делу не относится. Он помещает новые значения родительского ключа в таблицу, так что ни одно из этих значений не может быть вызвано в данный момент. Однако, вы можете захотеть позволить модификациям быть каскадными, но без удалений, и наоборот. Лучшей может быть ситуация которая позволит вам определять любую из трех категорий, независимо от команд UPDATE и DELETE. Мы будем следовательно ссылаться на эффект модификации (update effects) и эффект удаления (delete effects ), котторые определяют, что случитс если вы выполните команды UPDATE или DELETE в родительском ключе. Эти эффекты, о которых мы говорили, называются: Ограниченные (RESTRICTED) изменения, Каскадируемые (CASCADES) изменения, и Пустые (NULL) изменения.

Фактические возможности вашей системы должны быть в строгом стандарте ANSI — это эффекты модификации и удаления, оба, автоматически ограниченнные — для более идеальной ситуации описаной выше. В качестве иллюстрации, мы покажем несколько примеров того, что вы можете делать с полным набором эффектов модификации и удаления. Конечно, эффекты модификации и удаления, являющиеся нестандартными средствами, испытывают недостаток в стандартном госинтаксисе. Синтаксис который мы используем здесь, прост в написании и будет служить в дальнейшем для иллюстрации функций этих эффектов.

Для полноты эксперимента, позволим себе предположить что вы имеете причину изменить поле snum таблицы Продавцов в случае, когда наша таблица Продавцов изменяет разделы. (Обычно изменение первичных ключей это не то что мы рекомендуем делать практически. Просто это еще один из доводов для имеющихся первичных ключей которые не умеют делать ничего другого кроме как, действовать как первичные ключи: они не должны изменяться. ) Когда вы изменяете номер продавца, вы хотите чтобы были сохранены все его заказчики. Онако, если этот продавец покидает свою фирму или компанию, вы можете не захотеть удалить его заказчиков, при удалении его самого из базы данных. Взамен, вы захотите убедиться, что заказчики назначены кому-нибудь еще. Чтобы сделать это вы должны указать UPDATE с Каскадируемым эффектом, и DELETE с Ограниченным эффектом.

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

snum integer REFERENCES Salespeople,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople RESTRICTED);

Если вы теперь попробуете удалить Peel из таблицы Продавцов, команда будет не допустима, пока вы не измените значение поля snum заказчиков Hoffman и Clemens для другого назначенного продавца. С другой стороны, вы можете изменить значение поля snum для Peel на 1009, и Hoffman и Clemens будут также автоматически изменены.

Третий эффект — Пустые (NULL) изменения. Бывает, что когда продавцы оставляют компанию, их текущие порядки не передаются другому продавцу.

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

CREATE TABLE Orders

(onum integer NOT NULL PRIMARY KEY,

odate date NOT NULL

cnum integer NOT NULL REFERENCES Customers

snum integer REFERENCES Salespeople,

UPDATE OF Customers CASCADES,

DELETE OF Customers CASCADES,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople NULLS);

Конечно, в команде DELETE с эффектом Пустого изменения в таблице Продавцов, ограничение NOT NULL должно быть удалено из поля snum.

ВНЕШНИЕ КЛЮЧИ КОТОРЫЕ ССЫЛАЮТСЯ ОБРАТНО К ИХ ПОДЧИНЕНЫМ ТАБЛИЦАМ

Как было упомянуто ранее, ограничение FOREIGN KEY может представить имя этой частной таблице, как таблицы родительского ключа. Далеко не будучи простой, эта особенность может пригодиться. Предположим, что мы имеем таблицу Employees с полем manager(администратор). Это поле содержит номера каждого из служащих, некоторые из которых являются еще и администраторами. Но так как каждый администратор — в то же время остается служащим, то он естественно будут также представлен в этой таблице. Давайте создадим таблицу, где номер служащего (столбец с именем empno ), объявляется как первичный ключ, а администратор, как внешний ключ, будет ссылаться на нее:

CREATE TABLE Employees

(empno integer NOT NULL PRIMARY KEY,

name char(10) NOT NULL UNIOUE,

manager integer REFERENCES Employees);

( Так как внешний ключ это ссылаемый первичный ключ таблицы, список столбцов может быть исключен. ) Имеется содержание этой таблицы:

Правила целостности.

Чтобы просмотреть это видео, включите JavaScript и используйте веб-браузер, который поддерживает видео в формате HTML5

Базы данных (Databases)

Half Faded Star

Курс знакомит слушателей с основными принципами работы со структурированными данными в реляционной модели, учит проектировать данные, описывать объекты базы данных в терминах реальной СУБД, составлять запросы на языке SQL, использовать представления, процедуры, функции и триггеры, создавать индексы, управлять конкурентным доступом к данным и манипулировать механизмом транзакций. Основу курса составляют изучение и применение языка SQL для создания, модификации объектов баз данных и управления данными в произвольной реляционной базе данных. Выполнение практических задач в рамках курса предполагает использование СУБД My SQL. В курсе рассматриваются этапы проектирования реляционных баз данных, правила составления запросов, основные методы индексирования данных. В курсе будут изучены вопросы использования транзакций и прав доступа к данным. Также курс дает обзор современных тенденций в области науки о данных в связи с появлением BigData. В заключении курса будут показаны сферы применения NoSQL баз данных и указаны современные подходы к обработке big data.

Получаемые навыки

Big Data, Database (DBMS), MySQL, SQL

Рецензии

Half Faded Star

На этой неделе мы познакомимся с типичными объектами реляционной базы данных, поговорим об их назначении и использовании. Изучим команды для описания таблиц и правил целостности. Рассмотрим команды манипулирования данными в таблицах: вставку, изменение, удаление. Также будет дано подробное описание демонстрационной базы данных, которую можно установить на своем компьютере и воспроизвести на основе СУБД MySQL (предварительно необходимо установить СУБД MySQL на свой компьютер).

Преподаватели

Михайлова Елена

Текст видео

[МУЗЫКА] [МУЗЫКА] [МУЗЫКА] Рассмотрим создание правил целостности. Чего мы хотим добиться, используя их? Мы хотим, чтобы некорректные данные в принципе не могли попасть в нашу базу данных. Также мы хотим обеспечить возможность связывания нескольких таблиц. Какие могут быть правила целостности? Это может быть NULL или NOT NULL, это может быть правило UNIQUE или PRIMARY KEY. Также это команды CHECK и FOREIGN KEY. Поговорим об этих правилах подробнее. Когда они появляются? Эти правила целостности могут быть описаны вместе с командой создания или модификации таблицы. Если мы используем создание правила целостности вместе с созданием таблицы, то мы указываем эти описания после описания столбца или отдельно после описания всех столбцов в таблице, если наше правило целостности затрагивает несколько столбцов. Как добавить или удалить правило целостности в нашу таблицу? Это можно сделать при помощи команды ALTER TABLE, которое позволяет нам менять таблицу в целом и отдельные ее столбцы и их описание. Рассмотрим первое правило целостности NULL или NOT NULL. Оно говорит нам о том, могут или не могут конкретные столбцы таблицы принимать незаданные или неопределенные значения. Рассмотрим на примере. Если мы записываем данные в какую-то телефонную книжку, наверное, нам нужно обязательно знать номер телефона человека и как его зовут. Также в нашу телефонную книжку мы можем записать некоторую дополнительную информацию, скажем, дату рождения или адрес. Но эти данные могут быть не у всех. Вот этот пример показывает, что некоторые столбцы нашей таблицы могут принимать незаданные значения, как, например, адрес и дата рождения, а некоторые являются обязательными, NOT NULL. К таким столбцам в нашем примере относятся номер телефона и имя человека. На нашем примере созданием таблицы PERSON мы запрещаем столбцу «идентификатор человека» принимать незаданные значения. Следующее правило целостности, очень важное в базах данных, называется первичный ключ, или PRIMARY KEY. Это ограничение целостности применяется для однозначной идентификации строк таблицы. Напомним, что каждая сущность должна быть у нас каким-то образом идентифицирована. Для каждой таблицы может быть определен только один первичный ключ. Но он, впрочем, может состоять не из одного поля, а из нескольких полей, и те столбцы, на которых определяется ограничение целостности «первичный ключ», не могут принимать незаданные значения. Давайте рассмотрим пример. Мы создаем таблицу Department, в которой будем хранить информацию об отделах, и идентификатор отдела или его номер мы делаем первичным ключом. Это означает, что значения его будут уникальны и не могут принимать незаданных значений. Кроме этого, в таблице будет еще одно поле — это название отдела. Когда мы обсуждали с вами ключи, то мы говорили, что некоторые сущности могут не иметь таких им свойственных атрибутов, которые образуют естественный ключ. Тогда нам может быть полезен ключ суррогатный, выдуманный системой. Для генерации таких искусственных ключей и существует у нас поле с автонумерацией. Пример его вы видите на экране. Если вы используете такое поле, то тип этого поля должен быть целочисленным, и, задавая такое поле, вы просите систему сгенерировать для каждой новой строки таблицы его уникальный идентификатор. На следующем примере мы видим, как создать первичный ключ из нескольких полей. Рассмотрим таблицу Person, в которой есть идентификатор человека, идентификатор отдела и имя человека. Если мы предположим, что у нас идентификатор человека уникален только в рамках отдела, то нам нужно сделать первичный ключ из комбинации двух полей — это идентификатор человека и идентификатор отдела. Для того чтобы сделать составной первичный ключ из двух полей, мы после описания полей таблицы пишем первичный ключ и далее в круглых скобках указываем те поля, которые первичный ключ будут образовывать. Мы научились с вами делать первичный ключ, задавая уникальность какого-то поля или комбинации полей. И мы сказали, что первичный ключ может быть для таблицы только один. Но нам может потребоваться задать уникальность не одного поля. Например, если мы будем рассматривать студента, то у него уникальным должен быть и номер паспорта, и номер зачетки. В качестве первичного ключа логично выбрать какое-то одно поле. Как же сделать другое поле уникальным? Это можно сделать при помощи ограничения целостности UNIQUE. Рассмотрим на примере, как это сделать. Для того чтобы сделать один или несколько атрибутов уникальными, мы можем использовать ограничение целостности UNIQUE. Если мы хотим сделать уникальным один из атрибутов, мы можем просто указать это правило целостности сразу после описания атрибута. А если нам нужно сделать комбинацию атрибутов уникальной, то мы после определения атрибутов пишем слово UNIQUE и в круглых скобках перечисляем атрибуты, значения которых вместе должны быть уникальны в рамках таблицы. Следует заметить отличие от PRIMARY KEY, во-первых, тем, что значения атрибутов, которые мы назвали уникальными, могут принимать в случае одной строки незаданное значение, и то, что атрибутов уникальных в нашей таблице может быть несколько. Следующее правило целостности называется FOREIGN KEY. Мы можем установить внешний ключ в таблице. Это правило целостности позволяет нам связать две таблицы. Давайте рассмотрим на примере таблицы отделов, в которых у нас хранится информация о номере отдела и названии отдела. И у нас есть таблица персонала, который мы хотим брать на работу, оформляя сразу в какой-то отдел. Каким образом у нас реализуются в таблицах связи вида один ко многим? Мы должны добавить в таблицу сотрудников, номер отдела, в который мы этого сотрудника зачисляем. Эти столбцы должны быть одинакового типа, но пока ничто не мешает нам, имея в распоряжении лишь отдел номер один и отдел номер два, оформить сотрудника в отдел номер три, что будет неправильно. Для того чтобы мы могли записывать информацию во вторую таблицу со ссылками лишь на существующие записи родительской таблицы, мы задаем ограничение FOREIGN KEY, связывая два столбца разных таблиц. Для того чтобы связать две таблицы при помощи внешнего ключа, мы должны иметь в этих таблицах, во-первых, два поля, значения по которым берутся из одной области определения. Эти два столбца должны быть строго одного типа, то есть нельзя сделать, например, поля в одном int, bigint, они должны быть строго одного типа. И поле главной таблицы, на которое мы будем ссылаться, должно быть объявлено первичным ключом или уникальным. Для того чтобы организовать такое правило целостности «внешний ключ», мы, определяя таблицу сотрудников после определения поля, которое ссылается на поле другой таблицы, пишем команду FOREIGN KEY и указываем таблицу, на которую мы ссылаемся и в круглых скобках указываем поле, которое связывается с данным текущим полем. Что нам гарантирует такая связка? Она гарантирует нам, что подчиненная таблица будет использовать в этом поле только те значения, которые присутствуют в родительской таблице. На экране вы видите пример, в котором таблица разработчиков или сотрудников связывается с таблицей отделов. Связку «внешний ключ» можно делать и внутри одной таблицы. Давайте представим пример, что у нас есть таблица разработчиков, и мы хотим сделать некоторых разработчиков руководителями других. Что мы можем сделать для этого? Для этого мы добавляем в нашу таблицу еще одно поле, которое будет по типу таким же, как ID разработчика. Назовем его Boss, и указываем, что это поле будет ссылаться на поле «идентификатор сотрудника», DeveloperId. Таким образом, мы установим ссылочную целостность на поля одной и той же таблицы, делая рекурсивную связь. Теперь интересный вопрос: а что делать при изменении родительской записи? Просто связка «внешний ключ» не дает нам добавить в подчиненную таблицу записи со значениями, которые не присутствуют в основной таблице. Но что делать, если мы добавили записи, все произошло корректно, но после этого записи в родительской таблице как-то меняются? Эти записи могут быть изменены или удалены. Вот для этого мы можем указать каскадное правило целостности, что делать с подчиненными записями при изменении родительской. На это есть несколько стратегий. Стратегия, которая применяется по умолчанию, запрещает вам удалять или изменять родительские записи, если на них есть записи в подчиненной таблице. Следующая стратегия — это каскадное обновление. Если у вас изменяется или удаляется запись в родительской таблице, то вместе с ней изменяются и удаляются подчиненные записи. Последняя возможность — это установка незаданных значений для подчиненной записи. Следующее правило целостности, которое мы рассмотрим, называется CHECK. Это дает нам возможность проверять значение конкретного атрибута до его добавления в базу данных. На примере, приведенном на экране, мы видим несколько правил целостности. Одно из правил целостности проверяет, что зарплата человека, которого мы принимаем на работу, будет не меньше 5 000 и не больше 50 000. Второе правило целостности проверяет принадлежность значения множеству. Должность человека, которого мы принимаем на работу, должна быть либо программист, либо аналитик, либо менеджер. Таким образом, мы рассмотрели с вами пять правил целостности, которые позволяют нам ограничивать значения столбцов таблицы.

Илон Маск рекомендует:  Предопределённые константы yp
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL