Win a copy of Svelte and Sapper in Action this week in the JavaScript 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
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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: 22485
151
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: 22485
151
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: 25814
69
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: 22485
151
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
 
Your mother is a hamster and your father smells of tiny ads!
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic