On Tue, Mar 27, 2012 at 7:40 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> "Albe Laurenz" <laurenz.a...@wien.gv.at> writes: > > pg_dump does not resolve dependencies, it avoids problems by adding > > constraints after inserting the data. > > > It seems that this is not done for CHECK constraints, however - they are > > added when the table is defined. > > > I think that this is a bug. > > It is not a bug; it is an unsafe and unsupported use of CHECK > constraints. > > Using a CHECK to enforce a cross-row constraint is fundamentally broken, > because there is no way for the database to know that the constraint > might be violated after the *other* row is modified. In the example > at hand, a change in sample_one.param_names could leave the constraint > unsatisfied for some rows in sample, but the database wouldn't detect > that. > In my case I won't allow anyone to insert/modify the rows of sample_one table. I have already inserted some rows in sample_one table where I want one constraint is number of array elements of sample_one.param_names and sample.params must be same. That's why I have created CHECK constraint in sample table. User can insert, modify and delete the rows of sample table, so I don't want any mismatch in the number of array elements of sample_one.param_names and sample.params table. > I think the right fix here would be to redesign the table schema so that > the required cross-table constraint could be expressed as a foreign key. > We don't have enough context to guess at what a better design would > look like, though. > > regards, tom lane > -- *Akshay Joshi Senior Software Engineer EnterpriseDB Corporation The Enterprise PostgreSQL Company Phone: +91 20-3058-9522 Mobile: +91 976-788-8246*
