On Tue, Mar 27, 2012 at 7:40 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"Albe Laurenz" <laurenz.albe@xxxxxxxxxx> writes:It is not a bug; it is an unsafe and unsupported use of CHECK
> 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.
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
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20-3058-9522
Mobile: +91 976-788-8246
