Oracle создание связанных таблиц

Обновлено: 15.01.2025

Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:

id_user - целочисленные значения, значит будет тип int, ограничим его 10 символами - int (10).
name - строковое значение varchar, ограничим его 20 символами - varchar(20).
email - строковое значение varchar, ограничим его 50 символами - varchar(50).
password - строковое значение varchar, ограничим его 15 символами - varchar(15).

Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.

id_user int (10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL

Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут - AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:

id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL

Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY (), в скобочках указывается имя ключевого поля. Внесем изменения:

id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)

Итак, таблица готова, и ее окончательный вариант выглядит так:

create table users (
id_user int (10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);

Теперь разберемся со второй таблицей - topics (темы). Рассуждая аналогично, имеем следующие поля:

id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)

Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис:

FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);

Укажем, что id_author - внешний ключ:

id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)

Таблица готова, и ее окончательный вариант выглядит так:

create table topics (
id_topic int (10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,
id_author int (10) NOT NULL,
PRIMARY KEY (id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);

create table posts (
id_post int (10) AUTO_INCREMENT,
message text NOT NULL,
id_author int (10) NOT NULL,
id_topic int (10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);

Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.

Запускаем сервер MySQL (Пуск - Программы - MySQL - MySQL Server 5.1 - MySQL Command Line Client), вводим пароль, создаем БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:

Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.

Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе, например в Блокноте, а затем копировать и вставлять их в черное окошко.

Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:

И, наконец, посмотрим структуру нашей последней таблицы posts:

Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием "Женаты\Замужем" и типом ENUM ('да', 'нет'), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:

married enum ('да', 'нет') NOT NULL default('да')

Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.

Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных сейчас научимся.

Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:

INSERT INTO имя_таблицы VALUES ('значение_первого_столбца','значение_второго_столбца', . 'значение_последнего_столбца');

Давайте попробуем внести в нашу таблицу users следующие значения:

Второй вариант используется для внесения данных в некоторые поля таблицы:

INSERT INTO имя_таблицы ('имя_столбца', 'имя_столбца') VALUES ('значение_первого_столбца','значение_второго_столбца');

В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово - AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:

Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:

Теперь внесем данные во вторую таблицу - topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи):

Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то не существует):

Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.

Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос - как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.

Видеоуроки php + mysql

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Отображение данных из нескольких таблиц с помощью объединений

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

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

Примечание. До выпуска Oracle9i синтаксис объединения отличался от стандартов Американского национального института стандартов (ANSI). Синтаксис соединения, совместимый с SQL: 1999, не дает никаких преимуществ в производительности по сравнению с синтаксисом соединения, разработанным Oracle, который существовал в предыдущих выпусках. Проверьте отдельную страницу соединения для получения подробной информации о собственном синтаксисе соединения.

Условия присоединения

Запросы на соединение должны содержать хотя бы одно условие соединения, либо в предложении FROM, либо в предложении WHERE. Условие соединения сравнивает два столбца из двух разных таблиц. База данных Oracle объединяет пары строк из каждой таблицы, участвующие в объединении, которые удовлетворяют условию объединения, оценивается как ИСТИНА.

Предложение WHERE, содержащее условие соединения, может также содержать другие условия, которые ссылаются на столбцы только одной таблицы. Эти условия могут дополнительно ограничить строки, возвращаемые запросом соединения.

Типы JOIN

Equijoins
Неэкви Джоинс
Самостоятельно присоединяется
Cross Join / Декартовы произведения
Внутренние соединения

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

Левое внешнее соединение
Правое внешнее соединение
Полное внешнее соединение

Equijoins

Equijoin - это соединение с условием соединения, содержащим оператор равенства. Это представлено знаком (=). Это соединение извлекает информацию, используя условие равенства.

Пример таблицы: emp_mast

Пример таблицы: dep_mast

Пример:

Следующая команда показывает, что две таблицы emp_mast и dep_mast объединяются на основе критериев совпадения, т. Е. «ГДЕ e.dept_no = d.dept_no».

Non-Equi Join

Соединение nonequi - это оператор внутреннего соединения, который использует неравную операцию (т. Е. <>,>, <,! =, BETWEEN и т. Д.) Для сопоставления строк из разных таблиц.

Пример:

Самостоятельно присоединяется

Самостоятельное объединение - это такое объединение, при котором таблица соединяется сама с собой. Например, когда вам требуются сведения о сотруднике и его менеджере (также сотруднике).

Пример:

Cross Joins

Кросс- соединение, декартово или декартово произведение - это соединение каждой строки одной таблицы с каждой строкой другой таблицы.

Пример:

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

Внутреннее соединение - это соединение, которое возвращает строки таблиц, которые удовлетворяют условию соединения.

Пример:

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

Внешнее соединение - это такое соединение, которое похоже на равное соединение, но Oracle также будет возвращать несопоставленные строки из таблицы.

Левое внешнее соединение

Это левое внешнее объединение отображает все совпадающие записи обеих таблиц вместе с записями в левой таблице предложения объединения, которых нет в правой таблице предложения соединения.

Пример:

Правое внешнее соединение

Это правое внешнее объединение отображает все совпадающие записи обеих таблиц вместе с записями в левой таблице предложения объединения, которых нет в правой таблице предложения соединения.

Пример:

Полное внешнее соединение

Полное внешнее соединение возвращает все строки из обеих таблиц слева и справа от предложения соединения, расширенные нулями, если они не удовлетворяют условию соединения.

Пример:

Natural Join

Естественное объединение - это такое объединение, которое сравнивает общие столбцы обеих таблиц друг с другом.

Пример:

Antijoins

Анти-соединение между двумя таблицами возвращает строки из первой таблицы, где во второй таблице не найдено совпадений. Анти-объединения доступны только при выполнении подзапроса NOT IN

Пример:

Частичные объединения

Полусоединение - это такое соединение, в котором предложение EXISTS используется с подзапросом. Это можно назвать полусоединением, потому что даже если в подзапросе возвращаются повторяющиеся строки, возвращается только один набор совпадающих значений во внешнем запросе.

Пример:

СОЕДИНЕНИЯ: SQL и другие реляционные базы данных

Предыдущее: NLS_CHARSET_NAME
Следующая: EQUIJOINS

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

Таблица CUSTOMERS:

Таблица SALESREPS:

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

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

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

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

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

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

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

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

Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.

Теория

Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:

Все записи относящиеся к первичному ключу должны быть уникальны. Это означает, что если первичный ключ состоит из одного поля, то все записи в нём должны быть уникальными. А если первичный ключ состоит из нескольких полей, то комбинация этих записей должна быть уникальна, но в отдельных полях допускаются повторения.
Записи в полях относящихся к первичному ключу не могут быть пустыми. Это ограничение в PostgreSQL называется not null.
В каждой таблице может присутствовать только один первичный ключ.

К первичному ключу предъявляют следующее требование:

Первичный ключ должен быть минимально достаточным. То есть в нем не должно быть полей, удаление которых из первичного ключа не отразится на его уникальности. Это не обязательное требование но желательно его соблюдать.

Первичный ключ может быть:

естественным – существует в реальном мире, например ФИО, или номер и серия паспорта;
суррогатным – не существует в реальном мире, например какой-то порядковый номер, который существует только в базе данных.

Я сам не имею большого опыта работы с SQL, но в книгах пишут что лучше использовать естественный первичный ключ. Почему именно так, я пока ответить не смогу.

Связь между таблицами

Первостепенная задача первичного ключа – это уникальная идентификация каждой строки. Но первичный ключ может решить ещё одну задачу. В базе данных есть возможность связывания нескольких таблиц. Для такой связи используют первичный и внешний ключ sql. В одной из таблиц создают внешний ключ, который ссылается на поля другой таблицы. Но внешний ключ не может ссылаться на любые поля другой таблицы, а может ссылаться только на определённые:

эти поля должны присутствовать и в ссылающейся таблице и в той таблице на которую он ссылается;
ссылается внешний ключ из одной таблицы обычно на первичный ключ другой таблицы.

Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:

ФИО full_name	Возраст age	Класс class
Иванов Иван Иванович	15	9А
Сумкин Фёдор Андреевич	15	9А
Петров Алексей Николаевич	14	8Б
Булгаков Александр Геннадьевич	14	8Б

Таблица pupils

И есть таблица “Успеваемость” (evaluations):

Предмет item	ФИО full_name	Оценка evaluation
Русский язык	Иванов Иван Иванович	4
Русский язык	Петров Алексей Николаевич	5
Математика	Булгаков Александр Геннадьевич	3
Литература	Сумкин Фёдор Андреевич	5

Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “ Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “ Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

составной первичный ключ – например, в качестве первичного ключа взять два поля: ФИО и Класс;
суррогатный первичный ключ – в таблице “Ученики” добавить поле “№ Ученика” и сделать это поле первичным ключом;
добавить более уникальное поле – например, можно использовать уникальный номер зачетной книжки и использовать новое поле в качестве первичного ключа;

Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:

поле full_name, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullable – not null;
для поля full_name был создан индекс pupils_pkey с типом btree. Про типы индексов и про сами индексы расскажу в другой статье.

Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу “pupils“:

Заполним таблицу “evaluations“:

А теперь попробуем поставить оценку не существующему ученику:

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

И посмотрим на строки в таблице evaluations:

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

Давайте удалим наши таблички и создадим их заново, но теперь создадим их используя составной первичный ключ:

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

Первичный ключ в таблице pupils уже состоит из двух полей, поэтому внешний ключ ссылается на эти два поля.

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

И также по второй таблице:

Удаление таблиц

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

Поэтому удалим наши таблицы в следующем порядке:

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

И сделаем таблицы без ключей:

Теперь создадим первичный ключ в таблице pupils:

И создадим внешний ключ в таблице evaluations:

Посмотрим что у нас получилось:

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

Читайте также: