On имя_таблицы (имя_поля)

ON [PRIMARY]

GO

ALTER TABLE имя_таблицы_потомка

ADD CONSTRAINT [имя_ограничения] FOREIGN KEY

References [имя_таблицы_предка] ([имя_поля])

3. Выполните этот код нажатием F5, Ctrl+E или щелчком на кнопке панели инструментов. После того как индекс создан, становится возможным создание отношения между таблицами.

Отображение зависимостей

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

1. Нажмите правой кнопкой мыши на выбранную таблицу вашей базы данных.

2. В контекстном меню выберите пункт просмотреть зависимости.

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

Создание схемы базы данных при помощи Enterprise Manager

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

Добавьте новую диаграмму

В созданную вами диаграмму базы данных добавьте нужные таблицы

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

Изменение макета диаграммы базы данных

Часто расположение объектов схемы БД на диаграмме после ее создания SQL Server не очень удобно для восприятия и его лучше изменить.

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

2. Захватив мышью одну из выделенных таблиц, продолжая удерживать кнопку мыши нажатой, вы можете перетащить таблицы в другое место на диаграмме и затем отпустите кнопку мыши. Вертикальная линия, которая заканчивается изображением ключа и соединяет две таблицы в вашей схеме, представляет связь между ними, выраженную огра­ничением foreign key. Чтобы вывести на экран метку этой связи, щелкните пра­вой кнопкой мыши на фоновой части окна и выберите из контекстного меню пункт Показать подписи отношений . Появится имя ограниче­ния foreign key.

3. При отображении таблицы, показываются только имена столбцов. Если такой информации недостаточно, то при помощи кнопки Режим таблицы на панели инструментов можно изменить режим отображения таблиц с Column Names на Standart. Такое меню можно раскрыть, щелкнув на таблице правой кнопкой мыши и выбрав в контекстном меню команду Режим таблицы.

4. Диаграмму можно снабдить этикеткой. Для этого вызовите контекстное меню на фоновой части окна и выберите пункт Создать примечание.

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

6. При помощи диаграммы можно создать и новую таблицу в составе БД. Для этого на панели инструментов выберите кнопку Создать таблицу. В результате появится окно с запросом имени таблицы, введите это имя и нажмите «Ok». После чего откроется окно конструирования таблицы. Заполнив это окно, щелкните на нем правой кнопкой мыши. В появившемся контекстном меню вы увидите среди прочих команды Удалить из базы данных и Удалить из диаграммы. Первую команду выполнить пока невозможно, так как таблица храниться только в ОЗУ, то удалить ее из БД нельзя. При выборе второй команды система вам предложит выбор – сохранить созданную таблицу вне данной диаграммы. При выборе NO, таблица будет удалена без сохранения. После этого сохраните диаграмму, щелкнув на кнопке Сохранить на панели инструментов.

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

Создание отношений на схеме базы данных при помощи Enterprise Manager

1. Раскройте требуемую БД в левой панели Enterprise Manager и затем найдите нужную диаграмму. Щелкните на ней правой кнопкой мыши и выберите команду Изменить диаграмму БД . В результате откроется диаграмма

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

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

Добавление данных при помощи SQL-команды INSERT

Первый метод вставки данных заключается в использовании SQL-команды INSERT .

1. Найдите в окне Object Browser (Объекты) узел нужной вам таблицы и выделите его.

2. Щелкните правой кнопкой мыши и выберите в контекстном меню команду Script Object tо New Window As (Включить объекты в новое окно как), за которой скрываете» вложенное меню, в котором следует выбрать Insert.

3. В результате вы увидите фрагмент кода, который не помещается в окне (обратите внимание, в окне Query Analyzer отсутствует перенос по словам). Вот готовый к правке код, который сгенерирован в панели запросов окна Query Analyzer:

INSERT INTO [имя_базы_данных].[dbo].[имя_таблицы] ([имя_атр1], [имя_атр2], [имя_атр3], [имя_атр4], . . .)

VALUES (знач_атр1, знач_атр2, знач_атр3, знач_атр4, . . .)

4. Отредактировав код в панели запросов Query Analyzer, вы сможете убедиться, что следующая секция кода действительно добавляет данные в таблицу. Обратите внимание на тот факт, что код не заканчивается командой GO; в этом нет нужды, поскольку речь идет о единственной команде Insert.

5. Выполните код, нажав F5 или Ctrl+E или щелкните на кнопке Execute (ВЫПОЛНИТЬ) на панели инструментов.

В операторе вставки указывается каждый столбец таблицы.

Один из способов избежать необходимости в том, чтобы вводить при вставке записи данные в каждый столбец, заключается в разрешении значений NULL для столбцов. Для этого требуется лишь установить флажок Allow Nulls (Разрешить NULL-значение). Существует главное правило, касающееся значений NULL, и заключается оно в том, что первичный ключ не может содержать в себе значений NULL.

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

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

Если команда INSERT попытается ввести данные в identity-столбец, также произойдет ошибка.

Добавление данных при помощи SQL Server management

1. Откройте окно Enterprise Manager.

2. Разверните узел нужной базы данных, затем узел с таблицами таблицей и выделите элемент нужной таблицы. Щелкните на нем правой кнопкой мыши и выберите в меню команду Open Table (Открыть таблицу), а затем выберите Return all rows (Вернуть все строки),

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

4. Эта операция сводится к тому, чтобы ввести информацию в необходимые столбцы. Однако, если вы осуществите ввод не во все столбцы и оставите пустым столбец, ввод данных в который обязателен, то получите сообщение об ошибке. Нажатие клавиши "стрелка вниз" приводит к тому, что позиция ввода (и указатель текущей записи) перемещается вниз. В этот момент SQL-сервер попытается обновить в таблице запись, которую вы покидаете.

5. Щелкнув на ОК, мы вернемся назад и сможем доввести недостающие данные. Завершив ввод, мы снова нажмем клавишу стрелка вниз, при этом база данных обновится. Обратите внимание на следующий факт: несмотря на то, что введена лишь первая запись в таблице, в столбце видим значение 2. Очередное значение идентификатора генерируется при каждой попытке вставить запись, вне зависимости от успеха этой попытки. Следствием этого факта могут стать пробелы в нумерации строк. Эта проблема легко решается при помощи Query Analyzer. При необходимости, можно сбросить текущее значение счетчика таким образом, что Query Analyser начнет отсчет с более желательного для вас числа. Синтаксис этой команды несложен:

DBCC CHEKIDENT ("имя таблицы"[, {NORESEED|RESEED [новое_значение_счетчика]}])

где,

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

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

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

Ограничения целостности для столбца

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

Существует два способа добавить в таблицу ограничение целостности:

• В окне Enterprise Manager, используя диалоговое окно Таblе Properties (Свойства таблицы), точно таким же образом, как и при создании отношений, при помощи вкладки Constraints (Ограничения), или в конструкторе таблиц.

• При помощи T-SQL-команд в окне Query Analyser.

Способ создания ограничений целостности при помощи команды ALTER TABLE T-SQL.

1. Запустите Query Analyzer.

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

USE имя_базы_ данных

GO

ALTER TABLE dbo.имя_таблицы

ADD CONSTRAINT имя_ключа PRIMARY KEY [CLUSTERED| NONCLUSTERED]

(столбец[, столбец,. . .])

ON группа_файлов

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

ADD CONSTRAINT имя_ограничения CHECK [синтаксис проверки]

Синтаксис ограничения CHECK представляет собой простое логическое выражение. При выполнении проверки выражение может оказаться либо истинным, либо ложным. Это может быть, например, математический оператор, сравнивающий значение с заданными константами (например, ([Kod]>=0)).

В операторе ALTER TABLE команде ADD CONSTRAINT может предшествовать WITH NOCHECK. Это выражение информирует SQL-сервер о том, что существующие в настоящий момент в таблице данные не следует проверять на соответствие условиям того ограничения, которое создается, и что проверке должны подвергаться только новые данные, которые будут добавляться в таблицу

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

ADD CONSTRAINT имя_ограничения DEFAULT значение_по_умолчанию

For имя_столбца

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

4. Выполните весь введенный код нажатием F5, Cfrl+E или щелчком на кнопке Execute на панели инструментов.

Транзакции

Транзакция представляет собой единицу работы, которая должна быть подвергнута тесту ACID (Atomicity, Consistency, Isolation и Durability - Атомарность, Целостность, Изоляция и Окончательность), прежде чем быть классифицированной, как транзакция.

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

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

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

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

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

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

Транзакция состоит из двух основных элементов. Это начало (старт) транзакции и окончание транзакции, когда мы решаем, принять транзакцию или отменить ее. Команда BEGIN TRAN объявляет начало транзакции. Собственно транзакция стартует с момента выполнения этой команды. Начиная с итого момента и оканчивая моментом выполнения одной из двух команд - COMMIT TRAN или ROLLBACK TRAN - любые операции, вносящие изменения в данные, относятся к данной транзакции.

Команду BEGIN TRAN можно дополнить именем транзакции длиной до 32 символов. Если вам потребуется вложенная транзакция, то есть транзакция, стартующая внутри другой транзакции, то именем можно снабдить только "внешнюю" транзакцию.

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

Если нам потребуется отменить все изменения, которые успели внести операции, относящиеся к текущей транзакции (например, по причине возникших ошибок), то мы можем использовать команду ROLLBACK TRAN. Представьте себе следующую ситуацию: мы начали транзакцию командой BEGIN TRAN, затем успешно вставили строку в таблицу командой INSERT, а потом команда UPDATE завершилась с ошибкой. Мы можем "откатить" таблицу к предыдущему состоянию, то есть состоянию еще до выполнения команды INSERT при помощи команды ROLLBACK TRAN. Таким образом, команда INSERT также будет отменена, несмотря на то, что она была выполнена успешно и без ошибок.

Основное назначение блокировок состоит в том, чтобы начавшаяся транзакция, которая вносит изменения в данные и "знает", что их, возможно, придется затем отменить, могла быть твердо "уверенной" в том, что никакая другая транзакция к этому моменту не изменит данные еще раз. Однако с таким подходом связана одна проблема, заключающаяся в следующем: SQL-сервер не может просто блокировать данные, уже подвергшиеся изменению в транзакции. Блокируются данные, которые изменены не завершившейся транзакцией (блокировка на уровне строки или записи), но сервер может заблокировать и всю базу данных (блокировка на уровне базы данных). Между этими двумя крайними случаями есть целый ряд промежуточных уровней, поэтому можно заблокировать большие или меньшие ресурсы, в зависимости от выполняемых операций.

Блокировками SQL-сервер управляет автоматически, но понимание происходящих при этом процессов позволит вам эффективно использовать блокировки в ваших транзакциях.

Обновление данных на основе механизма транзакций

1. Запустите Query Analyzer. Первый пример демонстрирует в действии использование команды COMMIT TRAM.

2. Введите следующий код и выполните его.

SET QUOTED_IDENTIFIER OFF

GO

BEGIN TRAN Restore_Value

DECLARE @ValueToUpdate varchar (30)

SET @ValueToUpdate = " Значение_1"

UРDATE имя_таблицы

SET имя_столбца = @ValueToUpdate

Where условие_отбора

COMMIT TRAN

Обратите внимание, что COMMIT TRAN не использует имя транзакции, заданное в BEGIN TRAN.

3. Теперь мы продемонстрируем другой исход транзакции - отмену иди "откат" транзакции при помощи команды ROLLBACK TRAN.

SET QUOTED_IDENTIFIER OFF

GO

BEGIN TRAN

UРDATE имя_таблицы

SET имя_столбца = " Значение_1"

Where условие_отбора

4.После выполнения этого кода, мы увидим, что произошла ошибка: весь столбец принял одно и тоже значение " Значение_1" .

5. Чтобы исправить ошибку, нужно воспользоваться командой ROLLBACK TRAN. Выполните эту команду. Это можно сделать в панели запросов.

Удаление данных

Удаление данных из таблицы производится командой DELETE. Ее синтаксис:

DELETE

FROM имя_таблицы

Where уловие_отбора

Одной командой можно удалять записи только из одной таблицы.

Удалять записи из таблицы можно также при помощи Enterprise Manager.

1. Откройте Enterprise Manager, откройте базу данных и найдите нужную таблицу. Щелкните на ее значке правой кнопкой мыши, выберите в контекстном меню команду Open Table (Открыть таблицу), затем выберите Return all rows (Вернуть вес строки),

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

2. Теперь нажмите клавишу Delete. В ответ на экране появится окно сообщения, запрашивающее ваше подтверждение. Чтобы удалить запись, щелкните нa Yes (Да).

3. 4. В результате вы увидите, что запись из таблицы удалена.

4. Другой способ удалить запись состоит в том, чтобы щелкнуть на соответствующей строке правой кнопкой мыши и выбрать в контекстном меню команду Delete