Thread: Constraint problems

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?

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?

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?