Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
FOREIGN KEY (КодТовара) REFERENCES товар
ON UPDATE NO ACTION ON DELETE NO ACTION
Пример 9.7. Добавления ограничения внешнего ключа, реализующего
каскадные обновления и изменения:
ALTER TABLE Сделка ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара) REFERENCES Товар
ON UPDATE CASCADE ON DELETE CASCADE
Пример 9.8. Пример создания и удаления вычисляемого поля.
ALTER TABLE Товар ADD Налог AS Цена*0.05
ALTER TABLE Товар DROP COLUMN Налог
Пусть создана таблица без ограничений:
CREATE TABLE Товар
(КодТовара INT,
Название VARCHAR(20),
Тир VARCHAR(20),
Дата DATETIME,
Цена MONEY,
Остаток INT)
Рассмотрим примеры внесения в таблицу всевозможных ограничений.
Пример 9.9. Поле КодТовара необходимо сделать первичным клю-
чом. Выполнение следующей команды будет отвергнуто, поскольку поле
КодТовара допускает внесение значений NULL.
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Сначала нужно изменить объявление столбца КодТовара, запретив
внесение значений NULL:
ALTER TABLE Товар ALTER COLUMN КодТовара INT NOT NULL
И только потом создать ограничение первичного ключа:
ALTER TABLE Товар ADD CONSTRAINT pk1 PRIMARY KEY(Код)
201
Лекция 9 Определение ограничений целостности
Пример 9.10. Удалить столбец целого типа и добавить столбец-счетчик
.
ALTER TABLE Товар DROP COLUMN КодТовара
ALTER TABLE Товар ADD КодТовара INT IDENTITY(1,1)
Пример 9.11. Добавить ограничение первичного ключа.
ALTER TABLE Товар ADD CONSTRAINT pk1 PRIMARY
KEY(КодТовара)
Пример 9.12. Изменить столбец, добавив ограничение NOT NULL.
ALTER TABLE Товар ALTER COLUMN
Название VARCHAR(40) NOT NULL
Пример 9.13. Добавить ограничение уникальности значения.
ALTER TABLE Товар ADD CONSTRAINT u1 UNIQUE(Название)
Пример 9.14. Создать умолчание и добавить умолчание столбцу.
CREATE DEFAULT df1 AS 0
sp_bindefault 'df1', 'Товар.Остаток'
CREATE DEFAULT df2 AS GETDATE()
sp_bindefault 'df2', 'Товар.Дата'
Пример 9.15. Создать правило и добавить правило столбцу.
CREATE RULE r1 AS @m IN ('мебель',
'бытовая химия','косметика')
sp_bindrule 'r1','Товар.Тип'
Курс
202
Основы SQL
Вариант 1
1. Даны две таблицы. Определить в них первичные ключи.
ALTER TABLE Автор
ALTER COLUMN Паспорт CHAR(12) NOT NULL
ALTER TABLE Автор
ADD CONSTRAINT pk1 PRIMARY KEY (Паспорт)
ALTER TABLE Книга ADD УДК CHAR(10)
ALTER TABLE Книга ALTER COLUMN УДК CHAR(10) NOT NULL
ALTER TABLE Книга ADD CONSTRAINT pk2 PRIMARY KEY (УДК)
ALTER TABLE Автор
ADD CONSTRAINT pk1 PRIMARY KEY (паспорт)
ALTER TABLE Книга ADD УДК CHAR(10) NOT NULL
ALTER TABLE Книга ADD CONSTRAINT pk2 PRIMARY KEY (УДК)
ALTER TABLE Автор
ADD CONSTRAINT pk1 PRIMARY KEY (Фамилия, Имя)
ALTER TABLE Книга
ADD УДК CHAR(10) NOT NULL PRIMARY KEY
ALTER TABLE Автор
ALTER COLUMN паспорт INT NOT NULL PRIMARY KEY
ALTER TABLE Книга ADD УДК CHAR(10) PRIMARY KEY
203
Лекция 9 Определение ограничений целостности
CREATE TABLE Автор
(Паспорт INT,
Фамилия VARCHAR(50) NULL,
Имя VARCHAR(50) NULL,
Отчество VARCHAR(50) NULL,
Пол CHAR(7),
Возраст INT,
Телефон INT)
CREATE TABLE Книга
(Название VARCHAR(50),
Цена MONEY,
Тематика VARCHAR(50),
Издательство VARCHAR(50),
Количество INT)
2. Даны таблицы Автор и Книга. Между ними необходимо
установить связь «один-ко-многим» (один автор – много
книг) с учетом первичных ключей, определенных в
Задании 1.
ALTER TABLE Книга ADD Автор CHAR(12)
ALTER TABLE Книга ALTER COLUMN Автор CHAR(12) NOT NULL
ALTER TABLE Книга ADD CONSTRAINT fk_Книга
FOREIGN KEY(Автор) REFERENCES Автор(паспорт)
ALTER TABLE Книга ADD Паспорт CHAR(12)
ALTER TABLE Книга ADD FOREIGN KEY(Паспорт)
REFERENCES Автор
ALTER TABLE Книга ADD Паспорт CHAR(12)
ALTER TABLE Автор ADD FOREIGN KEY(Паспорт)
REFERENCES Книга
ALTER TABLE Книга
ADD Автор CHAR(12) FOREIGN KEY REFERENCES Автор
3. В таблице Книга ограничить тематику заданным списком:
роман, фантастика, поэзия.
ALTER TABLE Книга ADD CONSTRAINT ch1
CHECK(тематика IN ('фантастика','роман','поэзия'))
ALTER TABLE Книга ADD
CHECK(тематика IN ('фантастика','роман','поэзия'))
ALTER TABLE Книга ALTER тематика VARCHAR(20)
CHECK(тематикаIN ('фантастика','роман','поэзия'))
ALTER TABLE Книга ADD CHECK(тематика='фантастика'
OR 'роман'OR 'поэзия')
4. Для поля Пол таблицы Автор создать и применить правило
для ввода только значений «Мужской» и «Женский».
CREATE RULE sex AS @p IN ('Мужской','Женский')
sp_bindrule 'sex', 'Автор.Пол'
CREATE RULE sex AS @p='Мужской' OR @p='Женский'
sp_bindrule 'sex', 'Автор.Пол'
CREATE RULE sex AS @p='Мужской' OR @p='Женский'
sp_bindrule 'sex', 'Пол'
CREATE RULE sex (Автор.Пол IN ('Мужской','Женский'))
sp_bindrule 'sex', 'Автор.Пол'
Курс
204
Основы SQL
Вариант 2
1. Даны две таблицы. Определить в них первичные ключи.
ALTER TABLE Рейс ALTER COLUMN Номер_рейса INT NOT NULL
ALTER TABLE Рейс
ADD CONSTRAINT pk1 PRIMARY KEY (Номер_рейса)
ALTER TABLE Билет
ALTER COLUMN Номер_места INT NOT NULL
ALTER TABLE Билет
ALTER COLUMN Номер_рейса INT NOTNULL
ALTER TABLE Билет ADD CONSTRAINT pk2
PRIMARY KEY (Номер_рейса, номер_места)
ALTER TABLE Рейс ALTER COLUMN Номер_рейса INT NOT NULL
ALTER TABLE Рейс
ADD CONSTRAINT pk1 PRIMARY KEY (Номер_рейса)
ALTER TABLE Билет ALTER COLUMN Номер_места INT NOT NULL
ALTER TABLE Билет ADD CONSTRAINT pk2
PRIMARY KEY (номер_места)
205
Лекция 9 Определение ограничений целостности
CREATE TABLE Рейс
(Номер_рейса INT,
Конечный_пункт VARCHAR(20),
Дата_вылета DATETIME,
Продолжительность_маршрута INT,
Число_билетов INT,
Стоимость MONEY)
CREATE TABLE Билет
(Номер_места INT,
Номер_рейса INT,
Дата_продажи DATETIME,
Стоимость MONEY,
Фамилия_пассажира VARCHAR(30))
ALTER TABLE Рейс ALTER COLUMN Номер_рейса INT NOT NULL
ALTER TABLE Рейс ADD CONSTRAINT pk1
PRIMARY KEY (Номер_рейса)
ALTER TABLE Билет ALTER COLUMN Номер_места INT NOT NULL
ALTER TABLE Билет ALTER COLUMN Номер_рейса INT NOT NULL
ALTER TABLE Билет ADD CONSTRAINT pk2
PRIMARY KEY (Номер_рейса)
ALTER TABLE Билет ADD CONSTRAINT pk2
PRIMARY KEY (номер_места)
ALTER TABLE Рейс ALTER COLUMN Номер_рейса INT NOT NULL
ALTER TABLE Рейс ADD PRIMARY KEY (Номер_рейса)
ALTER TABLE Билет ALTER COLUMN Номер_места INT NOT NULL
ALTER TABLE Билет ALTER COLUMN Номер_рейса INT NOT NULL
ALTER TABLE Билет
ADD PRIMARY KEY (Номер_рейса,номер_места)
2. Даны таблицы Рейс и Билет. Между ними необходимо
установить связь «один-ко-многим» (один рейс – много
мест) с учетом первичных ключей, определенных в
Задании 1.
ALTER TABLE Билет ADD CONSTRAINT fk_Билет
FOREIGN KEY(номер_рейса) REFERENCES Рейс
ALTER TABLE Билет ADD FOREIGN KEY(Номер_рейса)
REFERENCES Рейс
ALTER TABLE Рейс ADD FOREIGN KEY(Номер_рейса)
REFERENCES Билет
ALTER TABLE Рейс ADD Номер_места INT NOT NULL
FOREIGN KEY(Номер_места) REFERENCES Билет
Курс
206
Основы SQL
3. В таблице Рейс ограничить конечный пункт заданным спи-
ском: Москва, Самара, Киев.
ALTER TABLE Рейс ADD CONSTRAINT ch1
CHECK(конечный_пункт IN ('Москва','Самара','Киев'))
ALTER TABLE РейсADD CHECK(конечный_пунктIN
('Москва','Самара','Киев'))
ALTER TABLE Рейс ALTER конечный_пункт VARCHAR(20)
CHECK(конечный_пунктIN ('Москва','Самара','Киев'))
ALTER TABLE Рейс ADD CHECK(конечный_пункт='Москва'
OR 'Самара'OR 'Киев')
4. Для полей Дата_продажи таблицы Билет и Дата_вылета
таблицы Рейс создать и применить умолчание для ввода
текущей даты.
CREATE DEFAULT df1 AS GETDATE()
sp_bindefault 'df1', 'Билет.Дата_продажи'
sp_bindefault 'df1', 'Рейс.Дата_вылета'
CREATE DEFAULT df1 AS GETDATE()
sp_bindefault 'df1', 'Билет.Дата_продажи',
'Рейс.Дата_вылета'
CREATE Билет.Дата_продажи DEFAULT GETDATE()
CREATE Рейс.Дата_вылета DEFAULT GETDATE()
CREATE DEFAULT df1 AS GETDATE()
sp_bindefault 'df1',’Билет’, 'Дата_продажи'
sp_bindefault 'df1',’Рейс’, 'Дата_вылета'
207
Лекция 9 Определение ограничений целостности
Вариант 3
1. Даны две таблицы. Определить в них первичные ключи.
ALTER TABLE Блюдо
ALTER COLUMN Название_блюда VA
CHAR(20) NOT NULL
ALTER TABLE Блюдо
ADD CONSTRAINT pk1 PRIMARY KEY (Название_блюда)
ALTER TABLE Компонент
ALTER COLUMN Название_компонента VARCHAR(20) NOT NULL
LTER TABLE Компонент ADD CONSTRAINT pk2
PRIMARY KEY (Название_компонента)
ALTER TABLE Блюдо
ALTER COLUMN Название_блюда VA CHAR(20) NOT NULL
ALTER TABLE Блюдо ADD PRIMARY KEY (Название_блюда)
ALTER TABLE Компонент
ALTER COLUMN Название_компонента VARCHAR(20) NOT NULL
LTER TABLE Компонент
ADD PRIMARY KEY (Название_компонента)
ALTER TABLE Блюдо ADD КодБлюда INT PRIMARY KEY
ALTER TABLE Компонент ADD КодБлюда INT PRIMARY KEY
ALTER TABLE Блюдо ADD PRIMARY KEY (Название_блюда)
ALTER TABLE Компонент
ALTER COLUMN Название_компонента VARCHAR(20) NOT NULL
ALTER TABLE Компонент
ADD COLUMN Блюдо VARCHAR(20) NOT NULL
ALTER TABLE Компонент
ADD PRIMARY KEY (Название_компонента, Блюдо)
Курс
208
Основы SQL
CREATE TABLE Блюдо
(Название_блюда VARCHAR(20),
Время_приготовления INT,
Общая_калорийность INT,
Повар VARCHAR(20),
Стоимость MONEY,
Тип_блюда VARCHAR(20))
CREATE TABLE Компонент
(Название_компонента VARCHAR(20),
Калорийность INT,
Стоимость_компонента FLOAT )
2. Даны таблицы Блюдо и Компонент. Между ними необходи-
мо установить связь «один-ко-многим» (одно блюдо –
много компонент) с учетом первичных ключей,
определенных в Задании 1.
ALTER TABLE Компонент ADD Блюдо VARCHAR(20)
ALTER TABLE Компонент
ALTER COLUMN Блюдо VARCHAR(20) NOT NULL
ALTER TABLE Компонент ADD CONSTRAINT fk_Компонент
FOREIGN KEY(Блюдо) REFERENCES Блюдо(Название_блюда)
ALTER TABLE Компонент ADD Название_блюда VARCHAR(20)
ALTER TABLE Компонент ADD FOREIGN
KEY(Название_блюда) REFERENCES Блюдо
ALTER TABLE Компонент
ADD COLUMN Название_блюда VARCHAR(20) NOT NULL
ALTER TABLE Блюдо ADD
FOREIGN KEY(Название_компонента) REFERENCES Компонент
ALTER TABLE Компонент
ADD COLUMN Название_блюда VARCHAR(20) FOREIGN KEY
REFERENCES Блюдо
3. В таблице Блюдо ограничить тип блюда заданным
списком: десерт, выпечка, закуска.
ALTER TABLE Блюдо ADD CONSTRAINT ch1
CHECK(тип_блюда IN (десерт', 'выпечка', 'закуска'))
ALTER TABLE БлюдоADD
CHECK(тип_блюдаIN (десерт', 'выпечка','закуска'))
ALTER TABLE БлюдоALTER тип_блюдаVARCHAR(20)
CHECK(тип_блюдаIN (десерт', 'выпечка','закуска'))
ALTER TABLE Блюдо ADD CHECK(тип_блюда='десерт'
OR 'закуска'OR 'выпечка')
209
Лекция 9 Определение ограничений целостности
4.
Для полей Стоимость в таблице Блюдо и
Стоимость_компонента в таблице Компонент создать и
применить правило ввода значений больше 0 и меньше 100.
CREATE RULE r1 AS @p >0 AND @p<100
sp_bindrule 'r1', 'Блюдо.Стоимость'
sp_bindrule 'r1', 'Компонент.Стоимость_компонента'
CREATE RULE r1 AS @p BETWEEN 1 AND 99
sp_bindrule 'r1', 'Блюдо.Стоимость'
sp_bindrule 'r1', 'Компонент.Стоимость_компонента'
CREATE RULE r1 AS @p >0 AND @p<100
sp_bindrule 'r1', 'Блюдо’,’Стоимость'
sp_bindrule 'r1', 'Компонент’,’Стоимость_компонента'
CREATE RULE r1 AS Блюдо.Стоимость BETWEEN 1 AND 99
CREATE RULE r1 AS Компонент.Стоимость_компонента
BETWEEN 1 AND 99
Курс
210
Основы SQL