pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConstraint problems
3+ messages / 2 participants
[nested] [flat]
* Constraint problems
@ 2002-09-27 18:15 GB Clark <postgres@vsservices.com>
2002-09-27 18:29 ` Re: Constraint problems Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: GB Clark @ 2002-09-27 18:15 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm trying to create a constraint that will check to see if the inserted data is found
in another column in the table.
It could be that I'm going about this the wrong way, any clues for me?
Here is the present setup data for the table.
--SNIP--
--
--
--
DROP SEQUENCE sysusers_user_id_seq;
CREATE SEQUENCE sysusers_user_id_seq START 1000;
DROP TABLE sysusers;
CREATE TABLE sysusers (
user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
UNIQUE NOT NULL PRIMARY KEY,
username text NOT NULL UNIQUE,
password text NOT NULL,
createby text NOT NULL DEFAULT 'syscreate',
status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
comment1 text,
comment2 text,
tstamp timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing', 'syscreate');
ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
INSERT INTO sysusers (username, password) VALUES ('gclarkii', 'testing');
--SNIP--
It is the constraint on createby that I'm trying to get to work.
Thanks for any and all help.
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Constraint problems
2002-09-27 18:15 Constraint problems GB Clark <postgres@vsservices.com>
@ 2002-09-27 18:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-27 19:56 ` Re: Constraint problems GB Clark <postgres@vsservices.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2002-09-27 18:29 UTC (permalink / raw)
To: GB Clark <postgres@vsservices.com>; +Cc: pgsql-sql
On Fri, 27 Sep 2002, GB Clark wrote:
> CREATE TABLE sysusers (
> user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> UNIQUE NOT NULL PRIMARY KEY,
> username text NOT NULL UNIQUE,
> password text NOT NULL,
>
> createby text NOT NULL DEFAULT 'syscreate',
>
> status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
> comment1 text,
> comment2 text,
> tstamp timestamp DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
>
> INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing', 'syscreate');
>
> ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
This is only going to check the value of username in the current row.
Why not use a foreign key from createby to username?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Constraint problems
2002-09-27 18:15 Constraint problems GB Clark <postgres@vsservices.com>
2002-09-27 18:29 ` Re: Constraint problems Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-09-27 19:56 ` GB Clark <postgres@vsservices.com>
0 siblings, 0 replies; 3+ messages in thread
From: GB Clark @ 2002-09-27 19:56 UTC (permalink / raw)
To: pgsql-sql
On Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> On Fri, 27 Sep 2002, GB Clark wrote:
>
> > CREATE TABLE sysusers (
> > user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> > UNIQUE NOT NULL PRIMARY KEY,
> > username text NOT NULL UNIQUE,
> > password text NOT NULL,
> >
> > createby text NOT NULL DEFAULT 'syscreate',
> >
> > status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
> > comment1 text,
> > comment2 text,
> > tstamp timestamp DEFAULT CURRENT_TIMESTAMP
> > );
> >
> > CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
> >
> > INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing', 'syscreate');
> >
> > ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
>
> This is only going to check the value of username in the current row.
>
> Why not use a foreign key from createby to username?
>
Thanks! The FK was just what I was looking for. I had not relized that I could
do a current table reference in a FK declaration.
Thanks,
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-09-27 19:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-27 18:15 Constraint problems GB Clark <postgres@vsservices.com>
2002-09-27 18:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-27 19:56 ` GB Clark <postgres@vsservices.com>
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