Re: Generating tables with relationships in arc and part of primary key, bug?

From: Reid Lai <reidlai_at_hk.super.net>
Date: 1996/06/15
Message-ID: <4ptbp3$t7n_at_tst.hk.super.net>#1/1

meldahl_at_pvv.unit.no (Ole Christian Meldahl) wrote:

>I have three entites A, B and C. A relates to B and C with an arc and
>(B,C)one-to-many(A). Both relationships are part of the UID of A.
 

>Like this:
>-- optional relationship
>| Part of UID
>( arc
>< many
 

> B ----|-(-< A >-)-|--- C
 

>B: #B_ID
>C: #C_ID
 

>A: #A_ID
> #B_ID
>(or) #C_ID

>Hence my primary key on A should be
 

>A_ID NOT NULL
>B_ID NULL
>C_ID NULL
 
>This is (probably) not possible for a primary key, but a unique key
>should solve it?
 

>My actual problem relates to generation of tables from the entities.
>The Database Design Wizard gives table A with B_ID and C_ID as NULL
>and part of primary key. The Server Generator gives DDL accordingly
>but when the DDL is executed the status of B_ID and C_ID go from NULL
>to NOT NULL, hence making it impossible to enforce a mutually
>exclusive relationship.
 

>My conclusion is that the Generator/Wizard does not realize the
>problem with arcs and primary keys.
 

>If so, it's a bug! (IMHO, of course) The Wizard should have generated
>an unique key instead.
 

>Would it help if the relationships were mandatory?
 

>Any input appreciated!
 

>oli
 

>PS: Des2k 1.2
> oracle v7.1
>--
>Ole Christian Meldahl
>"Sailor"
>Norwegian High Command, Information Systems
>meldahl_at_pvv.unit.no

I think this is not a bug. In relational DBMS, referenced entities in a relationship must reference to the primary key of their referencing entities. For primary key definition, all composite key must be wholly null or wholly not null. Of course, the wholly null key means the key is non-exist.

For your problem, you can try the following:

Table A

---

CREATE TABLE A (

A_ID  		CHAR(10)	NOT NULL,
A_FLAG	CHAR(1)	NOT NULL,
B_ID		CHAR(10)	NULL,
C_ID		CHAR(10)	NULL,

PRIMARY KEY ( A_ID, A_FLAG) ) ; Table B

---

CREATE TABLE B (

A_ID		CHAR(10)	NOT NULL
A_FLAG	CHAR(10)	NOT NULL,
B_ID		CHAR(10)	NOT NULL 

PRIMARY KEY ( A_ID, A_FLAG, B_ID ),
FOREIGN KEY ( A_ID, A_FLAG) REFERENCES A ) ; Table C

---

CREATE TABLE C (

A_ID		CHAR(10)	NOT NULL
A_FLAG	CHAR(10)	NOT NULL,
C_ID		CHAR(10)	NOT NULL 

PRIMARY KEY ( A_ID, A_FLAG, C_ID ),
FOREIGN KEY ( A_ID, A_FLAG) REFERENCES A ) ; In table A, you can add check option for A_FLAG in ( 'B', 'C', 'O' ) where 'O' means other value.

I hope this can help you.

Best Regards,

Reid Lai
reidlai_at_hk.super.net Received on Sat Jun 15 1996 - 00:00:00 CEST