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  /

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

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

Здравствуйте,

Пытаюсь найти связь между таблицами и если если, то какая.

Условно у меня есть первая таблица, я хочу понять связана ли она как то с какой то другой таблицей:

Для этого попытался сделать так: зашел d PLSQL Debeloper`е в other_Users —> далее нашел Юзера у кого есть эта таблица, открыл эту таблицу —> в ней открыл вкладку «Constraints». Скриншот:

И далее не очень понимаю, если есть возможность подскажите пожалуйста.

Вот что, как мне показалось понял и что не понял:

1)В таблице есть главный и внешний ключ. Правда при самом запросе SELECT* from Table — название указанных ключей RTPL_PK и RTPL_RTPB_FK — не выгружается.
2)В Столбец R_TABLE_NAME — указано название таблицы на какую ссылается «данная» таблица то есть ссылается на таблицу ICE_TABLES. Такая таблица сущесвует.

В столбце R_CONSTRAINT_NAME указано наименование столбца PRTB_PK в таблице ICE_TABLES на которую ссылается «данная» таблица. Вот только проблема в том, что в таблице ICE_TABLES — нет столбца с названием PRTB_PK, а есть с названием PRTB_ID. И как это понимать, что то я не очень понимаю.

3)Не понял, что такое R_OWNER. Нагуглил, что это некая «схема», но что это значит не понятно. Единственно, что понятно, что название в столбце R_OWNER — «TC» — совпадает с названием Users в котором две эти таблицы находятся.

4)Так же меня смущают столбцы DELETE_RULE и STATUS — в которых напротив строки с «внешним ключом» стоит наименование NO_ACTION и DISABLED.
Это значит, внешний ключ не работает или что ?

5)И непонятно для чего столбца INDEX_ONWER и INDEX_NAME — что за информация в них указана и как ее нужно или можно использовать?

5)

В Oracle поддерживается несколько типов соединений, отличающихся способом, которым производится объединение строк из двух или более таблиц или представлений. В этой заметке моего блога будут описаны типы соединений, применяемые в Oracle наиболее часто.

Эквисоединение

При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения: 

SQL> SELECT e.last_name, d.dept
FROM emp e, dept d WHERE e.emp_id = d.emp_id;

Для показанного выше оператора соединения также можно использовать и следующий новый синтаксис:

SQL> SELECT e.last_name, d.dept
FROM emp e JOIN dept d
USING (emp_id); 

При желании соединить несколько столбцов, можно перечислить их имена в виде разделенного запятыми списка, например: USING (dept_id, emp_name).

Естественное соединение

Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:

SQL> SELECT e.last_name, d.dept
FROM emp e NATURAL JOIN dept d;

В этом примере условием для выполнения соединения служит наличие идентичных значений в столбце last_name в таблицах emp и dept.

Рефлексивное соединение

Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк. 

SQL> DELETE FROM employees X WHERE ROWID >
2 (select MIN(rowid) FROM employees Y
3 where X.key_values = Y.key_values);

Внутреннее соединение

Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения. Раньше в синтаксисе внутреннего соединения для указания того, каким образом должны соединяться таблицы, нужно было использовать конструкцию WHERE, например, так: 

SQL> SELECT e.flast_name, d.dept
FROM emp e, dept d WHERE e.emp_id = d.emp_id;

Теперь Oracle позволяет задавать критерии соединения в синтаксисе внутреннего (или простого) соединения за счет применения новой конструкции ON или USING, например: 

SQL> SELECT DISTINCT NVL(dname, 'No Dept'),
COUNT(empno) nbr_emps
FROM emp JOIN DEPT
ON emp.deptno = dept.deptno
WHERE emp.job IN ('MANAGER', 'SALESMAN', 'ANALYST')
GROUP BY dname;

Внешнее соединение

Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.

Oracle позволяет использовать операцию внешнего соединения, подразумевающую применение знака плюс (+) для обозначения недостающих значений в одной таблице, но рекомендует лучше использовать вместо нее более новый синтаксис соединения ISO/ANSI. Ниже приведен пример типичного запроса с оператором полного внешнего соединения: 

SQL> SELECT DISTINCT NVL(dept_name, 'No Dept') deptname,
COUNT(empno) nbr_emps
FROM emp FULL JOIN dept
ON dept.deptno = emp.deptno
GROUP BY dname;