There is an int field that may or may not matter.

What is the default value to use in this situation?

NULL is not allowed, and if 0 then this may be the primary key in another table.

  • four
    NULL is impossible, and if 0, then this may be the primary key in another table - this would be more detailed to know what you have in mind. If you have this field to refer to another table, it would be logical to make the foreign key referring to that table, but then NULL is the only right decision, just it can be in the column and not violate foreign - Mike
  • I have a table of departments and users, when adding a new user, he may not be in a department, so by default some value is needed! And on account Null to intovy type unless to use correctly? - quaresma89
  • 3
    Absolutely correct. NULL columns, when stored generally in innodb, are stored in one bit in the page header. Moreover, in the body of the record, 4 bytes of int are not stored, the record becomes shorter. And according to the ideology of relational databases, NULL was introduced specifically for such cases. Well, again, the foreign key should be done so that it would be impossible to include an employee in a program in a non-existent department in the database or delete a department that has employees - Mike

2 answers 2

Once you have a table (T1) that stores a certain value that is the primary key in another table (T2), then setting a special value other than NULL and not in T2 is a strange decision. It certainly has the right to life, but is used in very exotic cases.

In databases, when linking two tables, the value NULL is used to reflect the absence of any value and, therefore, the need for such a link in the record.

In relational databases, it is recommended to make foreign keys ( foreign key ) to reflect the connection of two tables. The presence of such foreign intended to ensure the referential integrity of the database. It does not allow values ​​in a subordinate table to exist that do not exist in the main table. At the same time, the foreign key allows the NULL value to exist - just to reflect the fact that there is no connection.

Recommended table structure (departments and employees):

 create table DEPT ( DEPT_ID INT not null auto_increment, DEPT_NAME VARCHAR(100), primary key (DEPT_ID) ) engine = InnoDB; create table PERSON ( PERSON_ID INT not null auto_increment, NAME VARCHAR(100) not null, DEPT_ID INT, primary key (ID), FOREIGN KEY (DEPT_ID) REFERENCES DEPT(DEPT_ID) ) engine = InnoDB;

In the absence of a department employee to whom he refers DEPT_ID should be NULL

    Use -1 since it will not be in auto_increment

    • one
      Thank you, too, was inclined to this option! - quaresma89