Foreign key vs. required

[hbrems]

Quick question: is there any use to setting a foreign key as required when the relationship already enforces referential integrity? Or could this have strange side effects?

 

[gemma-the-husky]

what actually happens when you create a relationship, is that access creates a hidden "foreign" key for the linking field(s) of the many table. (There is an attribute called "foreign" for an index) - and the index doesn't show in the list of indexes.

i am not sure what you mean by required.

 

[hbrems]

I am not sure what you mean by required.

In table design you will find this option in the field properties. You can set it to yes/no. Other properties include length, allow empty string, etc...

 

[hbrems]

After a bit of testing it seems that referential integrity has nothing to do with required fields. You can set up referential integrity and still have the foreign key field set as optional.

Unless anyone has any further comments to make about this I think I'm ok on this one.

 

[gemma-the-husky]

required at the table level determines whether a value HAS to be entered, or can be left null.

I am not sure whether a null value will defeat RI. It probably will, knowing the way nulls work!

I think it's foreign key that's confusing me. I am thinking index - I take it you mean the field value itself.

 

[dportas]

After a bit of testing it seems that referential integrity has nothing to do with required fields. You can set up referential integrity and still have the foreign key field set as optional.

Unless anyone has any further comments to make about this I think I'm ok on this one.

Setting a column to be Required=No means that the attribute is nullable (nulls are permitted). I suggest you avoid allowing nulls in foreign key columns.

A null foreign key means that the constraint is not enforced and this has disadvantages. Queries that expect the constraint to be enforced may give the wrong results. You would expect most users of the database to realise that a foreign key exists but they might not notice it is nullable and even if they do they may not understand that means the constraint isn't enforced. Also, the behaviour of nullable foreign keys in Jet/ACE contradicts many other DBMSs, which could lead to incompatibilities later on or cause confusion among those familiar with standard SQL and other SQL DBMSs.

Move the foreign key to a new table if necessary and make it non-nullable (Required).

 

[Banana]

I suggest you read the article about cascading to null which hopefully brings out the difference between "Required" property (which corresponds to NOT NULL DDL) and foreign key. Personally I don't like the idea of allow null foreign keys and make my foreign keys required most of time.

 

[Banana]

Also, the behaviour of nullable foreign keys in Jet/ACE contradicts many other DBMSs, which could lead to incompatibilities later on or cause confusion among those familiar with standard SQL and other SQL DBMSs.

Examples of this? As far as I know, nullable foreign keys seems to be part of the standard. I just verified that nullable foreign key is allowed on SQL Server 2008 R2 and I'm pretty sure it is true with MySQL & PostgreSQL though I''ve not tested it, nor would I want it anyway.

 

[dportas]

Examples of this? As far as I know, nullable foreign keys seems to be part of the standard. I just verified that nullable foreign key is allowed on SQL Server 2008 R2 and I'm pretty sure it is true with MySQL & PostgreSQL though I''ve not tested it

Nullable foreign keys are definitely part of standard SQL but in Oracle or SQL Server their default behaviour is different to Jet/ACE (not sure about MySQL or PostgreSQL). Try the following in SQL Server and you'll find that the row insert succeeds. Try applying the same constraint in Jet/ACE and the insert is not permitted.

CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
CREATE TABLE t2 (x INT PRIMARY KEY, a INT NULL, b INT NULL, FOREIGN KEY (a,b) REFERENCES t1 (a,b));
INSERT INTO t2 (x,a,b) VALUES (1,2,NULL);

nor would I want it anyway

I wouldn't either.

 

[Banana]

DPortas - thanks for that tidbit. I have to admit I can't remember ever encountering a composite primary key referenced as a foreign key - AFAIK, the behavior is identical when we talk about single-field primary key but I was able to reproduce the error you described with composite primary key.