Hi,
Patrick Francelle and I encountered this situation where there was a check
constraint on a table using a function to enforce a constraint across two
different tables. When using pg_dump to dump structure and data we found
out we couldn't restore it because tables weren't dumped in the right order
regarding that constraint.
Then, we found out this thread
https://www.postgresql.org/message-id/11619.1077803699%40sss.pgh.pa.us
where Tom explained how "check constraint were not intended to handle
cross-table checks" and how you should use a trigger instead. If you look
at modeling databases books, you'll actually find someting similar.
By looking at the constraint documentation page, we found out there was
nothing about it. So we decided to write a first version of a patch.
You will find it enclosed.
Here are some informations about it :
Project : postgresql
Branch : master
Applying, compilation and test : I applied it successfully. It compiles
sucessfully and I tested it on my laptop
Platform-specific : there shouldn't be any platform specific item
Regression tests : regression tests are not available for documentation
Documentation : We don't document documentation source code
Performance impact : none
Choices I made and why : I choose to include a trigger link to help users
go to the accurate documentation section. I also choose to add it as a note
so it's more visible (but I'm open minded on that matter)
Adresses a todo item : no
Please let me tell me know if I missed something. I'm waiting for feedbacks
to improve that patch.
Cheers,
Lætitia
--
*Think! Do you really need to print this email ? *
*There is no Planet B.*
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 2cd0b8a..dfe00a5 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -403,6 +403,17 @@ CREATE TABLE products (
ensure that a column does not contain null values, the not-null
constraint described in the next section can be used.
</para>
+
+ <note>
+ <para>
+ Check constraint were not designed to enforce business rules across tables.
+ Avoid using check constraints with function accessing to other tables and
+ prefer triggers instead (please refer to <xref linkend="triggers"/> for
+ more information about triggers). PostgreSQL won't prevent you
+ from doing so, but be aware you might encounter difficulties to restore
+ dumps (generated with pg_dump or pg_dumpall) if you do.
+ </para>
+ </note>
</sect2>
<sect2>
