MySQL Forums
Forum List  »  InnoDB

Re: ERROR 1005 (HY000):can't create a table (error 150)
Posted by: Alex Leung
Date: November 18, 2008 07:31AM

thanks first~!
but how can i create the table but still keep this " FOREIGN KEY (ClientID) REFERENCES Client (ClientID) on delete set null on update cascade,"

this is the code i write,
but i still get the error 1005 cant create a table (error150)
how can i change it for my code ?



CREATE TABLE Client (
ClientID INT NOT NULL,
Address VARCHAR(30) NOT NULL,
City VARCHAR(20) NOT NULL,
ClientName VARCHAR(50) NOT NULL,
ContactFirstName VARCHAR(20),
ContactLastName VARCHAR(30) NOT NULL,
Email VARCHAR(70) NOT NULL,
State VARCHAR(2) NOT NULL,
ZIP CHAR(10),
USERNAME VARCHAR(10) NOT NULL,
PASSWORD VARCHAR(10) NOT NULL,

PRIMARY KEY (ClientID)
);

CREATE TABLE Equipment (
EquipNum INT NOT NULL,
ClientID INT NOT NULL,
EquipName VARCHAR(15) NOT NULL,
EquipType VARCHAR(10) NOT NULL,
InServiceDate DATE NOT NULL,
Quantity INT NOT NULL,
PRIMARY KEY (EquipNum),
FOREIGN KEY (ClientID) REFERENCES Client (ClientID) on delete set null on update cascade,

check(quantity <= 50)
);


CREATE TABLE Component (
Barcode CHAR(20) NOT NULL,
ComponentType VARCHAR(10) NOT NULL,
DatePurch DATE NOT NULL,
Description CHAR(30) NOT NULL,
ModelNum VARCHAR(10) NOT NULL,
Vendor VARCHAR(30) NOT NULL,
Stock INT NOt NULL,
PRIMARY KEY (Barcode)
);

CREATE TABLE Installed_Component (
InstalledID INT NOT NULL,
DateInstalled DATE NOT NULL,
DateRemoved DATE NOT NULL,
Barcode CHAR(20) NOT NULL,
EquipNum INT NOT NULL,
PRIMARY KEY (InstalledID),
FOREIGN KEY (EquipNum) REFERENCES Equipment (EquipNum) on delete set null on update cascade,
FOREIGN KEY (Barcode) REFERENCES Component (Barcode) on delete set null on update cascade
);

CREATE TABLE Software_Configuration_Record (
ConfigurationID INT NOT NULL,
Equipment VARCHAR(20) NOT NULL,
InfoName VARCHAR(20) NOT NULL,
InfoValues VARCHAR(50) NOT NULL,
EquipNum INT NOT NULL,
PRIMARY KEY (ConfigurationID),
FOREIGN KEY (EquipNum) REFERENCES Equipment (EquipNum) on delete set null on update cascade
);

CREATE TABLE Request (
ReqNum INT NOT NULL,
ReportDate DATE NOT NULL,
ReportedBy VARCHAR(50) NOT NULL,
ResolutionDate DATE NOT NULL,
ClientID INT NOT NULL,
PRIMARY KEY (ReqNum),
FOREIGN KEY (ClientID) REFERENCES Client (ClientID) on delete set null on update cascade
);


CREATE TABLE Work (
WorkNum INT NOT NULL,
FinishTime CHAR(4) NOT NULL,
StartTime CHAR(4) NOT NULL,
TechInitials CHAR(3) NOT NULL,
WorkDate DATE NOT NULL,
WorkDescriptuion VARCHAR(500) NOT NULL,
ReqNum INT NOT NULL,
PRIMARY KEY (WorkNum),
FOREIGN KEY (ReqNum) REFERENCES Request (ReqNum) on delete set null on update cascade
);



Edited 1 time(s). Last edit at 11/19/2008 04:17AM by Alex Leung.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: ERROR 1005 (HY000):can't create a table (error 150)
3000
November 18, 2008 07:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.