Re: SQL question

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 04 May 2005 20:54:04 +0100
Message-ID: <jr9i71dq4ejkjr3uidmspkvs7rfks0o0ao@4ax.com>

On Wed, 04 May 2005 08:56:16 +0200, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>On Wed, 04 May 2005 08:49:49 +0200, 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;

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.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Wed May 04 2005 - 14:54:04 CDT