Should columns in a junction table be able to store null vales?
When creating a junction table 开发者_如何转开发in sql to handle a many-to-many releationship between two tables, should the foreign key columns in the junction table be able to store null values?
It is a bad idea to do that because it stores no information.
A junction table is a link between 2 tables. If a record exists, by definition it must have the id from both sides to make a "junction" link. Otherwise it carries no useful information and is known as a waste-of-space
.TM
No. It doesn't make sense to store a row representing the absence of a relationship in a table you designed to store the presence of relationships.
In addition to the other answers:
the two columns referencing the other tables are usually the primary key of that junction table. So per definition they cannot be null.
There are some circumstances where those columns do not make up the complete primary key (e.g. when having an attribute as part of the link and allowing multiple links with different attributes) - but then that attribute is part of the PK.
精彩评论