MsSQL allows me to create two 1st generation tables and either of two 2nd generation tables. However, it does not allow me to create both of the 2nd generation tables in the same database. In this case, it produces an errno 121. Can anyone help me figure this out?
This works:
Code:
CREATE TABLE counties
(
countyID CHAR(6) NOT NULL PRIMARY KEY
, countyname VARCHAR(30) NOT NULL
);
CREATE TABLE states
(
stateID CHAR(2) NOT NULL PRIMARY KEY
, statename VARCHAR(15) NOT NULL
, CONSTRAINT statename_uk
UNIQUE (statename)
);
CREATE TABLE counties_states
(
countyID CHAR(6) NOT NULL
, stateID CHAR(2) NOT NULL
, PRIMARY KEY (countyID, stateID)
, CONSTRAINT countyID_fk
FOREIGN KEY (countyID)
REFERENCES counties (countyID)
ON DELETE RESTRICT
ON UPDATE CASCADE
, CONSTRAINT stateID_fk
FOREIGN KEY (stateID)
REFERENCES states (stateID)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
And.... this works
Code:
CREATE TABLE counties
(
countyID CHAR(6) NOT NULL PRIMARY KEY
, countyname VARCHAR(30) NOT NULL
);
CREATE TABLE states
(
stateID CHAR(2) NOT NULL PRIMARY KEY
, statename VARCHAR(15) NOT NULL
, CONSTRAINT statename_uk
UNIQUE (statename)
);
CREATE TABLE biomassprocessors
(
biomassprocessorID SMALLINT(3) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, countyID CHAR(6) NOT NULL
, stateID CHAR(2) NOT NULL
, biomassprocessorname VARCHAR(30) NOT NULL
, biomassprocessordesc VARCHAR(50) NOT NULL
, biomassprocessoraddress VARCHAR(30) NOT NULL
, biomassprocessorcity VARCHAR(30) NOT NULL
, biomassprocessorzip INTEGER(5) UNSIGNED NOT NULL
, biomassprocessorareacode SMALLINT(3) UNSIGNED NOT NULL
, biomassprocessortelephone INTEGER(7) UNSIGNED NULL
, biomassprocessorfax INTEGER(7) UNSIGNED NULL
, CONSTRAINT biomassprocessorname_uk
UNIQUE (biomassprocessorname)
, CONSTRAINT countyID_fk
FOREIGN KEY (countyID)
REFERENCES counties(countyID)
ON DELETE RESTRICT
ON UPDATE CASCADE
, CONSTRAINT stateID_fk
FOREIGN KEY (stateID)
REFERENCES states (stateID)
ON DELETE RESTRICT
ON UPDATE CASCADE
) AUTO_INCREMENT = 100
;
But, attempting to create both tables, biomassprocessors and counties_states, always producers an errno 121. And, the error always falls on the biomassprocessors table.
This is true in any order I've attempted: -- Creating all tables simultaneously; -- creating the first generation (states and counties), first, then creating one or the other (biomassprocessors then counties_states, or counties_states then bioprocessors). -- creating the first generation then adding the 2nd gen tables one at a time.
Again, no matter what the sequence, the biomassprocessors table generates the error when I'm trying to add BOTH 2nd generation tables -- no matter which order each 2nd gen table is written into the statement. And, again, -- mysql DOES allow me to create biomassprocessors in the absence of counties_states (and vice versa).
PS and OT, no matter what I try, I can't seem to get the code to look ordered in posting. Any tips on that?
dBforums
>
errno 121 problems
03-31-09, 15:08
Linear Mode
