Simple Foreign key / Create View question

Question

What am I doing wrong? I need to create the foreign key but the code begining at CONSTRAINT is wrong somehow, also I need help with my view I think im doing the subquery wrong can you help me?

CREATE TABLE dealer(
dealershipId CHAR(10),
phone CHAR(10),
dealershipName CHAR(10),
webAddress char (10),
street CHAR (10),
city char (10),
zip char (5),
mCapacity INT,
managerFName CHAR (10),
managerLName CHAR (10),
PRIMARY KEY (dealershipId));

CREATE TABLE automobiles(
autoId CHAR (4),
vehiclecode VARCHAR (15),
manufacturer VARCHAR(15),
model VARCHAR (20),
style VARCHAR (5),
color VARCHAR (10),
ownership VARCHAR (8),
PRIMARY KEY (autoId)
CONSTRAINT fkHasRep FOREIGN KEY (dealershipId)
REFERENCE dealer(dealershipId) ON DELETE RESTRICT
);

--7.
CREATE VIEW division AS
SELECT dealershipName, webAddress, phone
FROM dealer
WHERE  dealershipId IN
( SELECT  manufacturer, model, style, ownership
FROM automobiles);

--8.
SELECT phone, manufacturer, model
FROM division;

Answer 1

My guess is that you don't have a dealershipID on your automobiles table.

And, your view isn't going to work since you can only include one field in your nested select query. You could probably rewrite that to use an INNER or OUTER JOIN as well.

Answer 2

Default Myisam engine doesn't support foreign keys. Specify Innodb engine in CREATE TABLE statements.

CREATE TABLE dealer(
dealershipId CHAR(10),
phone CHAR(10),
dealershipName CHAR(10),
webAddress char (10),
street CHAR (10),
city char (10),
zip char (5),
mCapacity INT,
managerFName CHAR (10),
managerLName CHAR (10),
PRIMARY KEY (dealershipId)) ENGINE = Innodb;

Answer 3

You're missing the comma between the Primary Ked & Foreign Key:

CREATE TABLE automobiles(
autoId CHAR (4),
vehiclecode VARCHAR (15),
manufacturer VARCHAR(15),
model VARCHAR (20),
style VARCHAR (5),
color VARCHAR (10),
ownership VARCHAR (8),
PRIMARY KEY (autoId),
CONSTRAINT fkHasRep FOREIGN KEY (dealershipId)
REFERENCE dealer(dealershipId) ON DELETE RESTRICT
);

MySQL views don't allow subqueries in Views (error #1349) - the restrictions are documented here. So convert it to use a JOIN instead:

CREATE VIEW division AS
   SELECT d.dealershipName, d.webAddress, d.phone, a.*
     FROM DEALER d
     JOIN AUTOMOBILES a ON d.dealershipid IN (a.manufacturer, 
                                              a.model, 
                                              a.style, 
                                              a.ownership);

Answer 4

You need a comma after "PRIMARY KEY (autoId)", and it is REFERENCE*S*, not REFERENCE.

Here is an example from the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/clauses002.htm#i1036780

Regards, Rob.