Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: errno 121 problems

    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?
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    Can anyone help me figure this out?
    FK constraint names must be unique across the database

    you have two instances of two FKs trying to use the same name -- countyID_fk and stateID_fk

    Quote Originally Posted by rbfree
    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?
    ordered??

    p.s. you should not encode the name of the table inside its column names

    change this --

    biomassprocessorname
    biomassprocessordesc
    biomassprocessoraddress
    biomassprocessorcity
    biomassprocessorzip
    biomassprocessorareacode
    biomassprocessortelephone
    biomassprocessorfax

    to this --

    name
    descr -- note DESC is a reserved word
    address
    city
    zip
    areacode
    telephone
    fax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104

    thanks r

    R, thanks for the tips. I had no clue on the foreign key constraint name. Ack.

    The tip on the names will help shorten these names (though I can see lots of editing ahead). Thanks. BTW, what's your reasoning?

    All this is very helpful. Thanks!!
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    Noun_Guelphdad verb_works adjective_hard

    Guelphdad works hard.

    I prefer the latter, don't you? Think of your tables and column names in the same way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •