pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedchanging constraints
5+ messages / 4 participants
[nested] [flat]
* changing constraints
@ 2004-03-10 12:27 David <de4@kent.ac.uk>
2004-03-10 13:07 ` Re: changing constraints Paul Thomas <paul@tmsl.demon.co.uk>
2004-03-10 13:41 ` Re: changing constraints Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-03-10 13:51 ` Re: changing constraints Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 5+ messages in thread
From: David @ 2004-03-10 12:27 UTC (permalink / raw)
To: pgsql-sql
I tried adding a constraint thus:
de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
But get the msg:
ERROR: AlterTableAddConstraint: rejected due to CHECK constraint $2
de4=> \d genus
Table "public.genus"
Column | Type | Modifiers
----------------+-----------------------+-----------
genus_name | character varying(20) | not null
gender | character varying(10) |
cas_gen_number | integer |
family_name | character(7) |
Indexes: genus_pkey primary key btree (genus_name)
Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION
I cant see a $2 constraint so why am i getting the error msg?
Many thanks Dave
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing constraints
2004-03-10 12:27 changing constraints David <de4@kent.ac.uk>
@ 2004-03-10 13:07 ` Paul Thomas <paul@tmsl.demon.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Paul Thomas @ 2004-03-10 13:07 UTC (permalink / raw)
To: David <de4@kent.ac.uk>; +Cc: pgsql-sql
On 10/03/2004 12:27 David wrote:
> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR: AlterTableAddConstraint: rejected due to CHECK constraint $2
>
> de4=> \d genus
> Table "public.genus"
> Column | Type | Modifiers
> ----------------+-----------------------+-----------
> genus_name | character varying(20) | not null
> gender | character varying(10) |
> cas_gen_number | integer |
> family_name | character(7) |
> Indexes: genus_pkey primary key btree (genus_name)
> Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
> family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> I cant see a $2 constraint so why am i getting the error msg?
>
> Many thanks Dave
Just guessing but maybe it's because gender is nullable?
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing constraints
2004-03-10 12:27 changing constraints David <de4@kent.ac.uk>
@ 2004-03-10 13:41 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2004-03-10 13:41 UTC (permalink / raw)
To: David <de4@kent.ac.uk>; +Cc: pgsql-sql
O kyrios David egrapse stis Mar 10, 2004 :
> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR: AlterTableAddConstraint: rejected due to CHECK constraint $2
Which pgsql version are you using??
In any case what you wrote means CHECK (gender='masculinefeminine')
you should write it as gender = 'masculine' OR gender = 'feminine'.
>
> de4=> \d genus
> Table "public.genus"
> Column | Type | Modifiers
> ----------------+-----------------------+-----------
> genus_name | character varying(20) | not null
> gender | character varying(10) |
> cas_gen_number | integer |
> family_name | character(7) |
> Indexes: genus_pkey primary key btree (genus_name)
> Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
> family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> I cant see a $2 constraint so why am i getting the error msg?
>
> Many thanks Dave
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
-Achilleus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing constraints
2004-03-10 12:27 changing constraints David <de4@kent.ac.uk>
@ 2004-03-10 13:51 ` Richard Huxton <dev@archonet.com>
2004-03-10 15:57 ` Re: changing constraints David <de4@kent.ac.uk>
2 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2004-03-10 13:51 UTC (permalink / raw)
To: David <de4@kent.ac.uk>; pgsql-sql
On Wednesday 10 March 2004 12:27, David wrote:
> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR: AlterTableAddConstraint: rejected due to CHECK constraint $2
> I cant see a $2 constraint so why am i getting the error msg?
Firstly, $2 will be the automatically created name for this new constraint.
Secondly || doesn't mean OR - it joins strings.
Try something like (untested):
ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN
('masculine','feminine'));
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing constraints
2004-03-10 12:27 changing constraints David <de4@kent.ac.uk>
2004-03-10 13:51 ` Re: changing constraints Richard Huxton <dev@archonet.com>
@ 2004-03-10 15:57 ` David <de4@kent.ac.uk>
0 siblings, 0 replies; 5+ messages in thread
From: David @ 2004-03-10 15:57 UTC (permalink / raw)
To: pgsql-sql
Cheers that worked fine, i guess its obvious im new to postgres (SQL in
general!), oh well you have to learn somehow
Dave
>
> Try something like (untested):
> ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN
> ('masculine','feminine'));
>
>
> --
> Richard Huxton
> Archonet Ltd
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-03-10 15:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-10 12:27 changing constraints David <de4@kent.ac.uk>
2004-03-10 13:07 ` Paul Thomas <paul@tmsl.demon.co.uk>
2004-03-10 13:41 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-03-10 13:51 ` Richard Huxton <dev@archonet.com>
2004-03-10 15:57 ` David <de4@kent.ac.uk>
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