Re: SQL question

From: Walt <walt_askier_at_YerBootsyahoo.com>
Date: Wed, 04 May 2005 17:49:41 -0400
Message-ID: <Crbee.1241$II.89@news.itd.umich.edu>

Andy Hassall wrote:
> On Wed, 04 May 2005 08:56:16 +0200, Sybrand Bakker wrote:

>> Andreas Mosmann<keineemails_at_gmx.de> wrote:
>>
>>>Only for question, did anybody try this out for self referencing tables, 
>>>especially for those, that contain a selfreference in one row?
>>
>>Selfreference in one row has never been allowed in Oracle, the
>>reference being cyclic.

>
>
> SQL> create table t (
> 2 c number not null,
> 3 constraint t_pk primary key (c),
> 4 constraint t_fk1 foreign key (c)
> 5 references t (c)
> 6 );
>
> Table created.
>
> SQL> insert into t values (1);
>
> 1 row created.
>
> SQL> select * from t;
>
> C
> ----------
> 1
>
> SQL> delete from t where c=1;
>
> 1 row deleted.
>
> SQL> select constraint_name, r_constraint_name
> 2 from user_constraints
> 3 where table_name = 'T'
> 4 and constraint_type = 'R';
>
> CONSTRAINT_NAME R_CONSTRAINT_NAME
> ------------------------------ ------------------------------
> T_FK1 T_PK
>
>
> It's perfectly happy, you don't even have to mess with deferrable constraints
> to get a self-referencing column working (same table, same row). How useful
> such a thing is, is another matter.

Agreed; a field that is foreign keyed to itself appears to have limited utility. But the OP was asking about a table with two fields, one of which is foreign keyed to the other. Adapting your nomenclature, that seems to work:

SQLWKS> create table t (

      2>   c1 number not null,
      3>   c2 number not null,
      4>   constraint t_pk primary key (c1),
      5>   constraint t_fk1 foreign key (c2) references t (c1) );

Statement processed.
SQLWKS> insert into t values (1,1);
1 row processed.
SQLWKS> insert into t values (2,2);
1 row processed.

SQLWKS> insert into t values (2,3);
ORA-00001: unique constraint (T_PK) violated
SQLWKS> insert into t values (3,2);

1 row processed.
SQLWKS> select * from t;
C1 C2
---------- ----------

          1          1
          2          2
          3          2

3 rows selected.

Not sure what others are talking about with regard to self refrencing being disallowed. I'm using 9.2/W2k3 if that makes any difference.

-- 
//-Walt
//
//

Received on Wed May 04 2005 - 16:49:41 CDT