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