Primary key cannot be null, this is a rule. But why foreign key can? If it is also a primary key of another table.
Is it because it is not from the main table?
- 1MySQL, SQL Server or SQLite? They are 3 different RDBMS - please tag only the RDBMS of interest.Dale K– Dale K2020-04-27 00:17:35 +00:00Commented Apr 27, 2020 at 0:17
- 1@Irene . . . I removed the inconsistent database tags. You should tag only with the database you are really using -- even though the answer doesn't actually depend on the database in this case.Gordon Linoff– Gordon Linoff2020-04-27 00:18:22 +00:00Commented Apr 27, 2020 at 0:18
- Most of this is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Also ask exactly 1 clear researched non-duplicate question. Also "why" is not a meaningful question--it has no clear answer--ask "what". Also that "why" is probably not what you actually mean to ask. Please make a clear question about whatever you are trying to talk about in the last 2 sentences of your post. Find out how PKs & FKs work first. See How to Ask, other help center links & the voting arrow mouseover texts.philipxy– philipxy2020-04-27 05:12:31 +00:00Commented Apr 27, 2020 at 5:12
- meta.stackoverflow.com/questions/323334/… meta.stackexchange.com/questions/214348/… meta.stackoverflow.com/questions/323005/… meta.stackexchange.com/questions/165952/… etc etcphilipxy– philipxy2020-04-27 07:07:53 +00:00Commented Apr 27, 2020 at 7:07
Add a comment |
2 Answers
Pointing to Primary Keys
Foreign keys can point to primary keys of other/self tables. If it's null (or partially null), then it means it's doesn't have a referenced row.
Pointing to Unique Constraints
Foreign keys can also point to unique constraints as well; and these unique constraints can actually be nullable. Look at the example below:
create table person ( id int primary key not null, first_name varchar(20), last_name varchar(20), constraint uq1 unique (first_name, last_name) ); insert into person (id, first_name, last_name) values (1, 'Peter', 'Cantropus'); insert into person (id, first_name, last_name) values (2, 'Chad', 'Ensis'); insert into person (id, first_name, last_name) values (3, null, null); create table car ( brand varchar(30), owner_first_name varchar(20), owner_last_name varchar(20), constraint fkoc1 foreign key (owner_first_name, owner_last_name) references person(first_name, last_name) ); insert into car (brand, owner_first_name, owner_last_name) values ('Renault', 'Peter', 'Cantropus'); insert into car (brand, owner_first_name, owner_last_name) values ('Lada', null, null); insert into car (brand, owner_first_name, owner_last_name) values ('Sumi', 'Chad', null); select * from car; Result:
id first_name last_name -- ---------- --------- 1 Peter Cantropus 2 Chad Ensis 3 <null> <null> brand owner_first_name owner_last_name ------- ---------------- --------------- Renault Peter Cantropus -- not null: has a referenced row Lada <null> <null> -- null: not referencing Sumi Chad <null> -- partially null: not referencing As you see:
- Only the fully not null foreign key references a row in the other table.
- The partially null row does not reference a row in the other table (though may be partially validated under some circumstances).
- The totally null row does not reference a row in the other table either. Even, considering there's a unique key that is (null, null).
Comments
The nullability of foreign keys is determined by the NULL constraint. That is, foreign keys can be NULL, indicating no relationship.
Of course, primary keys can never be NULL, so there is no danger that a NULL value would be confused with a match.
