pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable constraints
11+ messages / 6 participants
[nested] [flat]
* Table constraints
@ 2002-03-28 19:00 Oleg Lebedev <olebedev@waterford.org>
0 siblings, 1 reply; 11+ messages in thread
From: Oleg Lebedev @ 2002-03-28 19:00 UTC (permalink / raw)
To: pgsql-sql
Hi everybody,
I saw a couple of messages regarding rule/constraint/trigger standards
which Tom proposed to adopt in postgres. I've read through the current
specs, but still can't figure it out. I am using version 7.1.3 and this
is what I am trying to do:
I have 2 tables:
Set { type_id int,
set_desc varchar(128) }
Type { id int primary key }
I want to update a row in Type table and cascade this update to update
Set table. I declare a constraint as follows:
ALTER TABLE Set
ADD CONSTRAINT fk_type
FOREIGN KEY (type_id)
REFERENCES Type (id)
ON UPDATE CASCADE;
Postgres gives me a NOTICE and creates the constraint.
Here are some questions:
Why pg_relcheck table is still empty after the constraint is added?
Why instead it created 5 triggers (I checked pg_class.reltriggers for
Set table): 3 called "fk_type" on Set and 2 unnamed on Type?
Why when I try to update id in Type table I get RI violation error?
Shouldn't it cascade the update?
How can I drop the constraint now? I set reltriggers to 0 in pg_class
entry corresponding to Set table, and tried to delete generated
triggers, but since only 3 out of 5 have "fk_type" name, I couldn't
figure out how to track down the rest of them.
Thanks in advance for any help.
Oleg
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Table constraints
@ 2002-03-28 19:14 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Oleg Lebedev <olebedev@waterford.org>
0 siblings, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2002-03-28 19:14 UTC (permalink / raw)
To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql
On Thu, 28 Mar 2002, Oleg Lebedev wrote:
> Hi everybody,
> I saw a couple of messages regarding rule/constraint/trigger standards
> which Tom proposed to adopt in postgres. I've read through the current
> specs, but still can't figure it out. I am using version 7.1.3 and this
> is what I am trying to do:
> I have 2 tables:
> Set { type_id int,
> set_desc varchar(128) }
> Type { id int primary key }
>
> I want to update a row in Type table and cascade this update to update
> Set table. I declare a constraint as follows:
> ALTER TABLE Set
> ADD CONSTRAINT fk_type
> FOREIGN KEY (type_id)
> REFERENCES Type (id)
> ON UPDATE CASCADE;
> Postgres gives me a NOTICE and creates the constraint.
>
> Here are some questions:
> Why pg_relcheck table is still empty after the constraint is added?
relcheck is only for CHECK constraints.
> Why instead it created 5 triggers (I checked pg_class.reltriggers for
> Set table): 3 called "fk_type" on Set and 2 unnamed on Type?
Hmm, it should have only created 3 triggers, 1 on set and 2 on Type
and they should have all had tgconstrname set to fk_type.
What does select * from pg_trigger say?
> Why when I try to update id in Type table I get RI violation error?
> Shouldn't it cascade the update?
It works for me in 7.2. I don't have 7.1.3 to test against right now
but I'm pretty sure that's in the regression test.
Can you give a short script that illustrates the problem?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Table constraints
@ 2002-03-28 20:37 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Oleg Lebedev <olebedev@waterford.org>
0 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2002-03-28 20:37 UTC (permalink / raw)
To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql
On Thu, 28 Mar 2002, Oleg Lebedev wrote:
> of course you were right about the number of created triggers. I must have
> counted them wrong.
> Here is what I do (in my previous example type = activitytype, id = objectid)
>
> webspectest=# select count(*) from pg_trigger;
> count
> -------
> 119
> (1 row)
>
> webspectest=# alter table "set" add constraint "fk_acttype" foreign key
> (acttype) references activitytype (objectid) on update cascade;
> NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
> for FOREIGN KEY check(s)
> CREATE
> webspectest=# select count(*) from pg_trigger;
> count
> -------
> 122
> (1 row)
>
> webspectest=# update activitytype set objectid=999 where objectid=1;
> UPDATE 1
> webspectest=# alter table set drop constraint fk_acttype;
> ERROR: parser: parse error at or near ";"
>
> So the only question left is how do I drop this constraint now?
Use drop trigger on the three triggers it created. You'll need to double
quote the trigger names (they're mixed case and not the constraint name
that you gave but an internal name.)
> BTW, could you briefly explain to me what kind of triggers were created for
> this constraint.
I'll give the short view, there's a document on it on techdocs that goes
into more detail.
There are three triggers, one on the referencing (fk) table, two on the
referenced (pk) table. The trigger on the referencing table fires after
insert or update to that table and makes sure that an appropriate row
exists in the pk table. One of the triggers on the referenced table fires
after update to that table and calls one of a set of function based on
whether a referential action was asked for and if so which one. The final
trigger fires after delete to the referenced table and handles delete
actions.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Table constraints
@ 2002-03-28 20:38 Oleg Lebedev <olebedev@waterford.org>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Oleg Lebedev @ 2002-03-28 20:38 UTC (permalink / raw)
To: pgsql-sql
Stephan,
of course you were right about the number of created triggers. I must have
counted them wrong.
Here is what I do (in my previous example type = activitytype, id = objectid)
webspectest=# select count(*) from pg_trigger;
count
-------
119
(1 row)
webspectest=# alter table "set" add constraint "fk_acttype" foreign key
(acttype) references activitytype (objectid) on update cascade;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
webspectest=# select count(*) from pg_trigger;
count
-------
122
(1 row)
webspectest=# update activitytype set objectid=999 where objectid=1;
UPDATE 1
webspectest=# alter table set drop constraint fk_acttype;
ERROR: parser: parse error at or near ";"
So the only question left is how do I drop this constraint now?
BTW, could you briefly explain to me what kind of triggers were created for
this constraint.
thanks,
Oleg
Stephan Szabo wrote:
> On Thu, 28 Mar 2002, Oleg Lebedev wrote:
>
> > Hi everybody,
> > I saw a couple of messages regarding rule/constraint/trigger standards
> > which Tom proposed to adopt in postgres. I've read through the current
> > specs, but still can't figure it out. I am using version 7.1.3 and this
> > is what I am trying to do:
> > I have 2 tables:
> > Set { type_id int,
> > set_desc varchar(128) }
> > Type { id int primary key }
> >
> > I want to update a row in Type table and cascade this update to update
> > Set table. I declare a constraint as follows:
> > ALTER TABLE Set
> > ADD CONSTRAINT fk_type
> > FOREIGN KEY (type_id)
> > REFERENCES Type (id)
> > ON UPDATE CASCADE;
> > Postgres gives me a NOTICE and creates the constraint.
> >
> > Here are some questions:
> > Why pg_relcheck table is still empty after the constraint is added?
>
> relcheck is only for CHECK constraints.
>
> > Why instead it created 5 triggers (I checked pg_class.reltriggers for
> > Set table): 3 called "fk_type" on Set and 2 unnamed on Type?
>
> Hmm, it should have only created 3 triggers, 1 on set and 2 on Type
> and they should have all had tgconstrname set to fk_type.
> What does select * from pg_trigger say?
>
> > Why when I try to update id in Type table I get RI violation error?
> > Shouldn't it cascade the update?
>
> It works for me in 7.2. I don't have 7.1.3 to test against right now
> but I'm pretty sure that's in the regression test.
>
> Can you give a short script that illustrates the problem?
^ permalink raw reply [nested|flat] 11+ messages in thread
* table constraints
@ 2005-02-28 19:28 Casey T. Deccio <ctdecci@sandia.gov>
0 siblings, 2 replies; 11+ messages in thread
From: Casey T. Deccio @ 2005-02-28 19:28 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am running PostgreSQL 7.4.7. I am having some issues with a
constraint for one of my database tables. The code snippet below
outlines the code and its output (output is commented).
In this case each bldg has an owner associated to it, and each animal
lives in some bldg. Each owner has exactly one own favorite animal out
of all the bldgs owned by him. So the constraint added to each zoo row
is that the boolean field 'favorite' is true for exactly once for each
group of animals in the zoo that have a common owner.
The unique_favorite(text) function is created to help with this
constraint. The function returns what it is supposed to (see select
statement in the code), but doesn't hold when used as a constraint.
There may be a better way to do this, but I'm not looking for that right
now. I would like to know if I am using this constraint wrong, or if
there is there something wrong with the table constraints in the
database system.
Thanks,
Casey
------ Code snippet -------
CREATE TABLE owner (owner varchar(50));
-- CREATE TABLE
INSERT INTO owner VALUES ('steve');
-- INSERT 13193166 1
CREATE TABLE bldg (bldg varchar(50), owner varchar(50));
-- CREATE TABLE
INSERT INTO bldg VALUES ('bldg1', 'steve');
-- INSERT 13193169 1
CREATE TABLE zoo (animal varchar(50), bldg varchar(50), favorite
boolean);
-- CREATE TABLE
CREATE OR REPLACE FUNCTION unique_favorite(text) RETURNS BOOLEAN AS '
DECLARE
temp RECORD;
BEGIN
SELECT into temp * FROM
(SELECT b.owner, sum(CASE WHEN favorite = TRUE THEN 1 ELSE 0 END) AS
num_favorites
FROM (SELECT * FROM zoo WHERE animal = $1) z INNER JOIN bldg b
USING(bldg) GROUP BY b.owner) sub
WHERE sub.num_favorites <> 1;
RETURN NOT FOUND;
END;
' LANGUAGE plpgsql;
-- CREATE FUNCTION
ALTER TABLE zoo ADD check(unique_favorite(animal));
-- ALTER TABLE
INSERT into zoo VALUES ('monkey', 'bldg1', false);
-- INSERT 13193173 1
-- (This shouldn't be allowed!!!)
SELECT *, unique_favorite(animal) FROM zoo;
-- animal | bldg | favorite | unique_favorite
-- --------+-------+----------+-----------------
-- monkey | bldg1 | f | f
INSERT into zoo VALUES ('monkey', 'bldg1', false);
-- ERROR: new row for relation "zoo" violates check constraint "$1"
INSERT into zoo VALUES ('monkey', 'bldg1', true);
-- ERROR: new row for relation "zoo" violates check constraint "$1"
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: table constraints
@ 2005-02-28 20:20 Bruno Wolff III <bruno@wolff.to>
parent: Casey T. Deccio <ctdecci@sandia.gov>
1 sibling, 1 reply; 11+ messages in thread
From: Bruno Wolff III @ 2005-02-28 20:20 UTC (permalink / raw)
To: Casey T. Deccio <ctdecci@sandia.gov>; +Cc: pgsql-sql
On Mon, Feb 28, 2005 at 11:28:30 -0800,
"Casey T. Deccio" <ctdecci@sandia.gov> wrote:
>
> In this case each bldg has an owner associated to it, and each animal
> lives in some bldg. Each owner has exactly one own favorite animal out
> of all the bldgs owned by him. So the constraint added to each zoo row
> is that the boolean field 'favorite' is true for exactly once for each
> group of animals in the zoo that have a common owner.
One way to do this kind of thing is to add a UNIQUE key to the table
that holds the animal - table relation of the animal and table and add
a UNIQUE key for the building table of the building and owner.
Then in the owner table add favorite animal building and favorite
animal columns with the property NOT NULL. Then add two deferable
foreign keys (owner, favorite animal building) to the building table
and (favorite animal building, favorite animal) to the animal - building
table. When modifying data you want to change favorites before changing
the underlying tables (where a building or animal change affects someones
favorites).
P.S. in your example you used varchar(50) for the text strings. Using 'text'
is better unless there is a business rule limiting the name lengths to 50
or you expect to port the application to another rdbms.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: table constraints
@ 2005-02-28 20:23 Casey T. Deccio <ctdecci@sandia.gov>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 11+ messages in thread
From: Casey T. Deccio @ 2005-02-28 20:23 UTC (permalink / raw)
To: pgsql-sql
On Mon, 2005-02-28 at 13:20 -0700, Bruno Wolff III wrote:
> On Mon, Feb 28, 2005 at 11:28:30 -0800,
> "Casey T. Deccio" <ctdecci@sandia.gov> wrote:
> >
> > In this case each bldg has an owner associated to it, and each
> animal
> > lives in some bldg. Each owner has exactly one own favorite animal
> out
> > of all the bldgs owned by him. So the constraint added to each zoo
> row
> > is that the boolean field 'favorite' is true for exactly once for
> each
> > group of animals in the zoo that have a common owner.
>
> One way to do this kind of thing is to add a UNIQUE key to the table
> that holds the animal - table relation of the animal and table and add
> a UNIQUE key for the building table of the building and owner.
> Then in the owner table add favorite animal building and favorite
> animal columns with the property NOT NULL. Then add two deferable
> foreign keys (owner, favorite animal building) to the building table
> and (favorite animal building, favorite animal) to the animal -
> building
> table. When modifying data you want to change favorites before
> changing
> the underlying tables (where a building or animal change affects
> someones
> favorites).
>
Thanks for the input. This may work in the example I've given.
However, the example I provided was contrived and was used merely to
show the discrepancy that I'm finding with using the function as a
constraint. In the larger example, things are a bit more complex, and
I've found using such a constraint a better fit for now for the problem
I'm working with.
That said, I'd like to know why the constraint I provided isn't working
with the corresponding example.
> P.S. in your example you used varchar(50) for the text strings. Using
> 'text'
> is better unless there is a business rule limiting the name lengths to
> 50
> or you expect to port the application to another rdbms.
Thanks for the tip. Good point. I've never really known when to use
text over varchar, so though I began using varchar for potential later
porting to other rdbms, and since then it's just been tradition...:)
Casey
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: table constraints
@ 2005-03-01 01:01 Andrew - Supernews <andrew+nonews@supernews.com>
parent: Casey T. Deccio <ctdecci@sandia.gov>
0 siblings, 1 reply; 11+ messages in thread
From: Andrew - Supernews @ 2005-03-01 01:01 UTC (permalink / raw)
To: pgsql-sql
On 2005-02-28, "Casey T. Deccio" <ctdecci@sandia.gov> wrote:
> However, the example I provided was contrived and was used merely to
> show the discrepancy that I'm finding with using the function as a
> constraint. In the larger example, things are a bit more complex, and
> I've found using such a constraint a better fit for now for the problem
> I'm working with.
>
> That said, I'd like to know why the constraint I provided isn't working
> with the corresponding example.
The CHECK is obviously being evaluated prior to the actual insertion of
the record, whereas the logic of your function clearly expects to be
evaluated after the insertion.
Allowing non-immutable functions in CHECK is probably an error, since it
can lead to tables which can not be dumped+restored (consider in your
example what happens when the constraint becomes false as a result of
deleting a row - at that point, a dump and restore of the table will fail,
since the constraint can not be defined after loading the data if it is
violated by that data).
Using triggers is a more reliable way to do this sort of thing - at least
then it is clear that you are checking the data only at the time of
modification, whereas CHECK constraints are declarative constraints which
are expected to be true at all times.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: table constraints
@ 2005-03-01 01:25 Casey T. Deccio <ctdecci@sandia.gov>
parent: Andrew - Supernews <andrew+nonews@supernews.com>
0 siblings, 0 replies; 11+ messages in thread
From: Casey T. Deccio @ 2005-03-01 01:25 UTC (permalink / raw)
To: andrew@supernews.com; +Cc: pgsql-sql
On Mon, 2005-02-28 at 18:01 -0700, Andrew - Supernews wrote:
> The CHECK is obviously being evaluated prior to the actual insertion
> of
> the record, whereas the logic of your function clearly expects to be
> evaluated after the insertion.
>
I finally came to that conclusion just a few minutes before I got this
email. However, it was your email that made it quite clear why this
was.
> Allowing non-immutable functions in CHECK is probably an error, since
> it
> can lead to tables which can not be dumped+restored (consider in your
> example what happens when the constraint becomes false as a result of
> deleting a row - at that point, a dump and restore of the table will
> fail,
> since the constraint can not be defined after loading the data if it
> is
> violated by that data).
> Using triggers is a more reliable way to do this sort of thing - at
> least
> then it is clear that you are checking the data only at the time of
> modification, whereas CHECK constraints are declarative constraints
> which
> are expected to be true at all times.
>
This is a very good point. I've now implemented the check with a
trigger, and it works very well. Thanks for the input and the logic.
Casey
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: table constraints
@ 2005-03-01 16:56 Greg Patnude <gpatnude@hotmail.com>
parent: Casey T. Deccio <ctdecci@sandia.gov>
1 sibling, 1 reply; 11+ messages in thread
From: Greg Patnude @ 2005-03-01 16:56 UTC (permalink / raw)
To: pgsql-sql
foreign keys and primary keys have to be defined as unique at the table /
column level if you want to implement a check constraint -- your contrived
example doesn't stand up all that well -- If you want to use constraints --
then your database schema should conform to traditional RDBMS theory and
data normalization by having primary and foreign keys instead of just
trying to create arbitrary contraints on a non-normalized schema and
implement constraints as a user-defined function...
""Casey T. Deccio"" <ctdecci@sandia.gov> wrote in message
news:1109618910.32166.0.camel@boomerang.ran.sandia.gov...
> Hi,
>
> I am running PostgreSQL 7.4.7. I am having some issues with a
> constraint for one of my database tables. The code snippet below
> outlines the code and its output (output is commented).
>
> In this case each bldg has an owner associated to it, and each animal
> lives in some bldg. Each owner has exactly one own favorite animal out
> of all the bldgs owned by him. So the constraint added to each zoo row
> is that the boolean field 'favorite' is true for exactly once for each
> group of animals in the zoo that have a common owner.
>
> The unique_favorite(text) function is created to help with this
> constraint. The function returns what it is supposed to (see select
> statement in the code), but doesn't hold when used as a constraint.
>
> There may be a better way to do this, but I'm not looking for that right
> now. I would like to know if I am using this constraint wrong, or if
> there is there something wrong with the table constraints in the
> database system.
>
> Thanks,
> Casey
>
> ------ Code snippet -------
> CREATE TABLE owner (owner varchar(50));
> -- CREATE TABLE
> INSERT INTO owner VALUES ('steve');
> -- INSERT 13193166 1
>
> CREATE TABLE bldg (bldg varchar(50), owner varchar(50));
> -- CREATE TABLE
> INSERT INTO bldg VALUES ('bldg1', 'steve');
> -- INSERT 13193169 1
>
> CREATE TABLE zoo (animal varchar(50), bldg varchar(50), favorite
> boolean);
> -- CREATE TABLE
>
> CREATE OR REPLACE FUNCTION unique_favorite(text) RETURNS BOOLEAN AS '
> DECLARE
> temp RECORD;
> BEGIN
> SELECT into temp * FROM
> (SELECT b.owner, sum(CASE WHEN favorite = TRUE THEN 1 ELSE 0 END) AS
> num_favorites
> FROM (SELECT * FROM zoo WHERE animal = $1) z INNER JOIN bldg b
> USING(bldg) GROUP BY b.owner) sub
> WHERE sub.num_favorites <> 1;
> RETURN NOT FOUND;
> END;
> ' LANGUAGE plpgsql;
> -- CREATE FUNCTION
>
> ALTER TABLE zoo ADD check(unique_favorite(animal));
> -- ALTER TABLE
>
> INSERT into zoo VALUES ('monkey', 'bldg1', false);
> -- INSERT 13193173 1
> -- (This shouldn't be allowed!!!)
>
> SELECT *, unique_favorite(animal) FROM zoo;
> -- animal | bldg | favorite | unique_favorite
> -- --------+-------+----------+-----------------
> -- monkey | bldg1 | f | f
>
> INSERT into zoo VALUES ('monkey', 'bldg1', false);
> -- ERROR: new row for relation "zoo" violates check constraint "$1"
>
> INSERT into zoo VALUES ('monkey', 'bldg1', true);
> -- ERROR: new row for relation "zoo" violates check constraint "$1"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: table constraints
@ 2005-03-02 16:14 Casey T. Deccio <ctdecci@sandia.gov>
parent: Greg Patnude <gpatnude@hotmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Casey T. Deccio @ 2005-03-02 16:14 UTC (permalink / raw)
To: Greg Patnude <gpatnude@hotmail.com>; +Cc: pgsql-sql
On Tue, 2005-03-01 at 09:56 -0700, Greg Patnude wrote:
> foreign keys and primary keys have to be defined as unique at the
> table /
> column level if you want to implement a check constraint -- your
> contrived
> example doesn't stand up all that well -- If you want to use
> constraints --
> then your database schema should conform to traditional RDBMS theory
> and
> data normalization by having primary and foreign keys instead of just
> trying to create arbitrary contraints on a non-normalized schema and
> implement constraints as a user-defined function...
>
You are correct. I did not take the time to write in these constraints
in the contrived example because I was rapidly trying to put together
something that would simply illustrate the problem. They were/are in
place in my actual schema. Thanks,
Casey
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-03-02 16:14 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-28 19:00 Table constraints Oleg Lebedev <olebedev@waterford.org>
2002-03-28 19:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-28 20:38 ` Oleg Lebedev <olebedev@waterford.org>
2002-03-28 20:37 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2005-02-28 19:28 table constraints Casey T. Deccio <ctdecci@sandia.gov>
2005-02-28 20:20 ` Re: table constraints Bruno Wolff III <bruno@wolff.to>
2005-02-28 20:23 ` Re: table constraints Casey T. Deccio <ctdecci@sandia.gov>
2005-03-01 01:01 ` Re: table constraints Andrew - Supernews <andrew+nonews@supernews.com>
2005-03-01 01:25 ` Re: table constraints Casey T. Deccio <ctdecci@sandia.gov>
2005-03-01 16:56 ` Re: table constraints Greg Patnude <gpatnude@hotmail.com>
2005-03-02 16:14 ` Re: table constraints Casey T. Deccio <ctdecci@sandia.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox