Can You Use Auto-Increment in MySQL with Out It Being The Primary Key

Can you use auto-increment in MySql with out it being the primary Key

A GUID value is intended to be unique across tables and even databases so, make the auto_increment column primary index and make a UNIQUE index for the GUID

MySQL InnoDB: autoincrement non-primary key

Yes you can. You just need to make that column be an index.

CREATE TABLE `test` (
  `testID` int(11) NOT NULL,
  `string` varchar(45) DEFAULT NULL,
  `testInc` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`testID`),
  KEY `testInc` (`testInc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert into test(
  testID,
 string
)
values (
1,
    'Hello'
);

insert into test( 
testID,
 string
)
values (
2,
    'world'
);

Will insert rows with auto-incrementing values for 'testInc'. However this is a really dumb thing to do.

You already said the right way to do it:

"Make the comment_id PK and enforce integrity through a unique index on book_id, timestamp, user_id."

That's exactly the way that you should be doing it. Not only does it provide you with a proper primary key key for the table which you will need for future queries, it also satisfies the principle of least astonishment.

Making a primary key not auto-increment in MySQL?

Yes.

You actually have to specify AUTO INCREMENT or data type SERIAL for it to automatically increment values. If your PRIMARY KEY that will be referenced by this FOREIGN KEY is data type SERIAL then you will need to make the FOREIGN KEY's data type BIGINT UNSIGNED. SERIAL is effectively an alias for setting the column to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Setting the the FOREIGN KEY to BIGINT UNSIGNED would give it the same integer range as a serial.

Remember, a PRIMARY KEY must be unique. So if this table is meant to have multiple records that will reference a single record in the other table, you may be better off making a PRIMARY KEY that is type SERIAL and then have another column that is your FOREIGN KEY that is type BIGINT UNSIGNED. This would allow you to have multiple records with the same value for the FOREIGN KEY column but you will still have a valid PRIMARY KEY

mysql auto increment primary key running out

It's fine. Depending on how many records you expect, you may want to make sure it's a bigint type, but int should be fine in most cases.

How to have an auto increment column in MySQL table without making it a primary key?

Consider:

CREATE TABLE insurance (
    _id INT(5) UNSIGNED AUTO_INCREMENT,
    patient_id INT(5) UNSIGNED NOT NULL,
    iname VARCHAR(40) DEFAULT NULL,
    from_date DATE DEFAULT NULL,
    to_date DATE DEFAULT NULL,
    PRIMARY KEY (patient_id),
    KEY(_id),
    CONSTRAINT fk_insurance FOREIGN KEY (patient_id)
    REFERENCES patient(_id)
);

That is, the auto-incremented column must at least be a key. You can still use another column as the primary key if you like.

Why do we use primary key auto increment, and not just auto_increment?

Because of constraints, because SQL is a declarative language, and because it is self-documenting.

AUTO_INCREMENT and PRIMARY KEY do two very different things.

AUTO_INCREMENT is not standard SQL, but most databases have something like it. In usually makes the default an incrementing integer. That's the mechanistic element of a primary key: an auto-incrementing integer is a decent unique id.

PRIMARY KEY does a number of things.

1. The columns are not null.
2. The columns are unique.
3. (Non-standard, but almost always) The columns are indexed.
4. The columns are declared the primary key.
5. No other columns can be declared the primary key.

Most are constraints on the value. It must exist (1), it must be unique (2), and it must be the only primary key (5). Constraints guarantee the data is as you expect. Without them you might accidentally insert multiple rows with the same primary key, or with no primary key. Or the table might have multiple "primary" keys, which do you use? With constraints you don't have to check the data every time you fetch it, you know it will within its declared constraints.

Indexing the primary key (3) is an optimization. You're probably going to be searching by primary key a lot. Indexes aren't part of the SQL standard. That might be surprising. They're a detail of implementing SQL. Indexes don't affect the result of the query, and the SQL standard avoids telling databases how they should do things. This is part of why SQL is so ubiquitous, it is declarative.

In a declarative language you don't say how to do it, you say what you want. A SQL query says what result you want and the database figures it out for you. You could implement most of the above as constraints and triggers, but if you did the database wouldn't understand why you're doing that and would not be able to use that to help you out. By stating to the database "this is the primary key" and it can handle that as it sees fit. The database adds the necessary constraints. The database adds its optimizations, which can be more than indexing. The database can use this information in queries to identify unique rows. You get the benefit of 50 years of database theory.

Finally, it lets people know the purpose of the column. Anyone can read the schema and know that column is the primary key. That anyone can be you six months from now.

---

Related Topics