Шаг 108 - Oracle - ТАБЛИЦЫ БД определение отношений

Надеюсь из прошлых, почти полностью теоретических, изложений стало немного яснее, что же все-таки есть реляционные данные и все, что с ними связано. Давайте сейчас попробуем просмотреть все это практически. Наша с вами учебная БД в схеме miller содержит, пять таблиц. Все они в принципе отвечают требованиям 3НФ. Но, когда я их создавал, я не связал столбцы этих таблиц между собой с помощью стандартных средств. А, вот сейчас давайте мы с вами это сделаем. Итак для примера организуем связь, которая чаше всего рекомендована к применению, типа один-ко-многим. Ярким примером для построения такой связи служит две из наших пяти таблиц это CUSTOMERS и SALESREPS. Оператор CREATE TABLE их DDL определений записан следующим образом:

Таблица CUSTOMERS:

CREATE TABLE CUSTOMERS
(
	CUST_NUM INTEGER PRIMARY KEY,
	COMPANY VARCHAR2(30),
	CUST_REP INTEGER,
	CREDIT_LIMIT NUMBER,
)
/

Таблица SALESREPS:

CREATE TABLE SALESREPS
(
	EMPL_NUM INTEGER PRIMARY KEY,
	NAME VARCHAR2(30),
	AGE INTEGER,
	REP_OFFICE INTEGER,
	TITLE VARCHAR2(20),
	HIRE_DATE DATE NOT NULL,
	MANAGER INTEGER,
	QUOTA NUMBER,
	SALES NUMBER
)
/

При просмотре данных, этих таблиц почти сразу видно, что столбец таблицы SALESREPS - EMPL_NUM есть отношение один-ко-многим столбца CUST_REP для таблицы CUSTOMERS. Для определения связи между таблицами воспользуемся оператором ALTER TABLE и запишем вот такую конструкцию:

ALTER TABLE CUSTOMERS ADD 
FOREIGN KEY (CUST_REP) REFERENCES SALESREPS (EMPL_NUM)
/

Получаем:

SQL> ALTER TABLE CUSTOMERS ADD 
  2  FOREIGN KEY (CUST_REP) REFERENCES SALESREPS (EMPL_NUM)
  3  /

Таблица изменена.

Все, связь между столбцами таблиц установлена! Все достаточно просто. Теперь действует ограничение ссылочной целостности и нарушить его нам с вами не позволят! Можно убедиться в этом. Столбец таблицы SALESREPS - EMPL_NUM содержит следующее множество значений 101 .. 110 и отдельно 120. Попробуйте что-нибудь вроде:

INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit)
			VALUES(2155, NULL, 150, 34.567)
/

После ввода, получаем:

SQL> INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit)
  2     VALUES(2155, NULL, 150, 34.567)
  3  /
INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit)
*
ошибка в строке 1:
ORA-02291: нарушено ограничение целостности (MILLER.SYS_C003548) - исходный 
ключ не найден 

Естественно ошибка ORA-02291! А все потому, что множество 101 .. 110 и отдельно 120 не содержит числа 150! И по этому в данном случае не допустимо! Вот и получилось жесткое отношение один-ко-многим! Так же, можно и удалить связь, между столбцами таблиц применив оператор DROP. Но, нужно узнать имя ссылочной целостности в системе. Сейчас мы его знаем благодаря ошибке. А что если, в процессе работы нужно удалить ссылочную целостность, а потом снова восстановить ее! Для этого обратимся к представлению в вашей схеме USER_CONSTRAINTS. Оно содержит все имена ваших ограничений. Дадим такой запрос:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CUSTOMERS'
/

Получаем:

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
  2  WHERE TABLE_NAME = 'CUSTOMERS'
  3  /

CONSTRAINT_NAME                CONSTRAINT_TYPE
------------------------------ ---------------
SYS_C003506                    P
SYS_C003548                    R

Там где поле CONSTRAINT_TYPE содержит значение R и есть наше ограничение (по моему от REFERENCES, точно не помню!) Получаем имя ограничения - SYS_C003548 (тоже номер был и в ошибке помните?). Вот теперь давайте от него избавимся:

ALTER TABLE CUSTOMERS DROP CONSTRAINT SYS_C003548
/

Получаем:

SQL> ALTER TABLE CUSTOMERS DROP CONSTRAINT SYS_C003548
  2  /

Таблица изменена.

Вот теперь ограничение снято. Повторим наш предыдущий запрос и посмотрим, что содержит USER_CONSTRAINTS сейчас:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CUSTOMERS'
/

Получаем:

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
  2  WHERE TABLE_NAME = 'CUSTOMERS'
  3  /

CONSTRAINT_NAME                CONSTRAINT_TYPE
------------------------------ ---------------
SYS_C003506                    P

Хорошо видно, что осталось только ограничение первичного ключа таблицы CUSTOMERS имеющее имя SYS_C003506. Кроме того, таблица может содержать ограничение на саму себя например все с той же таблицей SALESREPS можно проделать следующее:

ALTER TABLE SALESREPS ADD 
FOREIGN KEY (MANAGER) REFERENCES SALESREPS (EMPL_NUM)
/

Получаем:

SQL> ALTER TABLE SALESREPS ADD 
  2  FOREIGN KEY (MANAGER) REFERENCES SALESREPS (EMPL_NUM)
  3  /

Таблица изменена.

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


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.