Why setting the key constraints on column removes "NOT NULL" from it (MySQL)?

PolGraphic

I have the code to create a table:

DROP TABLE IF EXISTS `database`.`creatures_kinds` ;

CREATE TABLE IF NOT EXISTS `database`.`creatures_kinds` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INT UNSIGNED NULL,
  `name_en` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`, `parent_id`),
  INDEX `parent_id` (`parent_id` ASC),
  CONSTRAINT `parent`
    FOREIGN KEY (`parent_id`)
    REFERENCES `database`.`creatures_kinds` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

What surprise me is that the newly created table looks like this: enter image description here

Why the parent_id is set to NOT NULL when I wrote NULL in the CREATE TABLE?

Does it has something to do with the PRIMARY KEY or FOREIGN KEY?

I guess that parent_id is an identifiable relation (it points to more general creature kind, e.g. from pigeon to bird, and it's NULL for top-level creature kinds).

user4003407

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). MySQL Manual

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL Check constraints: only one column is not null

From Dev

Why adding constraints removes the ability to resize NSWindow?

From Dev

Foreign Key constraints in MySQL

From Dev

Mysql: Multiple column primary key with null values

From Dev

Why is the `MUL` keyword showing up under the `Key` column for the `show columns from Employee` mysql command?

From Dev

Find MySql specific key constraints

From Dev

Prevent Entity Framework from setting foreign key to NULL

From Dev

Prevent Entity Framework from setting foreign key to NULL

From Dev

Setting new key bindings in ZSH removes default ones

From Dev

Autolayout Constraints from column to row

From Dev

delete Removes key from 2 objects

From Dev

Why would an access table primary key column contain a null?

From Dev

Resharper removes yield from foreach. Why?

From Dev

Resharper removes yield from foreach. Why?

From Dev

Why performInsert() removes field from array?

From Dev

useState() hook removes fields from the object when setting new value

From Dev

Backup MySQL schema with foreign key table constraints

From Dev

Cannot INSERT because of Foreign key constraints - mysql

From Dev

Problem with foreign key constraints and construction of Mysql DB

From Dev

What is wrong with my mysql foreign key constraints?

From Dev

MySQL DB is ignoring foreign key constraints

From Dev

MySQL SELECT column FROM table WHERE column IS NULL

From Dev

Why is it not possible anymore to resize NSWindow after setting up constraints?

From Dev

How to migrate IDs from JOIN table into foreign key column in MySQL

From Dev

Mysql column comparison 'different from 0' but not from NULL

From Dev

Setting a column as timestamp in MySql workbench?

From Dev

Setting index in MySql on a Varchar Column

From Dev

AngularJS ngRepeat filter with null values removes nulls permanently, why?

From Dev

Why is null<>null=null in mysql

Related Related

  1. 1

    MySQL Check constraints: only one column is not null

  2. 2

    Why adding constraints removes the ability to resize NSWindow?

  3. 3

    Foreign Key constraints in MySQL

  4. 4

    Mysql: Multiple column primary key with null values

  5. 5

    Why is the `MUL` keyword showing up under the `Key` column for the `show columns from Employee` mysql command?

  6. 6

    Find MySql specific key constraints

  7. 7

    Prevent Entity Framework from setting foreign key to NULL

  8. 8

    Prevent Entity Framework from setting foreign key to NULL

  9. 9

    Setting new key bindings in ZSH removes default ones

  10. 10

    Autolayout Constraints from column to row

  11. 11

    delete Removes key from 2 objects

  12. 12

    Why would an access table primary key column contain a null?

  13. 13

    Resharper removes yield from foreach. Why?

  14. 14

    Resharper removes yield from foreach. Why?

  15. 15

    Why performInsert() removes field from array?

  16. 16

    useState() hook removes fields from the object when setting new value

  17. 17

    Backup MySQL schema with foreign key table constraints

  18. 18

    Cannot INSERT because of Foreign key constraints - mysql

  19. 19

    Problem with foreign key constraints and construction of Mysql DB

  20. 20

    What is wrong with my mysql foreign key constraints?

  21. 21

    MySQL DB is ignoring foreign key constraints

  22. 22

    MySQL SELECT column FROM table WHERE column IS NULL

  23. 23

    Why is it not possible anymore to resize NSWindow after setting up constraints?

  24. 24

    How to migrate IDs from JOIN table into foreign key column in MySQL

  25. 25

    Mysql column comparison 'different from 0' but not from NULL

  26. 26

    Setting a column as timestamp in MySql workbench?

  27. 27

    Setting index in MySql on a Varchar Column

  28. 28

    AngularJS ngRepeat filter with null values removes nulls permanently, why?

  29. 29

    Why is null<>null=null in mysql

HotTag

Archive