![]() WHERE cardinality(array_positions(ail, fkeys.source)) < 2 * This way, we get the table once with "circular = TRUE". * Stop when a table appears in the trail the third time. JOIN tables ON tables.table_name = fkeys.target ![]() * all tables with a foreign key pointing a table in the working set */ ![]() except the ones that have a foreign key */ * source and target tables for all foreign keys */ĪND NOT relnamespace::regnamespace::text LIKE ANY So why not write a query telling us the order in which we are supposed to insert data? Populating an empty data model can be a bit tricky. If you have to start using an existing data model, it can be a bit hard to wrap your head around this stuff. Determining the correct insertion order for foreign keys The order does matter in the default case. In order words: We have to fill the currency table first, then insert locations, and so on. If we want to INSERT, we have to use a NULL value (= unknown currency). Logically the first INSERT is going to fail because currency number 1 does not exist yet. Test=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', NULL) Test=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1) ĮRROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey"ĭETAIL: Key (currency_id)=(1) is not present in table "t_currency". Let us see if we happen to insert into the product table: Basically it is a very simple data model. We want to store currencies, products, as well as product descriptions. Location_id int REFERENCES t_location (id), Product_id int REFERENCES t_product (id), To show the importance of order, we have to create a data model first:Ĭurrency_id int REFERENCES t_currency (id), However, if a foreign key is in place, order starts to matter (at least in a typical scenario but more on that later). If there are no foreign keys, you can insert data into any table in any order. ![]() However, foreign keys will introduce some issues which you have to take care of when writing applications. Referential integrity is therefore one of the most important concepts ever invented in IT. The core idea is to prevent your PostgreSQL database from storing inconsistent data by enforcing constraints ensuring the correctness of your tables (at least as far as relations between objects are concerned). In SQL, the concept of foreign keys is an important one that can be found in all professional databases used in the industry. Constraint foreign key integrity postgresql ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |