Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Creating a table and a constraint

Re: Creating a table and a constraint

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Sat, 27 Mar 2004 05:16:28 GMT
Message-ID: <M689c.126044$Wa.12682@news-server.bigpond.net.au>


"JellyBum" <......@......com> wrote in message news:qC_8c.3$bO3.2_at_newsfe2-gui.server.ntli.net...
>
> "Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
> news:_YS8c.125222$Wa.105704_at_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
> >
> >
>
> i understand that, but how does a compare follow from that. Ive found that
> you cant do say name1=name2 in most versions of oracle, mine including.
>
>

Maybe an example will help in clarifying matters. I had inserted two rows previously into the table. The contents are shown below (the manager of the CEO is NULL):

SQL> SELECT * FROM systemusers;

STA MAN
--- ---
CEO
VP1 CEO Next, I try to insert a row that has an invalid MANAGER_ID ( the value, 'YYY', does not exist in the MANAGER_ID column ).

SQL> INSERT INTO systemusers
  2 ( staff_id, manager_id )
  3 VALUES ( 'XXX', 'YYY' );
INSERT INTO systemusers
*
ERROR at line 1:
ORA-02291: integrity constraint (TEST_USER.SYSTEMUSERS_MGR_ID_FK) violated - parent key not found

So the foreign key constraint enforces the business rule that the manager_id must exist as the staff_id in the table. Here the Oracle server is doing the comparison between the columns for you.

I can even enter a self-employed person (someone is who is their own boss) into the table.

SQL> INSERT INTO systemusers
  2 ( staff_id, manager_id )
  3 VALUES ( 'XXX', 'XXX' ); 1 row created.

Douglas Hawthorne Received on Fri Mar 26 2004 - 23:16:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US