0

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 :)

user3090164
  • 55
  • 1
  • 6
  • 1
    show the actual sql causing this. – Marc B May 25 '15 at 17:05
  • why do you want to give that constraint a name? – Karoly Horvath May 25 '15 at 17:09
  • actual sql is now in the post i've learned this at the university - so i can change it afterwards i think? :/ – user3090164 May 25 '15 at 17:09
  • will you? ever? really? – Karoly Horvath May 25 '15 at 17:15
  • no but it's for a project at the university and i think my professor think i will - maybe :P but is that the problem? – user3090164 May 25 '15 at 17:18
  • There are two types of people. Programmers, and people who are afraid to experiment with things... – Karoly Horvath May 25 '15 at 17:21
  • 1
    @KarolyHorvath: Other databases, such as Oracle, do assign a name to `NOT NULL` constraints. If a name is not supplied, Oracle assigns a system generated name like `SYS_nnnnnn`. And some developers/DBAs have a preference for having a consistent name for the constraint, rather than a system generated name that is different in each environment (dev, test, qa, pre-prod, production.) – spencer7593 May 25 '15 at 17:34

1 Answers1

2

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140