Creating a table, SQL. Basic stuff

Question

So I'm having a small problem with creating a table. The problem is in my attempt to create a foreign key to another table. I'm currently using MYSQL2008 Management R2 express, so no designer. Heres my two tables

use teckDB;

CREATE TABLE inventory
(
  primId int NOT NULL PRIMARY KEY,
  prodName VarChar(255),
  quantity int,
  prodCost MONEY,
  prodDesc VARCHAR(255)   
);

CREATE TABLE orderTB
(
 primId INT NOT NULL PRIMARY KEY,
 orderId INT NOT NULL,
 created date,
 prodId INT,
);

Those two tables ran without a problem. When create the thrid one however causes this error message.

Msg 1769, Level 16, State 1, Line 3 Foreign key 'orderTB' references invalid column 'orderTB' in referencing table 'CustomerTB'. Msg 1750, Level 16, State 0, Line 3 Could not create constraint. See previous errors.

On the thrid table of....

CREATE TABLE CustomerTB
(
 primId INT NOT NULL  PRIMARY KEY,
 orderId INT, FOREIGN KEY (orderTB) REFERENCES orderTB(orderId),
 fName VARCHAR(50),
 lName VARCHAR(50),
 addLN1 VARCHAR(255),
 addLN2 VARCHAR(255),
 addCity VARCHAR(255),
 addPro VARCHAR(255),
 addPST VARCHAR(7)
 );

score 0 · Answer 1 · answered Jan 30 '13 at 04:33

0

try this

FOREIGN KEY (iparent_id) REFERENCES innodb_parent (iparent_id)

answered Jan 30 '13 at 04:33

Pawan Lakhara

1,146
4
16
28

So I've gone and just simply created the table and am trying to get the foreign key using a alter statement. use teckDB; Alter Table CustomerTB ADD Foreign Key (orderId) References orderTb (orderId); Still haveing the same error message however... – KaosAkroma Jan 30 '13 at 13:54

wcraft · Answer 2 · 2013-01-30T21:21:05.333

0

I think this should help to solve your query

http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/

edited Jan 30 '13 at 21:21

answered Jan 30 '13 at 04:34

wcraft

473
6
10

score 0 · Answer 3 · answered Jun 17 '14 at 12:05

You have an extra comma after "orderId INT", and you have got the foreign key syntax wrong.

This should work:

CREATE TABLE CustomerTB
(
  primId INT NOT NULL  PRIMARY KEY,
  orderId INT REFERENCES orderTB(orderId),
  fName VARCHAR(50),
  lName VARCHAR(50),
  addLN1 VARCHAR(255),
  addLN2 VARCHAR(255),
  addCity VARCHAR(255),
  addPro VARCHAR(255),
  addPST VARCHAR(7)
);

tested on SQLFiddle here

Creating a table, SQL. Basic stuff

3 Answers3