Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I just want to have a table in which a first column is a monotonically increasing unique integer number. For example, if I have a table with 5 rows the indexes of the rows should be 1,2,3,4,5. And if I add a new row it gets index 6.

I am alway confused with this things because there are many related options: "auto_increment", "primary key", "unique", "index". What should I use?

share|improve this question
    
stackoverflow maybe? –  Warner Mar 3 '10 at 14:15

1 Answer 1

up vote 2 down vote accepted
CREATE TABLE test (
     id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

auto_increment: This value will automatically increase.

primary key: This is the column which holds the first, and probably most logical, sort order. Almost always this means that it's unique.

index: This column is one which might be chosen as a sort order. Any column or combination of columns can be indexed, not just the primary key.

unique: This is a special index, where each value is going to appear only once.

share|improve this answer
    
Just to be sure that I got it right. Since "id" is declared as a "PRIMARY KEY", the "id" will be used as a the default column for ordering or rows when "select" is used. Is it right? But what if I do not use "PRIMARY KEY (id)"? Rows will be displayed in a random order? –  Roman Mar 3 '10 at 10:55
    
primary key isn't necessarily sort order. Unless you give a specific sort order, the database is free to return the rows in any order, usually in the one they are found on the disk, which in the case of an autoincrement id column usually is the same, but doesn't have to be. And this question probably belongs to stackoverflow and not serverfault. –  ptman Mar 3 '10 at 11:04
    
So, than I do not understand what "PRIMARY KEY (id)". You said "it holds the firs, and probably most logical, sort order". But what does it means? What if I remove "PRIMARY KEY" from your example? What will it affect? P.S. Can I move my question to stackoverflow? –  Roman Mar 3 '10 at 11:24
    
PRIMARY KEY is basically a comment. You could do the exact same thing by defining a unique index, but by putting primary key you're letting anyone who looks at the schema know your intent. –  gorilla Mar 3 '10 at 12:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.