Hi

this is a general question about relational databases, not specific to
Oracle (I think), so if there is a better place to ask then please let me
know.

I have a question about constraints/foreign-keys.

For example:

I have table A and table B.
Table A has a foreign-key which points to a record in table B.

Is it possible to define this foreign-key so it can either be "null" (that
is, there is no related record in table B), or (if it has a value) then it
points to an existing record in table B.

It seems to me I can only define a foreign-key in table A so it must point
to an existing record in table B - it cannot be "null". I can just not
define a foreign-key at all - but then I cannot be sure that if there is a
value it actually points to a record in table B.

Hope you can understand my question.

Thanks,
Peter

Hi Peter,

If your column in table A allows for null values, then you should be fine:
you can add records in A with a null value for the reference column and have
no "master" record in table B.

Valentin Minzatu
v.a.l.e.n.t.i.n.m.i.n.z.a.t.u (AT) y_a_h_o_o (DOT) c_o_m


"Peter Kirk" <peter_no_spam_ (AT) thisisciberus (DOT) dk> wrote

Just add a null record to table B. (Can't be primary key column)

The point of a foreign key is force two tables to stay in sync.

You could dodge this problem by using an outer join in your select between
the two tables, but I don't think that iw what you are trying to do.

Or you can
"Peter Kirk" <peter_no_spam_ (AT) thisisciberus (DOT) dk> wrote

Hi,

In most SQL database engines if the field in table A which references
table B is not marked as "NOT NULL", then you can insert NULL value
into this field and then foreign key constraint is not checked (and you do
not have to insert NULL value into table B or even you should not).

Something like this should work without errors:

CREATE TABLE B (
b_key_field INT PRIMARY KEY,
some_value VARCHAR(100) NOT NULL,
);

CREATE TABLE A (
a_key_field CHAR(5) PRIMARY KEY,
some_value INT NOT NULL,
b_reference INT REFERENCES B(b_key_field)
);

INSERT INTO B ( b_key_field, some_value ) VALUES ( 1, 'beep' );
INSERT INTO B ( b_key_field, some_value ) VALUES ( 2, 'foo' );
INSERT INTO A ( a_key_field, some_value,b_reference ) VALUES ( 'smile', 5, 1 );
INSERT INTO A ( a_key_field, some_value,b_reference ) VALUES ( 'grin ', 0, NULL );


Hilarion


"Peter Kirk" <peter_no_spam_ (AT) thisisciberus (DOT) dk> wrote