Какие типы ограничений целостности вы знаете?
Выполнение работы
create table faculty
(
facpk integer primary key,
name varchar2(50) UNIQUE NOT NULL,
deanfk integer,
building char(2)
check (building in ('1','2','3','4','5','6','7','8','9','10')),
fund number(7,2) check(fund>100000)
);
create table department
(
deppk integer primary key,
facfk integer ,
name varchar2(50) not null,
headfk integer,
building char(2)
check (building in ('1','2','3','4','5','6','7','8','9','10')),
fund number(7,2) check (fund between 20000 and 100000),
constraints dpe_fk_on_fac foreign key(facfk) references faculty(facpk),
constraints uni_facfk_name unique(facfk,name)
);
create table teacher
(
tchpk integer primary key,
depfk integer,
name varchar2(50) not null,
post varchar2(20) check(post in ('assistant', 'counselor', 'assistant professor', 'professor')),
tel char(7),
hiredate date constraints teach_constr not null check (hiredate >= TO_DATE('01.01.1950', 'DD.MM.YYYY')),
salary number(6,2) constraints teach_sal_constr not null check(salary > 1000),
commission number (6,2) default 0 check(commission >= 0),
chieffk integer,
constraint teach_fk_on_dep foreign key(depfk) references department(deppk) on delete set null,
constraint teach_fk_on_teach foreign key(chieffk) references teacher(tchpk) on delete set null,
constraint commi_constr check (commission <= salary/2),
constraints com_sal_constr check (commission + salary between 1000 and 3000),
constraint chief_constr check(chieffk<>tchpk)
);
create table sgroup
(
grppk integer primary key,
depfk integer,
course number(1) check(course in (1,2,3,4,5,6)),
num number(3) check (num between 1 and 700),
quantity number(2) check (quantity between 1 and 50),
curator integer,
rating number(3) default 0 check(rating between 0 and 100),
constraints conn1 foreign key(depfk) references department(deppk) on delete set null,
constraints conn2 foreign key(curator) references teacher (tchpk) on delete set null,
constraints conn3 unique(depfk,num),
constraints conn654 unique(depfk,curator)
);
create table subject
(
sbjpk integer primary key,
name varchar2(50) not null
);
create table room
(
rompk integer primary key,
num number(4) not null,
seats number(3) check (seats between 1 and 300),
floor number(2) check(floor between 1 and 16),
building char(5) not null check (building in('1','2','3','4','5','6','7','8','9','10')),
constraints room_constr unique(num,building)
);
create table lecture
(
tchfk integer,
grpfk integer,
sbjfk integer,
romfk integer,
type varchar2(25) not null check (type in('lecture', 'lab', 'seminar', 'practice')),
day char(3) not null check(day in ('sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat')),
week number(1) not null check(week = 1 or week = 2),
lesson number(1) not null check(lesson between 1 and 8),
constraints conn14 foreign key(tchfk) references teacher(tchpk) on delete set null,
constraints conn15 foreign key(grpfk) references sgroup(grppk) on delete cascade,
constraints conn16 foreign key(sbjfk) references subject(sbjpk),
constraints conn17 foreign key(romfk) references room(rompk) on delete set null,
constraints conn18 unique(Grpfk, Day, Week, Lesson),
constraints conn19 unique (tchfk, Day, Week, Lesson)
);
Ответы на вопросы
Какова цель ограничений целостности?
Вы можете определить ограничения целостности на данные в таблицах для поддержания ограничений, действующих в вашей предметной области. После того, как описанные ограничения целостности вступят в силу, все данные в таблицах должны соответствовать указанным ограничениям. Если вы в дальнейшем вводите или модифицируете данные в таблицах, Oracle будет гарантировать, что результирующие данные будут удовлетворять указанным ограничениям. Если такие ограничения не указаны, то их можно будет поддерживать только программным способом в вашем приложении.
Какие типы ограничений целостности вы знаете?
В SQL Oracle имеются следующие ограничения целостности:
[NOT] NULL Это ограничение задается для столбца и разрешает или запрещает наличие неопределенных значений в столбце при вводе или изменении его значений.
UNIQUE Это ограничение задается для столбца или совокупности столбцов и указывает, что значение столбца (совокупности столбцов) должно быть уникальным в пределах таблицы. Называется ограничением уникального ключа.
PRIMARY KEY Это ограничение указывает, что столбец или совокупность столбцов выступают в качестве первичного ключа, который уникально идентифицирует строки таблицы.
FOREIGN KEY Это ограничение указывает, что столбец или совокупность столбцов принимают значения, которые должны совпадать со значениями столбца (совокупности столбцов) из другого связанной таблицы. Это так называемое референциальное ограничение целостности.
Референциальное ограничение целостности также включает правила, которые указывают, какие типы манипулирования данными допустимы на те значения, на которые делается ссылка, и как такие изменения влияют на зависимые данные. Правилами, связанными с референциальным ограничением целостности, являются следующие:
CHECK Описание сложных правил ограничений целостности.
3)Какие синтаксические формы ограничений целостности вы знаете? В чем различие между ними?
Имеются две синтаксические формы определения ограничений целостности: ограничение на столбец и ограничение на таблицу.
Ограничение на столбец специфицирует ограничения, которые относятся к значению конкретного столбца. Ограничения на таблицу специфицируют ограничения относительно нескольких столбцов таблицы и задаются вне контекста описания конкретного столбца.
Что означает ограничение UNIQUE? Что такое составной уникальный ключ? Какой синтаксис может использоваться для описания составного уникального ключа (синтаксис ограничения столбца или синтаксис ограничения таблицы)? Может ли столбец с ограничением UNIQUE принимать значения NULL? Можно ли одновременно устанавливать ограничения UNIQUE и PRIMARY KEY для одного и того же столбца (совокупности столбцов)?
UNIQUE Указывает, что столбец или совокупность столбцов должны удовлетворять ограничению UNIQUE. Ограничение UNIQUE указывает, что ни какие две строки таблицы не могут содержать одинаковых значений столбца(ов) с этим ограничением. Однако уникальный ключ, состоящий из елинственного столбца, может содержать NULL значения.
Составной уникальный ключ состоит из совокупности столбцов. Для определения составного уникального ключа необходимо воспользоваться синтаксисом table_constraint, а не синтаксисом column_constraint. Считается, что строка, содержащая NULL значения во всех столбцах составного уникального ключа, удовлетворяет этому ограничению. Однако строки, которые имеют NULL значения для одних из столбцов составного уникального ключа, и одинаковые совокупности значений для оставшихся столбцов, нарушают это ограничение.
Вы не можете использовать один и тот же столбец (совокупность столбцов) в качестве уникального ключа и первичного ключа одновременно.
5) Что означает ограничение PRIMARY KEY? Что такое составной первичный ключ? Какой синтаксис может использоваться для описания составного первичного ключа (синтаксис ограничения столбца или синтаксис ограничения таблицы)? Сколько первичных ключей может содержать таблица? Может ли столбец с ограничением PRIMARY KEY принимать значения NULL?
PRIMARY KEY Указывает, что столбец или совокупность столбцов являются первичным ключом. Составной первичный ключ состоит из совокупности столбцов. Для определения составного первичного ключа следует воспользоваться синтаксисом table_constraint, а не синтаксисом column_constraint.
Таблица может содержать только один первичный ключ.
Значение первичного ключа не может повторяться в пределах таблицы.
Первичные ключи не могут содержать NULL значений.
Вы не можете использовать один и тот же столбец (совокупность столбцов) в качестве уникального ключа и первичного ключа одновременно.
6)Что означает ограничение NULL (NOT NULL)? Какое значение по умолчанию принимается, если это ограничение не указывается явно? Можно ли задавать это ограничение в синтаксисе ограничения таблицы?
NULL NOT NULL Указывает, может ли столбец содержать неопределенные значения. Ограничения NULL и NOT NULL могут задаваться только в синтаксисе column_constraint.
NULL Указывает, что столбец может содержать неопределенные значения. Если вы не указываете ни NOT NULL ни NULL, то по умолчанию столбец может содержать неопределенные значения.
NOT NULL Указывает, что столбец не может содержать неопределенных значений. Для удовлетворения этого ограничения любая стока таблицы должна иметь значение этого столбца.
7)Что означает ограничение целостности FOREIGN KEY (референциальное ограничение целостности)? Определите следующие понятия: референциальный ключ, внешний ключ, родительская таблица, дочерняя таблица. Какие ограничением целостности должна обладать тот столбец (совокупность столбцов), на который делается ссылка из внешнего ключа? Можно ли определить референциальный ключ и внешний ключ в одной и той же таблице? Что означает составной внешний ключ? Какой синтаксис может использоваться для описания составного внешнего ключа (синтаксис ограничения столбца или синтаксис ограничения таблицы)? Можно ли определить внешний ключ в одним столбцом в синтаксисе ограничения таблицы? Можно ли использовать один и тот же столбец (совокупность столбцов) в качестве первичного ключа и внешнего ключа? Можно ли использовать один и тот же столбец (совокупность столбцов) в качестве внешнего ключа и уникального ключа? Можно ли несколько внешних ключей в одной таблице? Можно ли устанавливать значение NULL для вненего ключа (составного внешнего ключа)? Можно ли определить референциальное ограничение целостности в предложении CREATE TABLE, которое содержит фразу AS subquery? Можно ли при определении внешнего ключа опускать имена столбцов родительской таблицы? Что означает фраза ON DELETE в определении внешнего ключа? Что означает отсутствие фразы ON DELETE в определении внешнего ключа?
FOREIGN KEY Это ограничение указывает, что столбец или совокупность столбцов принимают значения, которые должны совпадать со значениями столбца (совокупности столбцов) из другого связанной таблицы. Это так называемое референциальное ограничение целостности.
| Референциальное ограничение целостности | Референциальное ограничение целостности указывает, что столбец или совокупность столбцов выступают в качестве внешнего ключа и устанавливает связь между этим внешним ключом и конкретным первичным или уникальным ключом, который называется ключом, на который делается ссылка. Таблица, содержащая внешний ключ, называется дочерней, а таблица, на которую делается ссылка, называется родительской. Внешний ключ и ключ, на который делается ссылка, могут быть в одной таблице. В этом случае родительская и дочерние таблицы выступают в одном лице. Для спецификации референциального ограничения целостности на уровне таблицы используйте фразу foreign_key_clause в синтаксисе table_constraint. В этом синтаксисе можно определить составной внешний ключ, который состоит из совокупности столбцов. Для спецификации референциального ограничения целостности на уровне столбца используйте фразу REFERENCES синтаксиса column_constraint. В этом случае внешний ключ состоит из единственного столбца Один и тот же столбец (совокупность столбцов) может выступать в качестве внешнего ключа, и первичного или уникального ключа. Таблица может содержать множество внешних ключей. Один и тот же столбец может входить в состав многих внешних ключей. Ограничения: · Ключ UNIQUE или PRIMARY , на который делается ссылка, должен уже быть определен. · Родительская и дочерняя таблицы должны быть в одной базе данных. · Нельзя определять референциальное ограничение целостности в предложении CREATE TABLE, которое содержит фразу AS subquery. В этом случае сначала создайте таблицу без ограничения, а затем добавьте его позже с помощью предложения ALTER TABLE. |
| foreign_key_ clause | Предоставляет возможность определить на уровне таблицы столбец или совокупность столбцов в качестве внешнего ключа. Этот синтаксис используется для определения составных внешних ключей. Для удовлетворения референциального ограничения целостности составного ключа, либо значения столбцов внешнего ключа должны соответствовать значениям столбцов родительской таблицы, на которые делается ссылка, либо значение по крайней мере одного из столбцов внешнего ключа должно быть NULL. Составной внешний ключ должен ссылаться на составной уникальный ключ или составной первичный ключ. |
| REFERENCES | Указывает, что текущий столбец является внешним ключом, и идентифицирует родительскую таблицу, а также ее столбец (совокупность столбцов), который выступают в качестве ключа, на который делается ссылка. Если определяется только родительская таблица без указания ее столбцов, то по умолчанию делается ссылка на первичный ключ родительской таблицы. Столбцы, на которые делается ссылка, и столбцы внешнего ключа должны совпадать по количеству и типу данных. |
| ON DELETE | Определяет, как Oracle должен автоматически поддерживать референциальную целостность, если удаляется значение того ключа, на который делается ссылка. Если эта фраза отсутствует, то Oracle запрещает удалять значение ключа родительской таблицы, на которое имеется ссылка из внешнего ключа дочерней таблицы. · CASCADE означает, что Oracle автоматически удаляет те строки дочерней таблицы, в которых имеются ссылки на удаляемое значение ключа родительской таблицы. · SET NULL означает, что значения внешнего ключа устанавливаются NULL. |
| CHECK | любая строка таблицы. Строка удовлетворяет условию, если на ней это условие принимает значение TRUE или UNKNOWN. Когда Oracle вычисляет условие ограничения CHECK относительно конкретной строки, то имена столбцов в выражении условия ссылаются на значения этих столбцов в этой строке. При определении для столбца многих ограничений CHECK стройте их так, чтобы не было конфликтов между ними. Oracle не проверяет условия в CHECK на их взаимную противоречивость. Условие в ограничении CHECK может ссылаться на любой столбец таблицы, но не может ссылаться на столбцы другой таблицы. |
8)Что означает ограничение целостности CHECK? Когда условие ограничения CHECK считается выполненным (удовлетворяется)? Можно ли определить ограничение CHECK над несколькими столбцами в синтаксисе ограничения столбца? Можно ли задать несколько ограничений CHECK для одного столбца? Проверяет ли Oracle тот факт, что множество ограничений CHECK, сформулированных для одного столбца, являются взаимно исключающими? Можно ли определить ограничение CHECK над столбцами различных таблиц?
CHECK любая строка таблицы. Строка удовлетворяет условию, если на ней это условие принимает значение TRUE или UNKNOWN. Когда Oracle вычисляет условие ограничения CHECK относительно конкретной строки, то имена столбцов в выражении условия ссылаются на значения этих столбцов в этой строке.
При определении для столбца многих ограничений CHECK стройте их так, чтобы не было конфликтов между ними. Oracle не проверяет условия в CHECK на их взаимную противоречивость.
Условие в ограничении CHECK может ссылаться на любой столбец таблицы, но не может ссылаться на столбцы другой таблицы.