Re: Creating a table and a constraint

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Fri, 26 Mar 2004 09:45:30 GMT
Message-ID: <_YS8c.125222$Wa.105704@news-server.bigpond.net.au>

"JellyBum" <......@......com> wrote in message news:vyS8c.200$X1.44_at_newsfe1-win...
> Hi there, having a problem working out whats needed here.
>
> Im trying to create a table, this table is allowed to be null, but if its
> not then it must meet some other criteria. I know basically what im doing
> but cant geta few things im trying todo.
> So far ive got:
>
> CREATE TABLE SystemUsers ( Staff_id char (3) CONSTRAINT pk_SU Primary Key
> ( Staff_id), Manager_id char (3)
> now need it so, if a value has been entered into Manager_id it must match
a
> value in Staff_id.
>
> Havent got a clue how to go about this? Cant find this specific thing in
my
> book either. Any help is apreciated
>
>

Try the following:

CREATE TABLE SystemUsers

   (

      Staff_id
         CHAR(3)
         CONSTRAINT systemusers_pk PRIMARY KEY,
      Manager_id
         CHAR(3),
      CONSTRAINT systemusers_mgr_id_fk
         FOREIGN KEY( manager_id )
         REFERENCES systemusers( staff_id )

   )
;

What you want is a foreign key constraint pointing back to the same table. (A self-referential constraint).

Douglas Hawthorne Received on Fri Mar 26 2004 - 03:45:30 CST