Приветствую всех, в этом занятии мы разберем, новые операторы и примеры с их использованием, а так же связи между таблицами.
Data Definition Language (DDL) (язык описания данных) — это словарь, используемый для определения или описания структуры баз данных.
В DDL входят такие операторы как: CREATE (создать), ALTER (изменить), DROP (удалить).
Оператор CREATE используются для определения новых сущностей.
Оператор ALTER используются для изменения определений существующих сущностей.
Оператор DROP используются для удаления существующих сущностей.
Изменение Базы Данных:
|
1 2 3 4 5 |
ALTER DATABASE ShopDB -- Оператор ALTER DATABASE изменяет БД MODIFY FILE -- Изменение размера БД ( NAME = 'ShopDB', SIZE = 100MB ) GO |
|
1 2 3 4 5 6 7 |
ALTER DATABASE ShopDB -- Оператор ALTER DATABASE изменяет БД MODIFY FILE ( NAME = 'ShopDB', MAXSIZE = 1000MB, -- Изменение максимального размера БД FILEGROWTH = 20% ) -- и параметра увеличения размера БД -- Если FILEGROWTH задается в процентах, то процент считается от текущего размера БД (FILEGROWTH = 0,2*SIZE). GO |
Важно! Измененный максимальный размер не может быть меньше текущего размера БД или равный текущему.
Изменяем таблицу Customers, добавив столбец NewFild
|
1 2 3 4 |
ALTER TABLE Customers -- Оператор ALTER TABLE изменяет таблицу ADD -- ADD - добавляет NewFild int NULL -- столбец. GO |
Изменяем таблицу Customers, удалив столбец NewFild
|
1 2 3 4 |
ALTER TABLE Customers -- Оператор ALTER TABLE изменяет таблицу DROP COLUMN -- DROP COLUMN - удаляет NewFild -- столбец. GO |
Установив значение по умолчанию
|
1 2 3 4 5 |
ALTER TABLE Customers ADD NewFild2 varchar(10) NOT NULL DEFAULT 'Unknown' GO |
Целостность баз данных
Целостность базы данных — свойство базы данных обеспечивающее корректность и непротиворечивость хранимых данных в любой момент времени Целостность БД не гарантирует достоверности содержащейся в ней информации, но обеспечивает по крайней мере правдоподобность этой информации, отвергая заведомо невероятные, невозможные значения.
Целостность базы данных обеспечивается ограничениями на вводимые пользователем данные.
Типы целостности базы данных:
- Доменная целостность
- Целостность сущностей
- Ссылочная целостность
Доменная целостность
Доменная целостность гарантирует наличие в некотором столбце только допустимых значений.
Ограничения, обеспечивающие доменную целостности:
- Использование ограничений проверки(CHECK).
- Использование ограничений на умолчания(DEFAULT).
Целостность сущностей
Целостность сущностей гарантирует уникальность записей в таблицах(сущностях).
Ограничения, обеспечивающие целостность сущностей:
- Использование ограничений первичного ключа(Primary Key).
- Использование ограничений на уникальность(UNIQUE).
Primary Key
Primary Key (Первичный Ключ) – предоставляет ссылку для связи с другими таблицами, а так же задает ограничение уникальности для столбца на котором задается.
Первичный ключ (Primary Key) — это атрибут или группа атрибутов, однозначно идентифицирующих экземпляр сущности. На диаграмме первичные ключи размещаются выше горизонтальной линии. Ключ может быть сложным, т.е. состоять из нескольких атрибутов.
Создание первичного ключа
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Customers ( CustomerNO int NOT NULL PRIMARY KEY, -- На столбце CustomerNO задаем первичный ключ CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) ) GO |
пример использования:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
INSERT INTO Customers (CustomerNO, CustomerName, Address1, City, Contact, Phone) VALUES (1,'Петренко Петр Петрович', 'Луганская 25', 'Конотоп', 'PetrPetrenko@mail.ru', '(093)1231212'), (2,'Иваненко Иван Иванович', 'Дихтяревская 5', 'Чернигов', 'IvanenkoIvan@gmail.com', '(095)2313244'); GO -- Попытка нарушить уникальность. INSERT INTO Customers (CustomerNO, CustomerName, Address1, City, Contact, Phone) VALUES (1,'Сидоров Семен Семенович', 'Драйзера 12', 'Харьков', 'SemSem@mail.ru', '(063)4533272'); -- Ошибка! |
Если первичный ключ состоит из двух и более столбцов, его называют составным первичный ключом. Попытка вставить второй Первичный ключ в таблицу приводит к ошибке. Первичный ключ в таблице может быть только один (простой или составной)
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Customers ( CustomerNo int NOT NULL, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) NOT NULL, PRIMARY KEY (CustomerNo, CustomerName) -- задаем составной первичный ключ на столбцах CustomerNo, CustomerName ) |
пример:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
INSERT INTO Customers (CustomerNO, CustomerName, Address1, City, Contact, Phone) VALUES (1,'Петренко Петр Петрович', 'Луганская 25', 'Конотоп', 'PetrPetrenko@mail.ru', '(093)1231212'), (2,'Иваненко Иван Иванович', 'Дихтяревская 5', 'Чернигов', 'IvanenkoIvan@gmail.com', '(095)2313244'); GO INSERT INTO Customers (CustomerNO, CustomerName, Address1, City, Contact, Phone) VALUES (1,'Сидоров Семен Семенович', 'Драйзера 12', 'Харьков', 'SemSem@mail.ru', '(063)4533272'); INSERT INTO Customers (CustomerNO, CustomerName, Address1, City, Contact, Phone) VALUES (2,'Иваненко Иван Иванович', 'Янгеля 32', 'Киев', 'IvanenkoIvan@mail.ru', '(050)1752124'); -- Ошибка! |
Изменяем таблицу Customers задав ограничение первичного ключа на столбце CustomerNo
|
1 2 3 |
ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerNo) |
|
1 2 3 |
ALTER TABLE Customers ADD -- Имя ограничения не является обязательным. PRIMARY KEY (CustomerNo,Address1) |
Ссылочная целостность
Ссылочная целостность гарантирует невозможность создания записи в одной таблице, которая ссылается на несуществующую запись другой таблицы.
Ограничения обеспечивающие ссылочную целостность:
- Использование ограничений внешнего ключа(Foreign Key).
Foreign Key
Foreign Key (Внешний Ключ) – задает столбец который ссылается на ограничение Primary Key или UNIQUE. Ограничение Foreign Key запрещает вводить данные не существующие в ссылочных столбцах (столбцах с ограничением Primary Key или UNIQUE).
Внешние ключи (Foreign Key) создаются автоматически, когда сущности соединяются связью (миграция ключа). Связи между таблицами реляционной БД представляются
одинаковыми ключами в таблицах (внешними ключами).
Создание внешнего ключа
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Orders ( OrderID int IDENTITY NOT NULL PRIMARY KEY, CustomerNo int NOT NULL -- Столбец дочерней таблицы для которой задан FOREIGN KEY FOREIGN KEY REFERENCES Customers(CustomerNo), -- Родительская таблица Customers ее столбец CustomerNo. OrderDate date NOT NULL, Goods varchar(30) NOT NULL ) GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO Customers (CustomerName, Address1, City, Contact, Phone) VALUES ('Petrenko Petr Petrovich', 'Luganskaya 25', 'Konotop', 'PetrPetrenko@mail.ru', '(093)1231212'), ('Ivanenko Ivan Ivanovich', 'Dehtarivska 5', 'Chernigov', 'IvanenkoIvan@gmail.com', '(095)2313244'); INSERT INTO Orders (CustomerNo, OrderDate, Goods) VALUES (1, GETDATE(), 'KeyBoard'), (2, GETDATE(), 'Mouse'), (2, GETDATE(), 'WebCam'), (1, GETDATE(), 'Mouse'); |
Не допускается запись в ссылаемый столбец (столбец с FOREIGN KEY) дочерней таблици, значений несуществующих в ссылочном столбце (столбец с PRIMARY KEY) родительской таблицы.
|
1 2 3 4 5 6 7 |
INSERT INTO Orders (CustomerNo, OrderDate, Goods) VALUES (3, GETDATE(), 'Mouse'); -- Ошибка! DROP TABLE Orders; GO |
Создание ограничения внешнего ключа на существующей таблице
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE Orders ( OrderID int IDENTITY NOT NULL PRIMARY KEY, CustomerNo int NOT NULL, OrderDate date NOT NULL, Goods varchar(30) NOT NULL ) ALTER TABLE Orders ADD CONSTRAINT FK_CustomersCustomerNo FOREIGN KEY (CustomerNo) REFERENCES Customers(CustomerNo); |
Удаление ограничения внешнего ключа. FK_CustomersCustomerNo — имя по умолчанию
|
1 2 3 |
ALTER TABLE Orders DROP CONSTRAINT FK_CustomersCustomerNo; GO |
Entity-Relationship
Диаграммы «сущность-связь» (Entity-Relationship) предназначены для разработки моделей данных и обеспечивают стандартный способ определения данных и отношений между ними.
Дочерняя и Родительская таблицы
Таблица содержащая FK называется дочерней таблицей по отношению к ссылаемой таблице (родительской таблице) содержащей первичный ключ.
Родительская таблица – таблица на которую ссылаются.
Дочерняя таблица – таблица которая ссылается.
СВЯЗИ-Relations
Связь — это некоторая ассоциация между двумя таблицами, реализованная при помощи пары FK -> PK или FK -> UNIQUE.
Одна таблица может быть связана с другой таблицей или сама с собою. Связи позволяют по одной сущности находить другие сущности, связанные с нею
Типы связей
Один к одному
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Customers ( CustomerNO int IDENTITY NOT NULL PRIMARY KEY, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) ) GO |
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Orders ( OrderID int IDENTITY NOT NULL PRIMARY KEY, CustomerNo int NOT NULL UNIQUE FOREIGN KEY REFERENCES Customers(CustomerNo), -- Связь Один к Одному. OrderDate date NOT NULL, Goods varchar(30) NOT NULL ) GO |
|
1 2 3 4 5 |
INSERT INTO Customers (CustomerName, Address1, City, Contact, Phone) VALUES ('Петренко Петр Петрович', 'Луганская 25', 'Конотоп', 'PetrPetrenko@mail.ru', '(093)1231212'), ('Иваненко Иван Иванович', 'Дихтяревская 5', 'Чернигов', 'IvanenkoIvan@gmail.com', '(095)2313244'); |
|
1 2 3 4 5 |
INSERT INTO Orders (CustomerNo, OrderDate, Goods) VALUES (1, GETDATE(), 'KeyBoard'), (2, GETDATE(), 'Mouse'); |
Один ко многим
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Customers ( CustomerNO int IDENTITY NOT NULL PRIMARY KEY, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) ) GO |
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Orders ( OrderID int IDENTITY NOT NULL PRIMARY KEY, CustomerNo int NOT NULL FOREIGN KEY REFERENCES Customers(CustomerNo), -- Связь Один ко Многим. OrderDate date NOT NULL, Goods varchar(30) NOT NULL ) GO |
|
1 2 3 4 5 |
INSERT INTO Customers (CustomerName, Address1, City, Contact, Phone) VALUES ('Петренко Петр Петрович', 'Луганская 25', 'Конотоп', 'PetrPetrenko@mail.ru', '(093)1231212'), ('Иваненко Иван Иванович', 'Дихтяревская 5', 'Чернигов', 'IvanenkoIvan@gmail.com', '(095)2313244'); |
|
1 2 3 4 5 6 7 |
INSERT INTO Orders (CustomerNo, OrderDate, Goods) VALUES (1, GETDATE(), 'KeyBoard'), (2, GETDATE(), 'Mouse'), (2, GETDATE(), 'WebCam'), (1, GETDATE(), 'Mouse'); |
Многие ко многим
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Customers ( CustomerNO int IDENTITY NOT NULL PRIMARY KEY, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) ) GO |
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Orders ( OrderID int IDENTITY NOT NULL PRIMARY KEY, CustomerNo int NOT NULL FOREIGN KEY REFERENCES Customers(CustomerNo), -- Связь Один ко Многим. OrderDate date NOT NULL, Goods varchar(30) NOT NULL ) GO |
|
1 2 3 4 5 6 7 |
CREATE TABLE CreditCards ( CardID bigint UNIQUE NOT NULL, CVV int NOT NULL, CustomerNo int NOT NULL FOREIGN KEY REFERENCES Customers(CustomerNo) -- Связь Один ко Многим. ) |
|
1 2 3 4 5 |
INSERT INTO Customers (CustomerName, Address1, City, Contact, Phone) VALUES ('Петренко Петр Петрович', 'Луганская 25', 'Конотоп', 'PetrPetrenko@mail.ru', '(093)1231212'), ('Иваненко Иван Иванович', 'Дихтяревская 5', 'Чернигов', 'IvanenkoIvan@gmail.com', '(095)2313244'); |
|
1 2 3 4 5 6 7 |
INSERT INTO Orders (CustomerNo, OrderDate, Goods) VALUES (1, GETDATE(), 'KeyBoard'), (2, GETDATE(), 'Mouse'), (2, GETDATE(), 'WebCam'), (1, GETDATE(), 'Mouse'); |
|
1 2 3 4 5 6 7 |
INSERT INTO CreditCards VALUES (1233431241,232,1), (2312312349,097,2), (4323456732,876,2), (5372168424,937,1), (7643354521,526,1); |
|
1 2 3 |
SELECT * FROM Orders; SELECT CustomerNO, CustomerName, Address1, City FROM Customers; SELECT * FROM CreditCards; |
Ограничения ссылочной целостности
С помощью ограничений ссылочной целостности (ON DELETE, ON UPDATE) можно определять действия, которые SQL Server будет производить над строками дочерней таблицы, когда пользователь попытается удалить или обновить ключевой столбец родительской таблицы.
Действия:CASCADE — при UPDATE, DELETE в родительской таблице ключевого значения,в дочерней таблице также выполняется UPDATE на новое значение, или DELETE строк из дочерней таблицы соответственно.
Изменяем ID в таблице:
|
1 2 |
DBCC CHECKIDENT("Customers",RESEED,0); -- Обновление значение IDENTITY = 0 GO |
SET NULL — при UPDATE, DELETE в родительской таблице ключевого значения, в дочерней таблице все значения, составляющие эти внешние ключи, будут изменены на NULL
|
1 2 3 4 5 |
ALTER TABLE Orders ADD CONSTRAINT FK_CustomersCustomerNo FOREIGN KEY (CustomerNo) REFERENCES Customers(CustomerNo) ON DELETE SET NULL GO |
SET DEFAULT — при UPDATE, DELETE в родительской таблице ключевого значения, в дочерней таблице все значения, составляющие эти внешние ключи, будут изменены на значение по умолчанию.
|
1 2 3 4 5 |
ALTER TABLE Orders ADD CONSTRAINT FK_CustomersCustomerNo FOREIGN KEY (CustomerNo) REFERENCES Customers(CustomerNo) ON DELETE SET DEFAULT GO |
NO ACTION (по умолчанию) — запрещает выполнение UPDATE, DELETE!
|
1 2 3 4 5 |
ALTER TABLE Orders ADD CONSTRAINT FK_CustomersCustomerNo FOREIGN KEY (CustomerNo) REFERENCES Customers(CustomerNo) ON DELETE NO ACTION GO |
Пользовательские ограничения
Создаем шаблон, при котором можно будет вводить только цифры и не более 12 штук.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Customers ( CustomerNO int IDENTITY NOT NULL, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, -- Пользовательское ограничение Phone char(12) CHECK (Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) GO |
UNIQUE (алтернативный ключ), значение в ячейке должно быть уникальное
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Customers ( CustomerNO int IDENTITY NOT NULL, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) UNIQUE ) GO |
Возможно задать сразу несколько ограничений в произвольном порядке
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Customers ( CustomerNO int IDENTITY NOT NULL, CustomerName varchar(25) NOT NULL, Address1 varchar(25) NOT NULL, City varchar(15) NOT NULL, Contact varchar(25) NOT NULL, Phone char(12) UNIQUE CHECK (Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) GO |
Создание пользовательского ограничения на существующей таблице
|
1 2 3 4 5 |
ALTER TABLE Customers ADD CONSTRAINT CN_CustomersPhoneNo -- Ограничение CHECK CHECK (Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') GO |
Отключение работы ограничения CHECK
|
1 2 |
ALTER TABLE Customers NOCHECK CONSTRAINT CN_CustomersPhoneNo; |
Включение работы ограничения CHECK
|
1 2 |
ALTER TABLE Customers CHECK CONSTRAINT CN_CustomersPhoneNo; |
Удаление ограничения CHECK
|
1 2 |
ALTER TABLE Customers DROP CONSTRAINT CN_CustomersPhoneNo; |
Важно! После удаления не возможен возврат ограничения CHECK