Re: Constraint problems

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?