MySQL auto-increment between tables

Question

In MySQL, is it possible to have a column in two different tables that auto-increment? Example: table1 has a column of 'secondaryid' and table2 also has a column of 'secondaryid'. Is it possible to have table1.secondaryid and table2.secondaryid hold the same information? Like table1.secondaryid could hold values 1, 2, 4, 6, 7, 8, etc and table2.secondaryid could hold values 3, 5, 9, 10? The reason for this is twofold: 1) the two tables will be referenced in a separate table of 'likes' (similar to users liking a page on facebook) and 2) the data in table2 is a subset of table1 using a primary key. So the information housed in table2 is dependent on table1 as they are the topics of different categories. (categories being table1 and topics being table2). Is it possible to do something described above or is there some other structural work around that im not aware of?

Answer 1

-- Set the auto-increment step to 2
SET @@auto_increment_increment = 2;

-- Create the table 'evens' with an initial auto-increment value of 1
CREATE TABLE evens (
    id INT AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 1;

-- Create the table 'odds' with an initial auto-increment value of 2
CREATE TABLE odds (
    id INT AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 2;

SET @@auto_increment_increment = 2;: This sets the increment value for auto-increment fields to 2. This means each new row will have an ID that is 2 greater than the previous one.

CREATE TABLE evens (...) AUTO_INCREMENT = 1;: Creates a table named evens with an auto-incrementing id column. The initial value of the auto-increment field is set to 1.

CREATE TABLE odds (...) AUTO_INCREMENT = 2;: Creates a table named odds with an auto-incrementing id column. The initial value of the auto-increment field is set to 2.

Answer 2

What is the most elegant way to have a sequence in mysql?

What about

drop table if exists sequence;
create table sequence (seq bigint unsigned);
insert into sequence values (0);

You can then do this: Fundamentally:

update sequence set seq = (@n := seq + 1)

mysql> update sequence set seq = (@n := seq + 1); select (seq) from sequence;
+------+
| seq  |
+------+
|    6 |
+------+

mysql> update sequence set seq = (@n := seq + 1); select (seq) from sequence;
// 7

mysql> update sequence set seq = (@n := seq + 1); select (seq) from sequence;
// 8

And then,

drop procedure if exists sequencer;
delimiter $$
create procedure sequencer()
BEGIN
update sequence set seq = (@n := seq + 1); select (seq) from sequence;
END$$
delimiter ;

You can then

mysql> call sequencer();
+------+
| seq  |
+------+
|   11 |
+------+

mysql> call sequencer();
+------+
| seq  |
+------+
|   12 |
+------+

As explained by user @LajosArpad, you can use as a function:

drop function if exists sequencer;
delimiter $$
create function sequencer() returns int
reads sql data
BEGIN
update sequence set seq = (@n := seq + 1);
return (select (seq) from sequence);
END$$
delimiter ;

(note - don't use deterministic in this function; after all the result is by very definition meant to be absolutely different every time. I used the harmless reads sql data.)

and then in your tables, mark the id as not null and the primary key, but do NOT use auto_incrmement ...

drop table if exists stuff;
CREATE TABLE `stuff ` (
`id` int NOT NULL ,
`city` char(32) not null unique,
`district` MEDIUMINT not null unique,
weather text not null default ('empty'),
PRIMARY KEY (`id`) );

and you can ...

replace into alarms (id, city, district, weather)
 values ( sequencer(), 'paris', 4, 'rainy and droll' );

Answer 3

create database MYSQL:

CREATE TABLE id_generator (
    secondaryid INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE table1 (
    secondaryid INT PRIMARY KEY,
    category_name VARCHAR(255)
);

CREATE TABLE table2 (
    secondaryid INT PRIMARY KEY,
    topic_name VARCHAR(255),
    FOREIGN KEY (secondaryid) REFERENCES table1(secondaryid)
);

In php :

In this approach, we don't need an id_generator. Instead, MySQL handles the auto-increment for both table1 and table2. Here’s an example of inserting data using PHP:

<?php

function insertIntoTable1($conn, $category_name) {
    $sql = "INSERT INTO table1 (category_name) VALUES ('$category_name')";
    if ($conn->query($sql) === TRUE) {
             $conn->insert_id ;
    } else {
         $conn->error;
    }
}

function insertIntoTable2($conn, $topic_name) {
    $sql = "INSERT INTO table2 (topic_name) VALUES ('$topic_name')";
    if ($conn->query($sql) === TRUE) {
         $conn->insert_id ;
    } else {
        $conn->error;
    }
}

?>

MySQL will automatically generate the secondaryid for both table1 and table2 based on the offset pattern defined (even for one table, odd for the other).

Answer 4

What you essentially need is a sequence, but, as far as I know, MySQL does ot support it currently. So you could create a table like this:

create table sequences(
    sequence_name varchar(64) primary key,
    current_value int not null default 1
);

and create stored function:

DELIMITER $$

DROP FUNCTION IF EXISTS `nextval`$$

CREATE FUNCTION `nextval`(sn VARCHAR(64)) RETURNS INT
BEGIN
     IF (1 = (SELECT COUNT(0) from sequences where sequence_name = sn)) THEN            
         update sequences set current_value = current_value + 1 where sequence_name = sn;
     ELSE
         insert into sequences(sequence_name, current_value) values(sn, 1);
     END IF;
     RETURN (select current_value from sequences where sequence_name = sn);

   END$$

 DELIMITER ;

Then, you can run something like

select nextval('foobar');

always getting the next value of the sequence:

And then you can do something like:

insert into tableA(id, <...>)
values (nextval('AB'), <...>)

and

insert into tableB(id, <...>)
values(nextval('AB'), <...>)

making the two tables share their sequence.

Answer 5

You could do it with triggers:

-- see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

CREATE TABLE table1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    secondardid INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

CREATE TABLE table2 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    secondardid INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

DROP TRIGGER IF EXISTS table1_before_insert;
DROP TRIGGER IF EXISTS table2_before_insert;

DELIMITER //

CREATE
TRIGGER table1_before_insert
    BEFORE INSERT ON 
        table1
    FOR EACH ROW
BEGIN
    UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    NEW.secondardid = LAST_INSERT_ID();
END;
//

CREATE
TRIGGER table2_before_insert
    BEFORE INSERT ON 
        table2
    FOR EACH ROW
BEGIN
    UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    NEW.secondardid = LAST_INSERT_ID();
END;
//

Answer 6

It seems you want to differentiate categories and topics in two separate tables, but have the ids of both of them be referenced in another table likes to facilitate users liking either a category or a topic.

What you can do is create a super-entity table with subtypes categories and topics. The auto-incremented key would be generated in the super-entity table and inserted into only one of the two subtype tables (based on whether it's a category or a topic).

The subtype tables reference this super-entity via the auto-incremented field in a 1:1 relationship.

This way, you can simply link the super-entity table to the likes table just based on one column (which can represent either a category or a topic), and no id in the subtype tables will be present in both.

Here is a simplified example of how you can model this out:

This model would allow you to maintain the relationship between categories and topics, but having both entities generalized in the superentity table.

Another advantage to this model is you can abstract out common fields in the subtype tables into the superentity table. Say for example that categories and topics both contained the fields title and url: you could put these fields in the superentity table because they are common attributes of its subtypes. Only put fields which are specific to the subtype tables IN the subtype tables.

Answer 7

It sounds like you want a MySQL equivalent of sequences, which can be found in DBMS's like PosgreSQL. There are a few known recipes for this, most of which involve creating table(s) that track the name of the sequence and an integer field that keeps the current value. This approach allows you to query the table that contains the sequence and use that on one or more tables, if necessary.

There's a post here that has an interesting approach on this problem. I have also seen this approach used in the DB PEAR module that's now obsolete.

Answer 8

If you just want the ID's in the two tables to be different you can initially set table2's AUTO_INCREMENT to some big number.

ALTER TABLE `table2` AUTO_INCREMENT=1000000000;

Answer 9

I'm confused by your question. If table 2 is a subset of table 3, why would you have it share the primary key values. Do you mean that the categories are split between table 2 and table 3?

If so, I would question the design choice of putting them into separate tables. It sounds like you have one of two different situations. The first is that you have a "category" entity that comes in two flavors. In this case, you should have a single category table, perhaps with a type column that specifies the type of category.

The second is that your users can "like" things that are different. In this case, the "user likes" table should have a separate foreign key for each object. You could pull off a trick using a composite foreign key, where you have the type of object and a regular numeric id afterwards. So, the like table would have "type" and "id". The person table would have a column filled with "PERSON" and another with the numeric id. And the join would say "on a.type = b.type and a.id = b.id". (Or the part on the "type" could be implicit, in the choice of the table).

Answer 10

You can't have an auto_increment value shared between tables, but you can make it appear that it is:

set @@auto_increment_increment=2; // change autoinrement to increase by 2

create table evens (
    id int auto_increment primary key
);
alter table evens auto_increment = 0;

create table odds (
    id int auto_increment primary key
);
alter table odds auto_increment = 1;

The downside to this is that you're changing a global setting, so ALL auto_inc fields will now be growing by 2 instead of 1.

Answer 11

You need to set the other table's increment value manually either by the client or inside mysql via an sql function:

ALTER TABLE users AUTO_INCREMENT = 3

So after inserting into table1 you get back the last auto increment then modify the other table's auto increment field by that.