0

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: n/a

5
Contributors
6
Replies
8
Views
6 Years
Discussion Span
Last Post by drjohn
0

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: n/a

0

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

0

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.

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.