New answers tagged

0

Another approach is to use the IDENTITY function. More info here Here is a test I have created for your reference. Note as J.D. mentioned, you still have to use sp_rename to switch the table and if start_date has duplicate values in the table then there will be a non-deterministic assignment of IDENTITY values IF OBJECT_ID('dbo.testident', 'U') IS NOT NULL ...


1

Identity columns have some special rules around them so to accomplish your goal I believe you'd have to take a few roundabout steps: Create a new table that matches your existing table's schema. You can shortcut this step by using a SELECT INTO statement like so: SELECT TOP 0 * INTO YourTableNew FROM YourTable;. Note this won't keep the same constraints or ...


1

INDEX(meta_id) Is the minimum needed to satisfy MySQL. (As mentioned in other Answers.) However, that allows the user to explicitly add a duplicate value for meta_id. Presumably WP never does this 'dumb' thing. UNIQUE(meta_id) Slows down INSERTs slightly -- because the Insert won't return to the client until the uniqueness has been checked. With a ...


0

Thanks to the suggestion by Bill Karwin I found that these symptoms are, or at least were, caused by a InnoDB table bug (id number 199 in bugs.mysql.com) here that was first reported in 2003 and still going strong in 2017. It is due to a server restart following the deletion of some rows in a table with an autoinc field. I've deleted rows in the past and my ...


1

Short answer: in InnoDB, the auto-increment column must be the leftmost column of some index, either the primary key (clustered) index, or else some secondary index. It doesn't have to be a unique key, any B-tree index will do. The following is not valid... PRIMARY KEY (post_id, meta_key, meta_id) unless another index (aka key) is defined with meta_id as ...


2

You want to change the PRIMARY KEY from PRIMARY KEY (meta_id) to PRIMARY KEY (post_id, meta_key, meta_id) This is allowed in InnoDB engine but it requires that you have an index (not necessarily UNIQUE on (meta_id) or an index with more columns and meta_id as the first column. So, to answer the last question: Or can I omit that index to save a little ...


2

Turns out this was a dumb question. The DBMS won't allow an autoincrementing column without an index. It throws Error 1075 upon attempts to do this. Because of other filter patterns not mentioned in the question I'm going with these indexes. PRIMARY KEY (post_id, meta_key, meta_id) UNIQUE INDEX meta_id (meta_id) INDEX meta_key (meta_key, meta_value(32), ...


0

Depends what you mean by "keep working correctly long term"? A unique index will only do its job of preventing duplicate values from being entered, regardless if the field has an auto increment specification or not. But that doesn't stop someone from manually inserting an out of sequence value that is unique into the meta_id field.


0

InnoDB does not generate auto-inc values less than the highest value in the table. Even if you use ALTER TABLE to set the auto-increment to a lower value, it immediately resets to the max+1 value. But a client can run an INSERT statement and specify a value manually. This can be one of the unused values you identified. It doesn't have to be greater than the ...


Top 50 recent answers are included