В MS SQL Server по умолчанию создается 4 системных баз данных: master: эта главная БД сервера, в случае ее отсутствия или повреждения сервер не сможет работать. Она хранит все используемые логины пользователей сервера, их роли, различные конфигурационные настройки, имена и информацию о базах данных, которые хранятся на сервере, а также ряд другой информации. model : эта БД представляет шаблон, на основе которого создаются другие БД. То есть когда мы создаем через SSMS свою БД, она создается как копия базы model. msdb : хранит информацию о работе, выполняемой таким компонентом как планировщик SQL. Также она хранит информацию о бекапах баз данных. tempdb : эта БД используется как хранилище для временных объектов. Она заново пересоздается при каждом запуске сервера.
Все эти базы можно увидеть через SQL Server Management Studio в узле Databases -> System Databases:
Создание базы данных в SQL Management Studio Теперь создадим свою базу данных. Для этого мы можем использовать скрипт на языке SQL, либо все сделать с помощью графических средств в SQL Management Studio. В данном случае мы выберем второй способ. Для этого откроем SQL Server Management Studio и нажмем правой кнопкой мыши на узел Databases. Затем в появившемся контекстном меню выберем пункт New Database :
После этого нам открывается окно для создания базы данных:
В поле Database необходимо ввести название новой БД. Пусть у нас БД называется university. Следующее поле Owner задает владельца БДх. По умолчанию оно имеет значение < defult >, то есть владельцем будет тот, кто создает эту БД. Оставим это поле без изменений. Далее идет таблица для установки общих настроек БД. Она содержит две строки – 1-я для установки настроек для главного файла, где будут храниться данные, и 2-я строка для конфигурации файла логгирования. В частности, мы можем установить следующие настройки: Logical Name : логическое имя, которое присваивается файлу БД. File Type : есть несколько типов файлов, но, как правило, основная работа ведется с файлами данных ( ROWS Data) и файлом лога ( LOG) Filegroup : обозначет группу файлов. Группа файлов может хранить множество файлов и может использоваться для разбиения БД на части для размещения в разных местах. Initial Size (MB) : устанавливает начальный размер файлов при создании (фактический размер может отличаться от этого значения).
Autogrowth/ Maxsize : при достижении базой данных начального размера SQL Server использует это значение для увеличения файла. Path : каталог, где будут храниться базы данных. File Name : непосредственное имя физического файла. Если оно не указано, то применяется логическое имя. После ввода названия базы данных нажмем на кнопку ОК, и бд будет создана. После этого она появится среди баз данных сервера. Если эта бд впоследствии не потребуется, то ее можно удалить, нажав на нее правой кнопкой мыши и выбрав в контекстном меню пункт Delete:
Создание таблиц Ключевым объектом в базе данных являются таблицы. Таблицы состоят из строк и столбцов. Столбцы определяют тип информации, которая хранится, а строки содержат значения для этих столбцов. Для этого раскроем узел базы данных university в SQL Server Management Studio, нажмем на его подузел Tables правой кнопкой мыши и далее в контексто меню выберем New -> Table... :
После этого нам откроется дизайнер таблицы. В центральной части в таблице необходимо ввести данные о столбцах таблицы. Дизайнер содержит три поля: Column Name : имя столбца Data Type : тип данных столбца. Тип данных определяет, какие данные могут храниться в этом столбце. Например, если столбец представляет числовой тип, то он может хранить только числа. Allow Nulls : может ли отсутствовать значение у столбца, то есть может ли он быть пустым
Создадим таблицу с данными учащихся в учебном заведении. Для этого в дизайнере таблицы четыре столбца: Id, FirstName, LastName и Year, которые будут представлять соответственно уникальный идентификатор пользователя, его имя, фамилию и год рождения. У первого и четвертого столбца надо указать тип int ( то есть целочисленный), а у столбцов FirstName и LastName - тип nvarchar (50) ( строковый). Затем в окне Properties, которая содержит свойства таблицы, в поле Name надо ввести имя таблицы - Students, а в поле Identity ввести Id, то есть тем самым указывая, что столбец Id будет идентификатором. Имя таблицы должно быть уникальным в рамках базы данных. Как правило, название таблицы отражает название сущности, которая в ней хранится. Например, мы хотим сохранить студентов, поэтому таблица называется Students ( слово студент во множественном числе на английском языке). Существуют разные мнения по поводу того, стоит использовать название сущности в единственном или множественном числе ( Student или Students). В данном случае вопрос наименования таблицы всецело ложится на разработчика базы данных. И в конце нам надо отметить, что столбец Id будет выполнять роль первичного ключа ( primary key). Первичный ключ уникально идентифицирует каждую строку. В роли первичного ключа может выступать один столбец, а может и несколько.
Для установки первичного ключа нажмем на столбец Id правой кнопкой мыши и в появившемся меню выберем пункт Set Primary Key.
После этого напротив поля Id должен появиться золотой ключик. Этот ключик будет указывать, что столбец Id будет выполнять роль первичного ключа. И после сохранения в базе данных university появится таблица Students: Мы можем заметить, что название таблицы на самом деле начинается с префикса dbo. Этот префикс представляет схему. Схема определяет контейнер, который хранит объекты. То есть схема логически разграничивает базы данных. Если схема явным образом не указывается при создании объекта, то объект принадлежит схеме по умолчанию - схеме dbo.
Нажмем правой кнопкой мыши на название таблицы, и нам отобразится контекстное меню с опциями:
Для добавления начальных данных можно выбрать опцию Edit Top 200 Rows. Она открывает в виде таблицы 200 первых строк и позволяет их изменить. Но так как у нас таблица только создана, то естественно в ней будет никаких данных. Введем пару строк - пару студентов, указав необходимые данные для столбцов:
Затем опять же по клику на таблицу правой кнопкой мыши мы можем выбрать в контекстном меню пункт Select To 1000 Rows, и будет запущен скрипт, который отобразит первые 1000 строк из таблицы:
Первый запрос на T-SQL О ткроем SQL Management Studio, нажмем правой кнопкой мыши на элемент самого верхнего уровня в Object Explorer ( название сервера) и в появившемся контекстном меню выберем пункт New Query :
После этого в центральной части программы откроется окно для ввода команд языка SQL. Выполним запрос к таблице, которая была создана в прошлой теме, в частности, получим все данные из нее. База данных у нас называется university, а таблица - dbo.Students, поэтому для получения данных из таблицы введем следующий запрос: 1 SELECT * FROM university.dbo.Students
Оператор SELECT позволяет выбирать данные. FROM указывает источник, откуда брать данные. Фактически этим запросом мы говорим "ВЫБРАТЬ все ИЗ таблицы university.dbo.Students ". Стоит отметить, что для названия таблицы используется полный ее путь с указанием базы данных и схемы.
После ввода запроса нажмем на панели инструментов на кнопку Execute, либо можно нажать на клавишу F5. В результате выполнения запроса в нижней части программы появится небольшая таблица, которая отобразит результаты запроса - то есть все данные из таблицы Students. Если необходимо совершить несколько запросов к одной и той же базе данных, то мы можем использовать команду USE, чтобы зафиксировать базу данных. В этом случае при запросах к таблицам достаточно указать их имя без имени бд и схемы: 1 2 USE university SELECT * FROM Students
В данном случае мы выполняем запрос в целом для сервера, мы можем обратиться к любой базе данных на сервере. Но также мы можем выполнять запросы только в рамках конкретной базы данных. Для этого необходимо нажать правой кнопкой мыши на нужную бд и в контекстном меню выбрать пункт New Query :
Если в этом случае мы захотим выполнить запрос к выше использованной таблице Students, то нам не пришлось бы указывать в запросе название базы данных и схему, так как эти значения итак уже были бы понятны: 1 SELECT * FROM Students
Создание и удаление базы данных Для создания базы данных используется команда CREATE DATABASE. Чтобы создать новую базу данных откроем SQL Server Management Studio. Нажмем на назначение сервера в окне Object Explorer и в появившемся меню выберем пункт New Query.
В центральное поле для ввода выражений sql введем следующий код: 1 CREATE DATABASE usersdb Тем самым мы создаем базу данных, которая будет называться " usersdb ":
После создания базы даных, мы можем установить ее в качестве текущей с помощью команды USE : 1 USE usersdb ; Прикрепление базы данных Возможна ситуация, что у нас уже есть файл базы данных, который, к примеру, создан на другом компьютере. Файл базы данных представляет файл с расширением mdf, и этот файл в принципе мы можем переносить. Однако даже если мы скопируем его компьютер с установленным MS SQL Server, просто так скопированная база данных на сервере не появится. Для этого необходимо выполнить прикрепление базы данных к серверу. В этом случае применяется выражение:
1 2 3 CREATE DATABASE название_базы_данных ON PRIMARY(FILENAME=' путь_к_файлу _ mdf _ на_локальном_компьютере ’) FOR ATTACH; После выполнения команды на сервере появится база данных contactsdb. Удаление базы данных Для удаления базы данных применяется команда DROP DATABASE, которая имеет следующий синтаксис: 1 DROP DATABASE database_name1 [, database_name2]... После команды через запятую мы можем перечислить все удаляемые базы данных. Например, удаление базы данных contactsdb : 1 DROP DATABASE contactsdb
Создание и удаление таблиц Для создания таблиц применяется команда CREATE TABLE. С этой командой можно использовать ряд операторов, которые определяют столбцы таблицы и их атрибуты. И кроме того, можно использовать ряд операторов, которые определяют свойства таблицы в целом. Одна база данных может содержать до 2 миллиардов таблиц. Общий синтаксис создания таблицы выглядит следующим образом: 1 2 3 4 5 6 7 CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, ................................................ название_столбца N тип_данных атрибуты_столбца N, атрибуты_таблицы )
После команды CREATE TABLE идет название создаваемой таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть уникальным. Имя должно иметь длину не больше 128 символов. Имя может состоять из алфавитно-цифровых символов, а также символов $ и знака подчеркивания. Причем первым символом должна быть буква или знак подчеркивания. Имя объекта не может включать пробелы и не может представлять одно из ключевых слов языка Transact-SQL. Если идентификатор все же содержит пробельные символы, то его следует заключать в кавычки. Если необходимо в качестве имени использовать ключевые слова, то эти слова помещаются в квадратные скобки. Примеры корректных идентификаторов:
1 2 3 4 5 Users tags$345 users_accounts "users accounts" [Table] После имени таблицы в скобках указываются параметры всех столбцов и в самом конце атрибуты, которые относятся ко всей таблице. Атрибуты столбцов и атрибуты таблицы являются необязательными компонентами, и их можно не указывать. В самом просто виде команда CREATE TABLE должна содержать как минимум имя таблицы, имена и типы столбцов. Таблица может содержать от 1 до 1024 столбцов. Каждый столбец должен иметь уникальное в рамках текущей таблицы имя, и ему должен быть назначен тип данных.
Например, определение простейшей таблицы Customers: CREATE TABLE Customers ( Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) ) В данном случае в таблице Customers определяются шесть столбцов: Id, FirstName, LastName, Age, Email, Phone. Первые два столбца представляют идентификатор клиента и его возраст и имеют тип INT, то есть будут хранить числовые значения. Следующие два столбца представляют имя и фамилию клиента и имеют тип NVARCHAR(20), то есть представляют строку UNICODE длиной не более 20 символов. Последние два столбца Email и Phone представляют адрес электронной почты и телефон клиента и имеют тип VARCHAR(30/20) - они также хранят строку, но не в кодировке UNICODE.
Создание таблицы в SQL Management Studio Создадим простую таблицу на сервере. Для этого откроем SQL Server Management Studio и нажмем правой кнопкой мыши на название сервера. В появившемся контекстном меню выберем пункт New Query.
Таблица создается в рамках текущей базы данных. Если мы запускаем окно редактора SQL как это сделано выше - из под названия сервера, то база данных по умолчанию не установлена. И для ее установки необходимо применить команду USE, после которой указывается имя базы данных. Поэтому введем в поле редактора SQL- команд следующие выражения: 1 2 3 4 5 6 7 8 9 10 11 USE usersdb ; CREATE TABLE Customers ( Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) );
Также можно открыть редактор из под базы данных, также нажав на нее правой кнопкой мыши и выбрав New Query:
Удаление таблиц Для удаления таблиц используется команда DROP TABLE, которая имеет следующий синтаксис: 1 DROP TABLE table1 [, table2,...] Например, удаление таблицы Customers: 1 DROP TABLE Customers Переименование таблицы Для переименования таблиц применяется системная хранимая процедура " sp_rename ". Например, переименование таблицы Users в UserAccounts в базе данных usersdb : 1 2 USE usersdb ; EXEC sp_rename 'Users', ' UserAccounts ';
Типы данных T-SQL Числовые типы данных BIT : хранит значение от 0 до 16. Может выступать аналогом булевого типа в языках программирования (в этом случае значению true соответствует 1, а значению false - 0). При значениях до 8 (включительно) занимает 1 байт, при значениях от 9 до 16 - 2 байта. TINYINT : хранит числа от 0 до 255. Занимает 1 байт. Хорошо подходит для хранения небольших чисел. SMALLINT : хранит числа от –32 768 до 32 767. Занимает 2 байта INT : хранит числа от –2 147 483 648 до 2 147 483 647. Занимает 4 байта. Наиболее используемый тип для хранения чисел. BIGINT : хранит очень большие числа от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, которые занимают в памяти 8 байт. DECIMAL : хранит числа c фиксированной точностью. Занимает от 5 до 17 байт в зависимости от количества чисел после запятой.
Данный тип может принимать два параметра precision и scale: DECIMAL(precision, scale). Параметр precision представляет максимальное количество цифр, которые может хранить число. Это значение должно находиться в диапазоне от 1 до 38. По умолчанию оно равно 18. Параметр scale представляет макс. Кол-во цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0. NUMERIC : данный тип аналогичен типу DECIMAL. SMALLMONEY : хранит дробные значения от -214 748.3648 до 214 748.3647. Предназначено для хранения денежных величин. MONEY : хранит дробные значения Представляет денежные величины и занимает 8 байт. Эквивалентен типу DECIMAL(19,4). FLOAT : хранит числа от –1.79 E+308 до 1.79 E+308. Занимает от 4 до 8 байт в зависимости от дробной части.
Может иметь форму опредеения в виде FLOAT(n), где n представляет число бит, которые используются для хранения десятичной части числа (мантиссы). По умолчанию n = 53. REAL : хранит числа от –340 E+38 to 3.40E+38. Занимает 4 байта. Эквивалентен типу FLOAT(24). Примеры числовых столбцов: 1 2 3 4 Salary MONEY, TotalWeight DECIMAL(9,2), Age INT, Surplus FLOAT
Типы данных, представляющие дату и время DATE : хранит даты от 0001-01-01 (1 января 0001 года) до 9999-12-31 (31 декабря 9999 года). Занимает 3 байта. TIME : хранит время в диапазоне от 00:00:00.0000000 до 23:59:59.9999999. Занимает от 3 до 5 байт. Может иметь форму TIME(n), где n представляет количество цифр от 0 до 7 в дробной части секунд. DATETIME : хранит даты и время от 01/01/1753 до 31/12/9999. Занимает 8 байт. DATETIME2 : хранит даты и время в диапазоне от 01/01/0001 00:00:00.0000000 до 31/12/9999 23:59:59.9999999. Занимает от 6 до 8 байт в зависимости от точности времени. Может иметь форму DATETIME2(n), где n представляет количество цифр от 0 до 7 в дробной части секунд. SMALLDATETIME : хранит даты и время в диапазоне от 01/01/1900 до 06/06/2079, то есть ближайшие даты. Занимает от 4 байта. DATETIMEOFFSET : хранит даты и время в диапазоне от 0001-01-01 до 9999-12-31. Сохраняет детальную информацию о времени с точностью до 100 наносекунд. Занимает 10 байт.
Распространенные форматы дат: yyyy -mm-dd - 2017-07-12 dd/mm/ yyyy - 12/07/2017 mm-dd- yy - 07-12-17 В таком формате двузначные числа от 00 до 49 воспринимаются как даты в диапазоне 2000-2049. А числа от 50 до 99 как диапазон чисел 1950 - 1999. Month dd, yyyy - July 12, 2017 Распространенные форматы времени: hh:mi - 13:21 hh:mi am/pm - 1:21 pm hh:mi:ss - 1:21:34 hh:mi:ss:mmm - 1:21:34:12 hh:mi:ss:nnnnnnn - 1:21:34:1234567
Строковые типы данных CHAR : хранит строку длиной от 1 до 8 000 символов. На каждый символ выделяет по 1 байту. Не подходит для многих языков, так как хранит символы не в кодировке Unicode. Количество символов, которое может хранить столбец, передается в скобках. Например, для столбца с типом CHAR(10) будет выделено 10 байт. И если мы сохраним в столбце строку менее 10 символов, то она будет дополнена пробелами. VARCHAR : хранит строку. На каждый символ выделяется 1 байт. Можно указать конкретную длину для столбца - от 1 до 8 000 символов, например, VARCHAR(10). Если строка должна иметь больше 8000 символов, то задается размер MAX, а на хранение строки может выделяться до 2 Гб: VARCHAR(MAX). Не подходит для многих языков, так как хранит символы не в кодировке Unicode. В отличие от типа CHAR если в столбец с типом VARCHAR(10) будет сохранена строка в 5 символов, то в столце будет сохранено именно пять символов. NCHAR : хранит строку в кодировке Unicode длиной от 1 до 4 000 символов. На каждый символ выделяется 2 байта. Например, NCHAR(15)
NVARCHAR : хранит строку в кодировке Unicode. На каждый символ выделяется 2 байта.Можно задать конкретный размер от 1 до 4 000 символов: . Если строка должна иметь больше 4000 символов, то задается размер MAX, а на хранение строки может выделяться до 2 Гб. Еще два типа TEXT и NTEXT являются устаревшими и поэтому их не рекомендуется использовать. Вместо них применяются VARCHAR и NVARCHAR соответственно. 1 2 Email VARCHAR(30), Comment NVARCHAR(MAX)
Бинарные типы данных BINARY : хранит бинарные данные в виде последовательности от 1 до 8 000 байт. VARBINARY : хранит бинарные данные в виде последовательности от 1 до 8 000 байт. Еще один бинарный тип - тип IMAGE является устаревшим, и вместо него рекомендуется применять тип VARBINARY. Остальные типы данных UNIQUEIDENTIFIER : уникальный идентификатор GUID ( по сути строка с уникальным значением), который занимает 16 байт. TIMESTAMP : некоторое число, которое хранит номер версии строки в таблице. Занимает 8 байт. CURSOR : представляет набор строк. HIERARCHYID : представляет позицию в иерархии. SQL_VARIANT : может хранить данные любого другого типа данных T-SQL. XML : хранит документы XML или фрагменты документов XML. Занимает в памяти до 2 Гб. TABLE : представляет определение таблицы. GEOGRAPHY : хранит географические данные, такие как широта и долгота. GEOMETRY : хранит координаты местонахождения на плоскости.
PRIMARY KEY С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом. 1 2 3 4 5 6 7 8 9 CREATE TABLE Customers ( Id INT PRIMARY KEY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )
Первичный ключ уникально идентифицирует строку в таблице. В качестве первичного ключа необязательно должны выступать столбцы с типом int, они могут представлять любой другой тип. Установка первичного ключа на уровне таблицы: 1 2 3 4 5 6 7 8 9 10 CREATE TABLE Customers ( Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), PRIMARY KEY(Id) )
IDENTITY Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно. CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )
UNIQUE Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE. CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )
NULL и NOT NULL Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL. Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа - в этом случае по умолчанию столбец имеет значение NOT NULL. CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )
DEFAULT Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию. CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE );
CHECK Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100: CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE CHECK(Email !=''), Phone VARCHAR(20) UNIQUE CHECK(Phone !='') );
С толбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL). Для соединения условий используется ключевое слово AND. Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=). Также с помощью CHECK можно создать ограничение в целом для таблицы: CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE, CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !='')) )
Оператор CONSTRAINT. Установка имени ограничений. С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, DEFAULT, CHECK. Имена ограничений можно задать на уровне столбцов. Они указываются после CONSTRAINT перед атрибутами: CREATE TABLE Customers ( Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18 CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE, Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE )
Внешние ключи Внешние ключи применяются для установки связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Хотя, как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы, но это необязательно должно быть непременным условием. Внешний ключ также может указывать на какой-то другой столбец, который имеет уникальное значение. Общий синтаксис установки внешнего ключа на уровне столбца: [FOREIGN KEY] REFERENCES главная_таблица ( столбец_главной_таблицы ) [ ON DELETE {CASCADE|NO ACTION}] [ON UPDATE {CASCADE|NO ACTION}]
Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY, но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE. Общий синтаксис установки внешнего ключа на уровне таблицы: FOREIGN KEY ( стобец1, столбец2,... столбец N) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2,... столбец_главной_таблицы N) [ON DELETE {CASCADE|NO ACTION}] [ON UPDATE {CASCADE|NO ACTION}]
Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers. Определение внешнего ключа на уровне таблицы выглядело бы следующим образом: CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY ( CustomerId ) REFERENCES Customers (Id) );
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса " FK_": CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY ( CustomerId ) REFERENCES Customers (Id) );
ON DELETE и ON UPDATE С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции: CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице. NO ACTION : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют. SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL. SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.
Каскадное удаление По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается какая-либо строка из зависимой таблицы, то мы не сможем удалить эту строку из главной таблицы. Вначале нам необходимо будет удалить все связанные строки из зависимой таблицы. И если при удалении строки из главной таблицы необходимо, чтобы были удалены все связанные строки из зависимой таблицы, то применяется каскадное удаление, то есть опция CASCADE : CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY ( CustomerId ) REFERENCES Customers (Id) ON DELETE CASCADE )
Аналогично работает выражение ON UPDATE CASCADE. При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Но так как первичные ключи, как правило, изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко. Установка NULL При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL: CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY ( CustomerId ) REFERENCES Customers (Id) ON DELETE SET NULL );
Установка значения по умолчанию CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY ( CustomerId ) REFERENCES Customers (Id) ON DELETE SET DEFAULT )
Изменение таблицы Возможно, в какой-то момент мы захотим изменить уже имеющуюся таблицу. Например, добавить или удалить столбцы, изменить тип столбцов, добавить или удалить ограничения. То есть потребуется изменить определение таблицы. Для изменения таблиц используется выражение ALTER TABLE. Общий формальный синтаксис команды выглядит следующим образом: ALTER TABLE название_таблицы [ WITH CHECK | WITH NOCHECK] { ADD название_столбца тип_данных_столбца [ атрибуты_столбца ] | DROP COLUMN название_столбца | ALTER COLUMN название_столбца тип_данных_столбца [ NULL|NOT NULL] | ADD [CONSTRAINT] определение_ограничения | DROP [CONSTRAINT] имя_ограничения }
Добавим в таблицу Customers новый столбец Address: 1 2 ALTER TABLE Customers ADD Address NVARCHAR(50) NULL; В данном случае столбец Address имеет тип NVARCHAR и для него определен атрибут NULL. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена: ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL; Удаление столбца Удалим столбец Address из таблицы Customers: ALTER TABLE Customers DROP COLUMN Address;
