basic syntax error 1064

[GWalk612]

Hello,

I am getting a syntax error in the following code, any suggestions what I need to modify?

Thanks

"Script line: 7 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 ')' at line 11"

CREATE TABLE STORE (
	StoreID		Integer		NOT NULL AUTO_INCREMENT,
	StoreName	Char (20)	NOT NULL,
	City		Char (12)	NULL,
	Country		Char (15)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (20)	NULL,
	Contact		Char (15)	NULL,
	CONSTRAINT	StorePK		PRIMARY KEY(StoreID, StoreName),
);

CREATE TABLE PURCHASE (
	PurchaseID	Integer		NOT NULL AUTO_INCREMENT,
	PurchaseDate		Char (25)	NULL,
	Description	Char (30)	NULL,
	Category	Char (15)	NULL,
	PriceUSD	Integer		NULL,
	StoreName	Char(20)	NOT NULL,
	CONSTRAINT	PurchasePK	PRIMARY KEY(PurchaseID),
	CONSTRAINT	StoreFK		FOREIGN KEY(StoreName)
				REFERENCES STORE(StoreName)
				ON UPDATE NO ACTION
				ON DELETE NO ACTION
);

[drjohn]

no comma required after StoreName) in the first table definition

[GWalk612]

no comma required after StoreName) in the first table definition

I changed it, still get the same error

[tesuji]

Hi,

Instead of:

CREATE TABLE STORE (
	StoreID		Integer		NOT NULL AUTO_INCREMENT,
	StoreName	Char (20)	NOT NULL,
	City		Char (12)	NULL,
	Country		Char (15)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (20)	NULL,
	Contact		Char (15)	NULL,
	CONSTRAINT	StorePK		PRIMARY KEY(StoreID, StoreName),
);

try this:

CREATE TABLE STORE (
	StoreID		Integer		NOT NULL AUTO_INCREMENT,
	StoreName	Char (20)	NOT NULL,
	City		Char (12)	NULL,
	Country		Char (15)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (20)	NULL,
	Contact		Char (15)	NULL,
	PRIMARY KEY(StoreID, StoreName)
);

What is the result? (isn't constraint restricted to foreign keys only?)

Btw, how come that you also put StoreName in primary key? Isn't StoreID already uniquely unique ?

-- tesu

[GWalk612]

The STORE table is created fine, the error comes in the PURCHASE table creaton.

[tesuji]

Ah, I see ! A not that negligible piece of information slided in.

** deleted **

Why not using the correct reference:

CONSTRAINT StoreFK FOREIGN KEY(StoreID) REFERENCES STORE(StoreID)

Also:

StoreName	CHAR(20)	NOT NULL,

should be:

StoreID		INTEGER		NOT NULL

Just out of curiosity: Why didn't you tell in your very first posting that the problem is table PURCHASE ?

-- tesu

[tesuji]

You should replace primary key of STORE by only storeID. Then the reference in PURCHASE must be FOREIGN KEY(StoreID) REFERENCES STORE(StoreID).

Thought there is no plausible reason that storeName is part of primary key in STORE, if you stick to this pk(StoreID,storeName), you must define the full reference in PURCHASE: FOREIGN KEY(StoreID, storeName) REFERENCES STORE(StoreID,storeName)

-- tesu

[GWalk612]

When I do the following why do the foreign key constraints fail to set up the table?

"Script line: 57 Can't create table 'morgan_importing.shipment_item' (errno: 121)"

CREATE TABLE STORE (
	StoreID		Integer		NOT NULL AUTO_INCREMENT,
	StoreName	Char (20)	NOT NULL,
	City		Char (12)	NULL,
	Country		Char (15)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (20)	NULL,
	Contact		Char (15)	NULL,
	CONSTRAINT	StorePK		PRIMARY KEY(StoreID),
	CONSTRAINT	StoreAK1	UNIQUE(StoreName)
);

CREATE TABLE PURCHASE (
	PurchaseID	Integer		NOT NULL AUTO_INCREMENT,
	PurchaseDate	Char (25)	NULL,
	Description	Char (30)	NULL,
	Category	Char (15)	NULL,
	PriceUSD	Integer		NULL,
	StoreID		Integer		NOT NULL,
	CONSTRAINT	PurchasePK	PRIMARY KEY(PurchaseID),
	CONSTRAINT	StoreFK		FOREIGN KEY(StoreID)
				REFERENCES STORE(StoreID)
				ON UPDATE NO ACTION
				ON DELETE NO ACTION
);

ALTER TABLE PURCHASE AUTO_INCREMENT = 500;

CREATE TABLE SHIPPER (
	ShipperID	Integer		NOT NULL AUTO_INCREMENT,
	ShipperName	Char (20)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (15)	NULL,
	Contact		Char (20)	NULL,
	CONSTRAINT	ShipperPK	PRIMARY KEY(ShipperID)
);

CREATE TABLE SHIPMENT (
	ShipmentID	Integer		NOT NULL AUTO_INCREMENT,
	ShipperInvoiceNumber	Integer	NULL,
	Origin		Char (25)	NULL,
	Destination	Char (25)	NULL,
	DepartureDate	Char (25)	NULL,
	ArrivalDate	Char (25)	NULL,
	ShipperID	Integer		NOT NULL,
	CONSTRAINT	ShipmentPK	PRIMARY KEY(ShipmentID),
	CONSTRAINT	ShipperFK	FOREIGN KEY(ShipperID)
				REFERENCES SHIPPER(ShipperID)
				ON UPDATE NO ACTION
				ON DELETE NO ACTION
);

ALTER TABLE SHIPMENT AUTO_INCREMENT = 100;

CREATE TABLE SHIPMENT_ITEM (
	ShipmentID	Integer		NOT NULL,
	PurchaseID	Integer		NOT NULL,
	InsuredValue	Integer		NULL DEFAULT '100',
	CONSTRAINT	Shipment_ItemPK	PRIMARY KEY(ShipmentID, PurchaseID),
	CONSTRAINT	Shipment_ItemFK	FOREIGN KEY(ShipmentID)
				REFERENCES SHIPMENT(ShipmentID)
				ON UPDATE NO ACTION
				ON DELETE NO CASCADE,
	CONSTRAINT	Shipment_ItemFK	FOREIGN KEY(PurchaseID)
				REFERENCES PURCHASE(PurchaseID)
				ON UPDATE NO ACTION
				ON DELETE CASCADE
);

[GWalk612]

I figured it out, I had duplicate constraint names.

CREATE TABLE SHIPMENT_ITEM (
	ShipmentID	Integer		NOT NULL,
	PurchaseID	Integer		NOT NULL,
	InsuredValue	Integer		NULL DEFAULT '100',
	CONSTRAINT	Shipment_ItemPK	PRIMARY KEY(ShipmentID, PurchaseID),
	CONSTRAINT	[B]Shipment_ItemFK[/B]	FOREIGN KEY(ShipmentID)
				REFERENCES SHIPMENT(ShipmentID)
				ON UPDATE NO ACTION
				ON DELETE NO CASCADE,
	CONSTRAINT	[B]Shipment_ItemFK[/B]	FOREIGN KEY(PurchaseID)
				REFERENCES PURCHASE(PurchaseID)
				ON UPDATE NO ACTION
				ON DELETE CASCADE
);

[tesuji]

Hi

I am glad to hearing of you again. It's nice to see the changes in your script, especiallly tables store and purchase now delight.

As for error around line 57, there could be 2 reasons

1. I don't know such delete policy: ON DELETE NO CASCADE, so obviously mysql don't

or

2. Sometimes error 121 occours if the name of a constraint is not unique on whole database. This is a namespace problem in sql databases. Therefore, duplicate constraint names are not allowed.

-- tesu