Меню

Postgresql таблица foreign keys



PostgreSQL Foreign Key

Summary: in this tutorial, you will learn about PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints.

Introduction to PostgreSQL Foreign Key Constraint

A foreign key is a column or a group of columns in a table that reference the primary key of another table.

The table that contains the foreign key is called the referencing table or child table. And the table referenced by the foreign key is called the referenced table or parent table.

A table can have multiple foreign keys depending on its relationships with other tables.

In PostgreSQL, you define a foreign key using the foreign key constraint. The foreign key constraint helps maintain the referential integrity of data between the child and parent tables.

A foreign key constraint indicates that values in a column or a group of columns in the child table equal the values in a column or a group of columns of the parent table.

PostgreSQL foreign key constraint syntax

The following illustrates a foreign key constraint syntax:

  • First, specify the name for the foreign key constraint after the CONSTRAINT keyword. The CONSTRAINT clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.
  • Second, specify one or more foreign key columns in parentheses after the FOREIGN KEY keywords.
  • Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.
  • Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses.

The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated. Since the primary key is rarely updated, the ON UPDATE action is not often used in practice. We’ll focus on the ON DELETE action.

PostgreSQL supports the following actions:

  • SET NULL
  • SET DEFAULT
  • RESTRICT
  • NO ACTION
  • CASCADE

PostgreSQL foreign key constraint examples

The following statements create the customers and contacts tables:

In this example, the customers table is the parent table and the contacts table is the child table.

Each customer has zero or many contacts and each contact belongs to zero or one customer.

The customer_id column in the contacts table is the foreign key column that references the primary key column with the same name in the customers table.

The following foreign key constraint fk_customer in the contacts table defines the customer_id as the foreign key:

Because the foreign key constraint does not have the ON DELETE and ON UPDATE action, they default to NO ACTION .

NO ACTION

The following inserts data into the customers and contacts tables:

The following statement deletes the customer id 1 from the customers table:

Because of the ON DELETE NO ACTION , PostgreSQL issues a constraint violation because the referencing rows of the customer id 1 still exist in the contacts table:

The RESTRICT action is similar to the NO ACTION . The difference only arises when you define the foreign key constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode. We’ll discuss more on this in the subsequent tutorial.

SET NULL

The SET NULL automatically sets NULL to the foreign key columns in the referencing rows of the child table when the referenced rows in the parent table are deleted.

Читайте также:  Таблица двигателей мерседес бенц

The following statements drop the sample tables and re-create them with the foreign key that uses the SET NULL action in the ON DELETE clause:

The following statements insert data into the customers and contacts tables:

To see how the SET NULL works, let’s delete the customer with id 1 from the customers table:

Because of the ON DELETE SET NULL action, the referencing rows in the contacts table set to NULL. The following statement displays the data in the contacts table:

PostgreSQL Foreign Key - SET NULL

As can be seen clearly from the output, the rows that have the customer_id 1 now have the customer_id sets to NULL

CASCADE

The ON DELETE CASCADE automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted. In practice, the ON DELETE CASCADE is the most commonly used option.

The following statements recreate the sample tables. However, the delete action of the fk_customer changes to CASCADE :

The following statement deletes the customer id 1:

Because of the ON DELETE CASCADE action, all the referencing rows in the contacts table are automatically deleted:

PostgreSQL Foreign Key - ON DELETE CASCADE

SET DEFAULT

The ON DELETE SET DEFAULT sets the default value to the foreign key column of the referencing rows in the child table when the referenced rows from the parent table are deleted.

Add a foreign key constraint to an existing table

To add a foreign key constraint to the existing table, you use the following form of the ALTER TABLE statement:

When you add a foreign key constraint with ON DELETE CASCADE option to an existing table, you need to follow these steps:

First, drop existing foreign key constraints:

First, add a new foreign key constraint with ON DELETE CASCADE action:

In this tutorial, you have learned about PostgreSQL foreign keys and how to use the foreign key constraint to create foreign keys for a table.

Источник

PostgreSQL – Foreign Key

In this article, we will look into the PostgreSQL Foreign key constraints using SQL statements. A foreign key is a column or a group of columns used to identify a row uniquely of a different table. The table that comprises the foreign key is called the referencing table or child table. And the table to that the foreign key references is known as the referenced table or parent table. A table can possess multiple foreign keys according to its relationships with other tables.

Let’s analyze the above syntax:

  • First, specify the name for the foreign key constraint after the CONSTRAINT keyword. The CONSTRAINT clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.
  • Second, specify one or more foreign key columns in parentheses after the FOREIGN KEY keywords.
  • Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.
  • Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses.

The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated. Since the primary key is rarely updated, the ON UPDATE action is not often used in practice. We’ll focus on the ON DELETE action.

Читайте также:  Молоток кашкарова таблица прочности

PostgreSQL supports the following actions:

Example:

The following statements create the customers and contacts tables:

In this example, the customer table is the parent table and the contacts table is the child table. Each customer has zero or many contacts and each contact belongs to zero or one customer. The customer_id column in the contacts table is the foreign key column that references the primary key column with the same name in the customer’s table. The following foreign key constraint fk_customer in the contacts table defines the customer_id as the foreign key:

Because the foreign key constraint does not have the ON DELETE and ON UPDATE action, they default to NO ACTION .

NO ACTION

The following inserts data into the customers and contacts tables:

The following statement deletes the customer id 1 from the customers table:

Because of the ON DELETE NO ACTION , PostgreSQL issues a constraint violation because the referencing rows of the customer id 1 still exist in the contacts table:

The RESTRICT action is similar to the NO ACTION . The difference only arises when you define the foreign key constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode.

SET NULL

The ON DELETE CASCADE automatically sets NULL to the foreign key columns in the referencing rows of the child table when the referenced rows in the parent table are deleted. The following statements drop the sample tables and re-create them with the foreign key that uses the SET NULL action in the ON DELETE clause:

The following statements insert data into the customers and contacts tables:

To see how the SET NULL works, let’s delete the customer with id 1 from the customers table:

Because of the ON DELETE SET NULL action, the referencing rows in the contacts table set to NULL. The following statement displays the data in the contacts table:

It will result in the following:

As can be seen clearly from the output, the rows that have the customer_id 1 now have the customer_id sets to NULL

CASCADE

The ON DELETE CASCADE automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted. In practice, the ON DELETE CASCADE is the most commonly used option. The following statements recreate the sample tables. However, the delete action of the fk_customer changes to CASCADE:

The following statement deletes the customer id 1:

Because of the ON DELETE CASCADE action, all the referencing rows in the contacts table are automatically deleted:

This will result in the following:

SET DEFAULT

The ON DELETE SET DEFAULT sets the default value to the foreign key column of the referencing rows in the child table when the referenced rows from the parent table are deleted. To add a foreign key constraint to the existing table, you use the following form of the ALTER TABLE statement:

When you add a foreign key constraint with ON DELETE CASCADE option to an existing table, you need to follow these steps:

First, drop existing foreign key constraints:

Then, add a new foreign key constraint with ON DELETE CASCADE action:

Читайте также:  Lie past simple таблица

Источник

Добавить внешний ключ в уже существующую таблицу

Я хочу добавить внешний ключ к таблице под названием «каталог».

когда я пытаюсь сделать это, я получаю это сообщение об ошибке:

ошибка в статусе INNODB:

120405 14:02:57 ошибка ограничения внешнего ключа таблицы таблица MyTable.#в SQL-7fb1_7d3a:

когда я использую этот запрос, он работает, но с неправильным действием «on delete»:

обе таблицы InnoDB и оба поля являются «INT (11) not null». Я использую MySQL 5.1.61. Попытка запустить этот запрос ALTER с MySQL Workbench (новейший) на MacBook Pro.

Таблица Create Операторы:

8 ответов

добавить внешний ключ (grade_id) к существующей таблице (пользователи), выполните следующие действия:

просто используйте этот запрос, я пробовал его в соответствии с моим сценарием, и он работает хорошо

на макушке моей головы приходят на ум две вещи.

  • является ли ваш индекс внешнего ключа уникальным именем во всей базе данных (#3 в списке)?
  • вы пытаетесь установить в таблице PK значение NULL при обновлении (#5 в списке)?

Я предполагаю, что проблема с значение NULL при обновлении (если мои мозги сегодня не работают в обратном направлении, как это часто бывает. ).

Edit: я пропустил комментарии к вашему исходному сообщению. Столбцы Unsigned/not unsigned int, возможно, разрешили ваше дело. Надеюсь моя ссылка поможет кому-то в будущем думал.

Простых Шагов.

но ваша таблица имеет:

Это не могу задайте для столбца Sprache значение NULL, поскольку он определен как NOT NULL.

при добавлении ограничения внешнего ключа в таблицу с помощью ALTER TABLE не забудьте сначала создать необходимые индексы.

  1. создать индекс
  2. Alter table

Источник

Ссылочная целостность: внешний ключ (FOREIGN KEY) стр. 1

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

Связь между таблицами не является равноправной. В ней всегда есть главная таблица и таблица подчиненная. Связи бывают двух типов: «один к одному» и «один ко многим». Связь «один к одному» означает, что строке главной таблицы соответствует не более одной строки (т.е. одна или ни одной) в подчиненной таблице. Связь «один ко многим» означает, что одной строке главной таблицы отвечает любое число строк (в том числе и 0) в подчиненной таблице.

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

Поскольку главная таблица всегда находится со стороны «один», то столбец, участвующий в связи по внешнему ключу, должен иметь ограничение PRIMARY KEY или UNIQUE . Внешний же ключ задается при создании или изменении структуры подчиненной таблицы при помощи спецификации FOREIGN KEY :

Количество столбцов в списках 1 и 2 должно быть одинаковым, а типы данных этих столбцов должны быть попарно совместимы.

Вот как можно создать внешний ключ в таблице PC:

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

Аналогичным образом создаются внешние ключи в таблицах Printer и Laptop.

Источник

Adblock
detector