Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: auto_increment syntax

    The Ref Manual says ...
    To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

    mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
    Various other texts also allude to inserting an AUTO_INCREMENT command into a CREATE statement. But, all examples of AUTO_INCREMENT in the CREATE TABLE statement use a simple AUTO_INCREMENT.

    However, I get a syntax error when I try to put the statement in-line (and I am creating an index for the column).

    Does this mean I have to create the table with column using a simple AUTO_INCREMENT and then follow the create statement with an alter statement? Or, is there a solution within the CREATE statement?

    Many thanks, again

    Code:
    CREATE TABLE owners
    (
        ownerID    SMALLINT  UNSIGNED   NOT NULL  PRIMARY KEY   AUTO_INCREMENT = 1000
    ,   stateID	CHAR(2)      NOT NULL
    ,   INDEX ownerID_idx (ownerID)
    );
    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
    two comments --

    first, the AUTO_INCREMENT = 1000 goes on the table, not on the column

    second, a PRIMARY KEY is automatically indexed, so if you index it yourself you have just added a second, redundant index
    Code:
     
    CREATE TABLE owners
    ( ownerID SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT 
    , stateID CHAR(2)           NOT NULL
    ) AUTO_INCREMENT = 1000
    ;
    three additional comments --

    this table makes little sense, presumably you will define a number of additional columns for an owner

    since ownerID is a surrogate key, one (or more) of those additional columns will need a UNIQUE constraint

    there is typically no need to start an auto_increment at a number other than 1, since you won't let your users see those values anyway

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104

    Wink thanks and some comments

    Thanks, R.

    Quote Originally Posted by r937
    first, the AUTO_INCREMENT = 1000 goes on the table, not on the column
    OK. That makes more sense. That didn't get across (the widening chasm).

    Quote Originally Posted by r937
    second, a PRIMARY KEY is automatically indexed, so if you index it yourself you have just added a second, redundant index
    OK. I read and forgot that the P-Key is automatically indexed. MT!

    Thanks for the code correction.

    Quote Originally Posted by r937
    three additional comments --

    this table makes little sense, presumably you will define a number of additional columns for an owner
    Yes, I removed them for the example. They are all non-key columns with no meaning to the example.

    Quote Originally Posted by r937
    since ownerID is a surrogate key, one (or more) of those additional columns will need a UNIQUE constraint
    Somehow I got the idea lodged in my head that a primary key statement made the column automagically unique. I don't know where that notion came from. Somewhere deep in the recesses...
    Quote Originally Posted by r937
    there is typically no need to start an auto_increment at a number other than 1, since you won't let your users see those values anyway
    That is a great point. I think my need for these constraints must stem from my inner need to control the future.
    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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you have an ownerID column that is a surrogate key, you should also have a UNIQUE constraint on some other column(s), so that you will not be able to add the same owner more than once

    without it, you are inviting duplicates

    do a search in your favourite search engine for "removing duplicates" and you will see that there are literally millions of web pages discussing this

    why? because they forgot to declare a UNIQUE constraint on the natural key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    104

    that's interesting and raises interesting points

    Hmmm. Thanks for telling me that, R. Somehow that (and more) slipped through the net...

    Then, I might be best with a unique constraint on ownername and owner telephone together. And, if I'm going to that trouble (which I should), I should be sure that a user (me) doesn't input slightly different strings for the same person.... presumably by breaking down ownername into three columns (first, last, middle initial).

    BTW, If I am declaring three fields (all foreign keys) as a primary key, I also need to put unique constraints on them, correct? (I don't see why I wouldn't, but I wonder if I'm missing something.)

    Again, 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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    BTW, If I am declaring three fields (all foreign keys) as a primary key, I also need to put unique constraints on them, correct?
    example, plz
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    104

    example

    Code:
    CREATE TABLE harvestsystems
    (
        equipmenttypeID   SMALLINT     UNSIGNED    	NOT NULL
    ,   operatorID      	 SMALLINT      UNSIGNED      NOT NULL
    ,   standID      	 INTEGER	UNSIGNED      NOT NULL
    ,   trucktrailerID      SMALLINT	UNSIGNED     	NOT NULL
    ,   PRIMARY KEY (equipmenttypeID, operatorID, standID, trucktrailerID)
    , CONSTRAINT harvsystems_uk 
          UNIQUE  (equipmenttypeID, operatorID, standID, trucktrailerID)
    ,   INDEX equipmenttypeID_idx (equipmenttypeID)
    ,   CONSTRAINT   equipmenttypeID_fk 
              FOREIGN KEY (equipmenttypeID)   
                        REFERENCES equipmenttypes (equipmenttypeID)
                            ON DELETE RESTRICT
    	                ON UPDATE  CASCADE
    ,   INDEX operatorID_idx (operatorID)
    ,   CONSTRAINT operatorID_fk 
                  FOREIGN KEY  (operatorID) 
                        REFERENCES operators (stateID)	
                             ON DELETE RESTRICT
                             ON UPDATE  CASCADE
    ,   INDEX standID_idx (standID)
    ,   CONSTRAINT standID_fk 
                  FOREIGN KEY (standID) 
                       REFERENCES stands (stateID)
                            ON DELETE RESTRICT
    	            	ON UPDATE  CASCADE
    ,   INDEX trucktrailerID_idx (trucktrailerID)
    ,   CONSTRAINT  trucktrailerID_fk 
                 FOREIGN KEY  (trucktrailerID) 
                          REFERENCES trucktrailers (stateID)
                              ON DELETE RESTRICT
                              ON UPDATE  CASCADE
    ,   INDEX trucktrailerID_idx (trucktrailerID)	
    );
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your constraint harvsystems_uk is redundant, as there is already a unique index exactly like it, to implement uniqueness for the primary key

    your index equipmenttypeID_idx is redundant as the primary key can be used to locate row via equipmenttypeID, as it is the leftmost column in the index

    and you have trucktrailerID_idx twice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2009
    Posts
    104

    i get it

    SO, I see that I misread the below statement, and now your recent statement has a context.
    since ownerID is a surrogate key, one (or more) of those additional columns will need a UNIQUE constraint
    I'll repeat it to myself three times: the natural key needs a unique constraint.... and the primary key does not.
    your index equipmenttypeID_idx is redundant as the primary key can be used to locate row via equipmenttypeID, as it is the leftmost column in the index
    Ack. I thought that with MySQL one must first add an index key to a foreign key column before adding the constraint. Is this an exception or am I misunderstanding the rule?

    Thanks for the tip on the redundant line.
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    I'll repeat it to myself three times: the natural key needs a unique constraint.... and the primary key does not.
    not quite

    it's more like this: all tables need a unique constraint, especially tables with a surrogate key as the primary key, which need a unique constraint in addition to the surrogate key

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2009
    Posts
    104
    ok re. the surrogate key, natural key, and unique contraint. (EDIT This was definitely a case of my manifest density I see by reviewing this thread.)

    But, what is the rule in regard to adding or not adding an index statement before any foreign key constraint (in MySQL)? Is it that one should add the index statement before any foreign-key constraint unless the column will be the leftmost column in the index?


    EDIT: If I have it right above then I'm pretty much done with writing this behemoth. Thanks!
    Last edited by rbfree; 03-24-09 at 23:55.
    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.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the rules are:
    Foreign keys definitions are subject to the following conditions:
    • Both tables must be InnoDB type.
    • In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
    • In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
    straight outta da manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2009
    Posts
    104

    maybe I have the wrong table type

    I'm not seeing the InnoDB type option in the table properties/Engine dropdown. Rather, all I'm seeing is the MyISAM. Have I installed the wrong thing? (Ack. Here's where a better explanation in the refs would help a n00b.)
    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.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    ... the table properties/Engine dropdown
    mysql doesn't have dropdowns

    if you want to see the exact table specs, run this query --
    Code:
    SHOW CREATE TABLE tablename
    if it's myisam, and you want to change it to innodb, run this query --
    Code:
    ALTER TABLE tablename
    ENGINE = innodb
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2009
    Posts
    104
    Thanks for the code for changing the engine. In the meantime, I had already reconfigured the engine to InnoDB so I'm back to the question (like a stinky old dog with a dull teeth and a delicious, fresh soup bone). EDIT: My miscommunication on the dropdowns; I was in HeidiSQL.

    Please bear with the following.... I'm trying to get this reference manual but this passage is not really making sense.
    In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
    http://dev.mysql.com/doc/refman/5.1/...nstraints.html

    In the same order as what? (It doesn't make sense to me. Too many missing referents.) Are they talking about an index for a composite key (listing multiple columns in the index statement)? Are they talking about (I assume) an individual index? If so, doesn't each column with an f-key constraint require iits own index?

    Also, in one of my fave techbooks on sql, I've read that in mysql, one must add an index prior to adding a foreign key constraint. But, the docs say...
    Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.)
    Are both statements true? Is there an assumption or rule in here that is not explicit? (That's my guess.)

    (Sorry to keep bugging this forum on this question! But, I have 6 sql books -- two of them dedicated to mysql -- and not one of them shows an sql example of coupling a foreign key constraint statement with an index creation statement. )

    Same problem with this statement.

    InnoDB allows a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
    In the same order as what? And, how does one assure that the referenced columns are listed as the first columns ... given one knows what "the same order" means?

    OFF TOPIC Can someone lead me to a reference for interpreting the refs? For instance, in the examples, what does symbol mean? (Eg.... [CONSTRAINT [symbol ]] FOREIGN KEY

    OFF TOPIC from foreign key restraints and indexes, but as a n00b coming into this project with an obvious need for referential integrity (a modeling application) it amazes me that the standard mysql configuration runs on the MyISAM engine when this engine doesn't support foreign constraints. What on earth would someone do with a mysql database that isn't based on foreign key relations?
    Last edited by rbfree; 03-25-09 at 14:16.
    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.

Posting Permissions

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