Menu
I've been using InnoDB for a project, and relying on auto_increment. This is not a problem for most of the tables, but for tables with deletion, this might be an issue:
AUTO_INCREMENT Handling in InnoDB
particularly this part:
AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE; InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table.
This is a problem because while it ensures that within the table, the key is unique, there are foreign keys to this table where those keys are no longer unique.
The mysql server does/should not restart often, but this is breaking. Are there any easy ways around this?
491
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
MySQL server already provides two auto increment variables: auto_increment_increment and auto_increment_offset, which can be used to generate different auto increment values on each member.
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
If you have a foreign key constraint, how can you delete a row from table A when table B references that row? That seems like an error to me.
Regardless, you can avoid the reuse of auto-increment values by resetting the offset when your application starts back up. Query for the maximum in all the tables that reference table A, then alter the table above that maximum, e.g. if the max is 989, use this:
alter table TableA auto_increment=999;
Also beware that different MySQL engines have different auto-increment behavior. This trick works for InnoDB.
199
So you have two tables:
TableA
A_ID [PK]
and
TableB
B_ID [PK]
A_ID [FK, TableA.A_ID]
And in TableB, the value of A_ID is not unique? Or is there a value in TableB.A_ID that is not in TableA.A_ID?
If you need the value of TableB.A_ID to be unique, then you need to add a UNIQUE constraint to that column.
Or am I still missing something?
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
