Search code examples
sqlpostgresqlauto-increment

Two or more table with serial with uniq nums in every table

I've 2 table with serial field (in table "m" it's field "uniq" and in table "u" it's field "uniq"). But, if I insert data in (for example) u. Autoincrement function make +1 for next row in u (from 1 to 2), but if after this action I insert data in another table (for example) m autoincrement field write down not next value in column (1,2,3..), but 3, even if in field was 1.

It means, what autoincrement function incremented every single value in database in series, but not in the table.

sorry for such a poor description of the problem and bad english = )

Solution

  • Try something like this if you want having an id which is unique in all tables:

    CREATE SEQUENCE id_seq;  

CREATE TABLE table1(id INTEGER PRIMARY KEY DEFAULT NEXTVAL('id_seq'),Test1 varchar);
CREATE TABLE table2(id INTEGER PRIMARY KEY DEFAULT NEXTVAL('id_seq'),Test2 varchar);

    try something like this to create unique id for each table

    CREATE TABLE table3(id serial,Test3 varchar);
CREATE TABLE table4(id serial,Test4 varchar);

    SQL Fiddle