Search code examples
postgresqlreferencesequences

reference to a sequence column (postgresql)

I encountered a problem when creating a foreign key referencing to a sequence, see the code example below.
But on creating the tables i recieve the following error.
"Detail: Key columns "product" and "id" are of incompatible types: integer and ownseq"
I've already tried different datatypes for the product column (like smallint, bigint) but none of them is accepted. 

CREATE SEQUENCE ownseq INCREMENET BY 1 MINVALUE 100 MAXVALUE 99999;  
CREATE TABLE products (  
id ownseq PRIMARY KEY,  
...);

CREATE TABLE basket (
basket_id SERIAL PRIMARY KEY,
product INTEGER FOREIGN KEY REFERENCES products(id));
Solution 
  • CREATE SEQUENCE ownseq INCREMENT BY 1 MINVALUE 100 MAXVALUE 99999;  
CREATE TABLE products (  
    id integer PRIMARY KEY default nextval('ownseq'),
    ...
);
alter sequence ownseq owned by products.id;

    The key change is that id is defined as an integer, rather than as ownseq. This is what would happen if you used the SERIAL pseudo-type to create the sequence.