Relationing tables in postresql-CodePudding

I want to make some relations in my postgresql database. My script looks like this:

create table anomaly_analysis(
   id bigserial not null,
   chart_id bigserial constraint charts_pk references charts id not null,
   analysis_date date not null,
   primary key (id)
);
create table anomaly_analysis_data_points(
   anomaly_analysis_id bigserial constraint anomaly_analysis_pk
    references anomaly_analysis id not null,
   data_point_id bigserial constraint charts_pk references charts id not null
);
alter table data_series
   add column isAnomaly boolean;

I want to add foreign key in first table, it will be referencing table charts, on field id. Also in the second table I want to add foreing key, that's going to reference on table I create above, field anomaly_analysis_id on id of anomaly_analysis, and chart_id on table charts and field id. I tried with querry (its an example for one line):

chart_id bigserial constraint charts_pk foreign key (charts_id )references charts not null

But it also didn't worked for me. I tried many similar combination, I was reading documentation and other sites, but it did not worked. What should i do to make my script correct?

EDIT I forgot to add logs. The database is being serviced with flyway. The exception that I receive is:

SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "id"
Position: 121
Location   : db/migration /V1.1.15__add_anomaly_analysis_tables.sql (/mypath/V1.1.15__add_anomaly_analysis_tables.sql)
Line       : 1
Statement  : create table anomaly_analysis(
   id bigserial not null,
   chart_id bigserial constraint charts_pk references charts id not null,
   analysis_date date not null,
   primary key (id)
)

CodePudding user response：

You should not use a serial as a FK column. It should be an integer, pointing to a serial.
The syntax for FKs needs parentheses: .. REFERENCING table_name (column, column,...)
the anomaly_analysis table appears to be a junction table, implementing an n-m relation. It certainly will need an index, or even two indices.
The (sub) syntax for CREATE TABLE ... is huge. see the fine manual

You probably want this:

CREATE TABLE charts( 
        id bigserial NOT NULL PRIMARY KEY
        );

CREATE TABLE anomaly_analysis(
   id bigserial NOT NULL PRIMARY KEY
   , chart_id bigint not null
         CONSTRAINT charts_fk REFERENCES charts (id)
   , analysis_date date NOT NULL
   );

CREATE TABLE anomaly_analysis_data_points(
   anomaly_analysis_id bigint NOT NULL
        CONSTRAINT anomaly_analysis_pk REFERENCES anomaly_analysis(id)
   , data_point_id bigint NOT NULL
        CONSTRAINT charts_fk REFERENCES charts(id)
-- , PRIMARY KEY (anomaly_analysis_id,data_point_id) -- ?? Will force an index to be created
-- , UNIQUE (data_point_id, anomaly_analysis_id) -- Will force an index to be created, too
   );

-- alter table data_series add column isAnomaly boolean; -- huh?