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?
Php – MySQL auto-increment between tables
MySQLPHPsql
Related Topic
- Sql – Get list of all tables in Oracle
- Mysql – concatenate multiple MySQL rows into one field
- Mysql – Should I use the datetime or timestamp data type in MySQL
- Mysql – Retrieving the last record in each group – MySQL
- Mysql – How to reset AUTO_INCREMENT in MySQL
- Mysql – How to get the sizes of the tables of a MySQL database
- Mysql – How to import an SQL file using the command line in MySQL
Best Answer
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
andtopics
. 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 noid
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
andtopics
both contained the fieldstitle
andurl
: you could put these fields in thesuperentity
table because they are common attributes of its subtypes. Only put fields which are specific to the subtype tables IN the subtype tables.