Hello guys and girls, here is a question I hope you can answer for me.
to avoid that id’s from diffrent tables would end up with the same number I would like a special start to all my diffrent auto incremented rows, as I understand it
ALTER TABLE sometable AUTO_INCREMENT = 1000
like this one can only push forward it to a certain number I would like a unique start to all my autoincemrented stuff like 6666-00 for comment tables and 7777-00 for user tables…
I know that there is no real problem with having the same id, but I do think that a id IS a Unique identifier for that row and when comparing tables to extract information having two exact same “unique identifier” seems wrong.
So there is no other way for autoincrement to count then 1, 2 , 3 and up or 1000,1001,1002 and up or 1212,1213,1214 and up etc ?
It’s not. Unless you have a very good reason why they should be unique across different tables?
So there is no other way for autoincrement to count then 1, 2 , 3 and up or 1000,1001,1002 and up or 1212,1213,1214 and up etc ?
Well, you could give all tables a composite primary key, with the first column a ‘table identifier’, and the second column the usual autoincremental id. But IMO that would be useless and needlessly complicated, because you’ll always know where you’re getting your data from anyway, so you might as well display the table name together with the id if that’s what you need.
there’s your problem – the comment table should not reference a pair of “either/or” tables with a single foreign key, you would instead use two foreign keys, one to one table, the other to the other table, and one of those foreign keys being NULL on any given row
you might find it easier to have only two tables – one for blogs/ideas, and one for comments
then your “unique across all tables” identifiers problem goes away
in fact, if it were me, i would use only one table for everything
