After that, a delete in either of the referenced tables will fail, as expected. Then you can insert matching rows in both referenced tables and a referencing row. Second, if you delete a row in either referenced table and there are matching rows in stock, that operation must be rejected too.īoth are easily verified: INSERT INTO stock values (1, 1, 10) ĮRROR: insert or update on table "stock_0" violates foreign key constraint "stock_item_id_fkey"ĭETAIL: Key (item_id)=(1) is not present in table "items". First, if you insert a row in stock (the referencing table) that doesn’t have a corresponding row in items or warehouses (the referenced table), an error must be raised. There are two basic operations that you want the foreign key to provide. Did you notice that each foreign key is only declared once? One points to to a regular (not partitioned) table warehouses, the other points to partitioned table items. You can see there are two foreign keys here. ![]() Warehouse_id integer not null REFERENCES warehouses,ĬREATE TABLE stock_0 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 0) ĬREATE TABLE stock_1 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 1) ĬREATE TABLE stock_2 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 2) ĬREATE TABLE stock_3 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 3) ĬREATE TABLE stock_4 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 4) Item_id integer not null REFERENCES items, CREATE TABLE items (ĬREATE TABLE items_0 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 0) ĬREATE TABLE items_1 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 1) ĬREATE TABLE items_2 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 2) ĬREATE TABLE warehouses (warehouse_id integer primary key, location text not null) Now that this feature is complete, some new use cases are open to PostgreSQL that previously weren’t. All this means that for the first time it is possible in PostgreSQL to maintain large volumes of data while maintaining referential integrity. Second, because the (early days) table inheritance feature didn’t really support foreign keys either. ![]() Why do I point this out? Two reasons: first, when partitioned tables were first introduced in PostgreSQL 10, they didn’t support foreign keys at all you couldn’t create FKs on partitioned tables, nor create FKs that referenced a partitioned table. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly. Now that PostgreSQL 12 is out, we consider foreign keys to be fully compatible with partitioned tables. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |