Quick SQL*Plus/Oracle-oriented question

[Aus1423]

I know this is a dumb question BUT........

A foreign key attribute in one table must be NULL or reference a primary key in another table. How can this be? It can't be NULL if it references another table as a primary key :confused: TIA!

[Grizzly]

Yeeaaaah, but that's just the nature of foreign keys. I've run into database situations myself where I was thankful about how foreign keys treat NULL's.

Remember....NULL is *not* a value. It's simply NULL, aka...nothing. A foreign key simply checks the field in question...and IF it has a value, it will ensure that there is a matching value in the foreign table. If there is no value (NULL), than there is nothing to validate.

Make sense?

[bryce777]

Originally posted by Aus1423
I know this is a dumb question BUT........

A foreign key attribute in one table must be NULL or reference a primary key in another table. How can this be? It can't be NULL if it references another table as a primary key :confused: TIA!
What grizzly said.

Also, if something should not be null it sould be declared NOT NULL int he ddl.

[Aus1423]

So basically foreign keys aren't unique to the entire column but just to specific values? Is this what you guys are saying?

Say for instance an entire column of foreign keys in a table is seen as primary keys in another table (displayed in the same order no less) then they should all be NOT NULL values. But take a totally separate unrelated table that has foreign keys with alot of NULLs, so can that column not have ANY reference to another table? Seems they shouldn't.

I can't see using some foreign keys for another table and not others if there are NULLs. I'm used to seeing an entire column of attributes being used as primary keys in another table. Thats why I'm confused.

Should I look at it like the entire column is referenced or is NULL or just as individual values being one or the other?

[krack_it_up]

I cant quite understand what you are asking... couldyou maybe explain it with examples?

[bryce777]

Originally posted by Aus1423
So basically foreign keys aren't unique to the entire column but just to specific values? Is this what you guys are saying?

Say for instance an entire column of foreign keys in a table is seen as primary keys in another table (displayed in the same order no less) then they should all be NOT NULL values. But take a totally separate unrelated table that has foreign keys with alot of NULLs, so can that column not have ANY reference to another table? Seems they shouldn't.

I can't see using some foreign keys for another table and not others if there are NULLs. I'm used to seeing an entire column of attributes being used as primary keys in another table. Thats why I'm confused.

Should I look at it like the entire column is referenced or is NULL or just as individual values being one or the other?

being nullable and being keys into other tables are somewhat seperate issues.

You can also have both, and you can have value oriented constraints on tables (which programmers hate but DBAs sometimes like).

[Grizzly]

Originally posted by bryce777


being nullable and being keys into other tables are somewhat seperate issues.

You can also have both, and you can have value oriented constraints on tables (which programmers hate but DBAs sometimes like).

Well said, and as both a programmer and DBA, I like value oriented constraints quite a bit. There's no better way to ensure data integrity than having the database enforce relationships for you. Trying to enforce data integrity programmatically will often mess up somewhere down the line, since no programmer is 100% perfect.