Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

MySQL: creating a table with Constraint that has multiple-choice input

 
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


i have the following code i try to execute



and i get the following error codes

   “A symbol name was expected! A reserved keyword can not be used as a column name without backquotes. (near "CHECK" at position 147)
   Unexpected beginning of statement. (near "Category" at position 155)
   Unrecognized statement type. (near "in" at position 164)

the error appears on the bold line of code " I am a beginner in MySQL so any help would be greatly appreciated

i also get the following error line "#1005 - Can't create table shopping_centres.shoppingcentreshop (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)"
I am a complete beginner so any help is much appreciated. i am using mariadb version 10.4.11
 
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You explicitly coded the invalid keyword CONSTRAINT in your DDL. MySQL doesn't need or want that word there. It already knows what a constraint is. So not:


But simply


It was thinking you were attempting to define a column named "CONSTRAINT". Badly.
 
Georgios Adamidis
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:You explicitly coded the invalid keyword CONSTRAINT in your DDL. MySQL doesn't need or want that word there. It already knows what a constraint is. So not:


But simply


It was thinking you were attempting to define a column named "CONSTRAINT". Badly.


i tried erasing the CONSTRAINT and i get an error code of "Unrecognized data type near CHECK"
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, I was looking at stale docs. One thing, though. I really don't recommend using upper-case letters in SQL column and table names. Different databases react differently and it can be pretty messy. MySQL is very strict  and lots of MySQL databases do have mixed-case names, but as I said, it's not something I recommend.

Anyway, let me see if I can take a stab:


I made the lines shorter so that they would fit in a non-scrolled browser window. But note that I put the check constraints with their column definitions and not as appended clauses. Makes the syntax simpler and reduces confusion about what's being done where.
 
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Georgios Adamidis wrote:i tried erasing the CONSTRAINT and i get an error code of "Unrecognized data type near CHECK"



Did you keep the comma at the end of line 5 in your original post? It looks to me like that comma makes MySQL think that there's another column definition coming on line 6.

I have to say: I decided to look in the MySQL documentation because I haven't used constraints like that before. And it's almost impossible to find actual examples there. That seems to be left to tutorial-writers on the web, who don't always do a good job of examples themselves. I read several such tutorials without once seeing "CONSTRAINT" in any of their examples.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's true. The examples are sparse and third-party examples are questionable. I ended up typing in the DDL myself as a confirmation.

The CHECK constraint can either be applied directly to the column definition (like I did) or as a clause after the column definitions. The keyword "CONSTRAINT" isn't needed on a column definition constraint, as it's known implicitly.
 
Georgios Adamidis
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Sorry, I was looking at stale docs. One thing, though. I really don't recommend using upper-case letters in SQL column and table names. Different databases react differently and it can be pretty messy. MySQL is very strict  and lots of MySQL databases do have mixed-case names, but as I said, it's not something I recommend.

Anyway, let me see if I can take a stab:


I made the lines shorter so that they would fit in a non-scrolled browser window. But note that I put the check constraints with their column definitions and not as appended clauses. Makes the syntax simpler and reduces confusion about what's being done where.



I get the following error code when i use the recommended code "MySQL said: Documentation

#1005 - Can't create table `shopping_centres`.`shoppingcentreshop` (errno: 150 "Foreign key constraint is incorrectly formed")"
 
Georgios Adamidis
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just managed to resolve the issue. The problem was the names of the tables in the foreign key assignments.. Thanks to everyone for your help
 
No. No. No. No. Changed my mind. Wanna come down. To see this tiny ad:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic