CODEWITHSUNDEEP
mysqlphpmyadmin
user1788048
user1788048

Reputation: 265

mysql foreign key using phpmyadmin

I am not able to create Foreign key in phpmyadmin . I used the following queries to create 2 tables.The first table was created but when i use 2nd query i got the error as 

#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 'order ( Orderid INT NOT NULL, cost INT, CustId INT NOT NULL, PRIMARY KEY(Or' at line 1

This is my first table:

CREATE TABLE customer
(
  CustId INT NOT NULL,
  first_name VARCHAR(30),
  PRIMARY KEY (CustId)
);

and this is my 2nd table:

CREATE TABLE order
(
 Orderid INT NOT NULL,
 cost INT,
 CustId INT NOT NULL,
 PRIMARY KEY(Orderid),
 INDEX (CustId),
 FOREIGN KEY (CustId) REFERENCES customer (CustId)
);

So what i am doing wrong here? how do i create this tables and also implement the foreign key in phpmyadmin.

Upvotes: 0

Views: 4664

Answers (2)

Sathish D
Sathish D

Reputation: 5034

Also this would help you:

If you ask to me, backticks should always be used. But there are some reasons why a team may prefer not to use them.

Advantages:

  • Using them, there are no reserved words or forbidden chars.
  • In some cases, you get more descriptive error messages.
  • If you avoid bad practices you don't care, but... in real word, sometimes they are a decent way to avoid SQL injections.

Disadvantages:

  • They are not standard and usually not portable. However, as long as you don't use a backtick as part of an identifier (which is the worst practice I am able to imagine), you can port your query by automatically removing backticks.
  • If some of your query come from Access, they may quote table names with " (and maybe you can't remove all the " blindly). However, mixtures of backticks and double quotes are allowed.
  • Some stupid software or function filters your queries, and has problems with backticks. However, they are part of ASCII so this means that your software/function is very bad.

Refer: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Upvotes: 0

Barmar
Barmar

Reputation: 780673

order is a MySQL reserved word (it's used in ORDER BY clauses), you have to enclose it in backticks if you want to use it as the name of a table or column.

CREATE TABLE `order`
(
 Orderid INT NOT NULL,
 cost INT,
 CustId INT NOT NULL,
 PRIMARY KEY(Orderid),
 INDEX (CustId),
 FOREIGN KEY (CustId) REFERENCES customer (CustId)
);

You'll have to remember to put it in backticks whenever you reference it in a query. You'll save yourself lots of trouble if you simply choose a different name for the table.

Upvotes: 1

Related Questions