Composite Foreign key question

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Wed, 31 Oct 2001 16:47:59 GMT
Message-ID: <3be0284f.1735268578@news.alt.net>

1. There are two table that have two columns. Both of the second table's columns reference both of the first table's columns. But the second column in the SECOND table can be NULL.

CREATE TABLE A (

	A NUMBER,
	B NUMBER,
	PRIMARY KEY (A, B)

);

CREATE TABLE B (

	A NUMBER CONSTRAINT Just_A REFERENCES A,
	B NUMBER,
	CONSTRAINT Both_A_And_B FOREIGN KEY (A, B) REFERENCES A (A, B)

);

The "real" FOREIGN KEY is Both_A_And_B. However, since in one case B can be NULL, I need the other FOREIGN KEY (Just_A) to keep things straight.

I think that would be how to do it. Any comments? I'd like to know if I am missing something.

2) There are two table that have two columns. Both of the second table's columns reference both of the first table's columns. But the second column in the FIRST table can be NULL.

CREATE TABLE A (

	A NUMBER PRIMARY KEY,
	B NUMBER,
	UNIQUE (A, B)

);

CREATE TABLE B (

	A NUMBER,
	B NUMBER,
	PRIMARY KEY (A, B)
	CONSTRAINT Both_A_And_B FOREIGN KEY (A, B) REFERENCES A (A, B)

);

How could I FOREIGN KEY A, B to reference A,B (in the first table) unless B is NULL in the first table, in which I would only want to FOREIGN KEY A. Brian Received on Wed Oct 31 2001 - 10:47:59 CST