Foreign key null - performance degradation

问题:

I have table folder where column parent_id references on id if that folder has parent, if not then parent_id is null. Is that ok solution or I need extra table for this connection or other solution? Can foreign key be null at all, and if can is this solution will has bigger time execution ?

table folder(
   id int primary key,            //primary key in my table
   parent_id int references id,   //foreign key on id column in same table
....
)

回答1:

Yes, a foreign key can be made to accept NULL values:

CREATE TABLE folders (
   id           int   NOT NULL   PRIMARY KEY, 
   parent_id    int   NULL, 
   FOREIGN KEY (parent_id) REFERENCES folders (id)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

INSERT INTO folders VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

Execution time is not affected if a foreign key is set to accept NULL values or not.

---

UPDATE: Further to comment below:

Keep in mind that B-tree indexes are most effective for high-cardinality data (i.e. columns with many possible values, where the data in the column is unique or almost unique). If you will be having many NULL values (or any other repeated value), the query optimizer might choose not to use the index to filter the records for your result set, since it would be faster not to. However this problem is independent of the fact that the column is a foreign key or not.

回答2:

You can have NULL foreign keys. No problems. I would not put an extra table just for folders without a parent (root folders). It will make your design more complicated with no benefits.