auto_increment syntax

rbfree · #1 (**permalink**) 03-24-09, 14:08

The Ref Manual says ...

Quote:

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)
);

r937 · #2 (**permalink**) 03-24-09, 14:37

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

rbfree · #3 (**permalink**) 03-24-09, 15:22

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.

r937 · #4 (**permalink**) 03-24-09, 15:58

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

rbfree · #5 (**permalink**) 03-24-09, 16:41

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.

r937 · #6 (**permalink**) 03-24-09, 16:45

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

rbfree · #7 (**permalink**) 03-24-09, 17:29

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)	
);

r937 · #8 (**permalink**) 03-24-09, 20:01

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

rbfree · #9 (**permalink**) 03-24-09, 21:39

SO, I see that I misread the below statement, and now your recent statement has a context.

Quote:

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.

Quote:

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.

r937 · #10 (**permalink**) 03-24-09, 21:52

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

rbfree · #11 (**permalink**) 03-24-09, 22:34

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!

r937 · #12 (**permalink**) 03-25-09, 02:43

the rules are:

Quote:

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

rbfree · #13 (**permalink**) 03-25-09, 11:21

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.)

r937 · #14 (**permalink**) 03-25-09, 12:05

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

rbfree · #15 (**permalink**) 03-25-09, 13:01

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.

Quote:

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

Quote:

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.

Quote:

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech