CODEWITHSUNDEEP
mysqlconstraintsnotnull
user3090164
user3090164

Reputation: 65

MySQL - Constraint NOT NULL Error 1064

i want to create a table and i get an error : My Errorcode

Why doesn't he like my constraint? is mysql so different to sql? because 2 years ago i created the same table with no problems in sql :(

here the whole code

CREATE table Mitarbeiter(
Mitarbeiternummer INT(5),
Personennummer INT(5) constraint not_null_mitarbpersnr NOT NULL,
Gehalt INT(10),
Chef_Mitarbeiternummer INT(5),
constraint mitarbeiter_pk primary key (Mitarbeiternummer),
constraint unique_mitpernr UNIQUE (Personennummer),
constraint marb_fk_persnr foreign key(Personennummer) references person(Personennummer) ON DELETE CASCADE,
constraint marb_fk_chef foreign key (Chef_Mitarbeiternummer) references mitarbeiter(Mitarbeiternummer));

Also i get the same error when i want to write "personennummer NUMBER(5)" but it is a personal identification number and so every personennummer has the same lenght 5 for instance the first has '00001' and so on

I hope you can answer my questions :)

Upvotes: 0

Views: 126

Answers (1)

spencer7593
spencer7593

Reputation: 108370

MySQL doesn't support syntax to give a name to a NOT NULL constraint.

This will throw an error:

  Personennummer INT(5) constraint not_null_mitarbpersnr NOT NULL
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

To declare a NOT NULL constraint on a column in MySQL, you have to omit the keyword CONSTRAINT and the name. This syntax is valid: 

  Personennummer INT(5) NOT NULL

Reference: https://dev.mysql.com/doc/refman/5.5/en/create-table.html

Note that the syntax following CONSTRAINT does not allow NOT NULL. The NOT NULL is included as part of the column definition.

MySQL differs from other databases, such as Oracle, which do allow you to give a name to a NOT NULL constraint. I Oracle, I think the NOT NULL constraint shows up as a constraint in the dictionary. So it's understandable that we'd want to give a specific name, rather than having a system generated SYS_nnnnnn name assigned. In MySQL, NOT NULL is handled as an attribute of the column, not as a separate constraint.

Upvotes: 2

Related Questions