Member Avatar for ben.matthews18

I have created a very basic database in 'MySQL Workbench' just as a test..

This is the SQL it produced -

CREATE  TABLE IF NOT EXISTS `CustomerOrder` (
  `OrderID` INT NOT NULL AUTO_INCREMENT ,
  `OrderDate` VARCHAR(45) NOT NULL ,
  `ProductName` VARCHAR(70) NOT NULL ,
  `ProductCost` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`OrderID`) ,
  INDEX `UserID` () ,
  CONSTRAINT `UserID`
    FOREIGN KEY ()
    REFERENCES `Users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM

When I copy it into phpmyadmin it does not let me turn it into a table.

This is the error message I get;

Error
SQL query:

CREATE TABLE IF NOT EXISTS `CustomerOrder` (

`OrderID` INT NOT NULL AUTO_INCREMENT ,
`OrderDate` VARCHAR( 45 ) NOT NULL ,
`ProductName` VARCHAR( 70 ) NOT NULL ,
`ProductCost` VARCHAR( 45 ) NOT NULL ,
PRIMARY KEY ( `OrderID` ) ,
INDEX `UserID` ( ) ,
CONSTRAINT `UserID` FOREIGN KEY ( ) REFERENCES `Users` ( ) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = MyISAMCREATE TABLE IF NOT EXISTS `CustomerOrder` (
`OrderID` INT NOT NULL AUTO_INCREMENT ,
`OrderDate` VARCHAR( 45 ) NOT NULL ,
`ProductName` VARCHAR( 70 ) NOT NULL ,
`ProductCost` VARCHAR( 45 ) NOT NULL ,
PRIMARY KEY ( `OrderID` ) ,
INDEX `UserID` ( ) ,
CONSTRAINT `UserID` FOREIGN KEY ( ) REFERENCES `Users` ( ) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = MYISAM

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') , CONSTRAINT `UserID` FOREIGN KEY () REFERENCES `Users` () ON DELETE' at line 1

Thanks in advance.

Edited by ben.matthews18 because: n/a
  • 5 Contributors
  • 6 Replies
  • 102 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by drjohn
Member Avatar for Ezzaral

Post the exact error message it gives you and perhaps someone can point out the problem.

Member Avatar for pritaeas

You must enter a column name between both () on the constraint line.

Member Avatar for ben.matthews18

So what would I add in between both ()?

I want to add a foreign key to this table.

The primary key in the other table is 'UserID' and the table is called 'Users'.

Edited by ben.matthews18 because: n/a
Member Avatar for pritaeas

You need to add the two columns you want to link. The first is the field in CustomerOrder, the second in Users.

Member Avatar for smantscheff

There are more than one problems with your syntax.
The INDEX() clause does not necessarily need a name, but at least one field. The name goes before the brackets, the indexed fields inside them.
The FOREIGN KEY clause needs the name of the foreign key column inside the brackets.
The REFERENCES clause needs the name of the referenced column inside the brackets.
Your table is lacking the UserID field on which you want to reference the user table.

And a tip: For basic development don't use phpMyAdmin but the MySQL command line. It's a lot faster, you can edit your queries and you will learn faster.

Member Avatar for drjohn

is UserID meant to be a field? because you're not creating that field in this table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.