PostgreSQL gives syntax error at "foreign" or near-CodePudding

These are tables before referances

CREATE TABLE olap.time (
        idtime SERIAL NOT NULL PRIMARY KEY,
        year integer,
        month integer,
        week integer,
        day integer
    );

 CREATE TABLE olap.addressees (
        idaddressee integer PRIMARY KEY NOT NULL,
        name varchar(40) NOT NULL,
        zip char(6) NOT NULL,
        address varchar(60) NOT NULL
);

CREATE TABLE olap.customers (
        idcustomer varchar(10) SERIAL PRIMARY KEY autoincrement,
        name varchar(40) NOT NULL,
        city varchar(40) NOT NULL,
        zip char(6) NOT NULL,
        address varchar(40) NOT NULL,
        email varchar(40),
        phone varchar(16) NOT NULL,
        regon char(9)
    );

After creating this tables I want to create this table

CREATE TABLE olap.fact(
        idtime integer NOT NULL,
        idaddressee integer NOT NULL,
        idcustomer varchar(10) NOT NULL,
        idfact integer NOT NULL,
        price numeric(7,2),
        PRIMARY KEY (idtime, idaddressee, idcustomer),
        FOREIGN KEY (idaddressee) REFERENCES olap.addressees(idaddressee),
        FOREIGN KEY (idcustomer REFERENCES olap.customers(idcustomer),
        FOREIGN KEY (idtime) REFERENCES time(idtime)
    ));

But I get error as "ERROR: syntax error at or near "REFERENCES" LINE 9: FOREIGN KEY (idcustomer REFERENCES olap.customers(idcustom..." Thanks in advance

CodePudding user response：

The idcustomer from olap.fact and idcustomer from olap.customers has different datatype SERIAL and Varchar(10), I have corrected the datatypes and validated the code below

CREATE TABLE olap.time (
        idtime  SERIAL NOT NULL PRIMARY KEY,
        year integer,
        month integer,
        week integer,
        day integer
    );

 CREATE TABLE olap.addressees (
        idaddressee integer PRIMARY KEY NOT NULL,
        name varchar(40) NOT NULL,
        zip char(6) NOT NULL,
        address varchar(60) NOT NULL
);

CREATE TABLE olap.customers (
        idcustomer  varchar(10) PRIMARY KEY ,
        name varchar(40) NOT NULL,
        city varchar(40) NOT NULL,
        zip char(6) NOT NULL,
        address varchar(40) NOT NULL,
        email varchar(40),
        phone varchar(16) NOT NULL,
        regon char(9)
    );
    
CREATE TABLE olap.fact(
        idtime integer NOT NULL,
        idaddressee integer NOT NULL,
        idcustomer varchar(10) NOT NULL,
        idfact integer NOT NULL,
        price numeric(7,2),
        PRIMARY KEY (idtime, idaddressee, idcustomer),
        FOREIGN KEY (idaddressee) REFERENCES olap.addressees(idaddressee),
        FOREIGN KEY (idcustomer) REFERENCES olap.customers(idcustomer),
        FOREIGN KEY (idtime) REFERENCES olap.time(idtime)
    );