SQL schema won't run with postgres?

Question

I want to create this table schema

CREATE TABLE "CreatedAt" 
(
    "id" SERIAL PRIMARY KEY,
    "created" timestamptz NOT NULL DEFAULT (now()),
    "trafficData_id" int,
    "intersection_id" int
);

CREATE TABLE "Intersection" 
(
    "id" SERIAL PRIMARY KEY,
    "intersection_id" int,
    "site_no" int,
    "latitude" float8,
    "longitude" float8,
    "site_name" VARCHAR,
    "relationships" VARCHAR,
    "type" VARCHAR,
    "op_status" VARCHAR,
    "road_classification" VARCHAR,
    "road_geometry" VARCHAR,
    "road_layout" VARCHAR,
    "suburb" VARCHAR,
    "switch_on_date" timestamp
);

CREATE TABLE "trafficData" 
(
    "index" SERIAL PRIMARY KEY,
    "trafficData_id" int,
    "properties_id" int,
    "latest_stats" int,
    "name" VARCHAR,
    "length" int,
    "min_number_of_lanes" int8,
    "minimum_tt" int,
    "is_freeway" bool,
    "direction" VARCHAR
);

CREATE TABLE "PropertyReference" 
(
    "index" SERIAL PRIMARY KEY,
    "type_id" int,
    "name" VARCHAR,
    "href" VARCHAR,
    "id" int
);

CREATE TABLE "StatisticsTraffic" 
(
    "index" SERIAL PRIMARY KEY,
    "type_id" int,
    "intervalStart" timestamp,
    "travelTime" int,
    "delay" int,
    "speed" int,
    "excessDelay" int,
    "congestion" int,
    "score" int,
    "flowRestrictionScore" int,
    "averageDensity" int,
    "density" int,
    "enoughData" bool,
    "ignored" bool,
    "closed" bool
);

ALTER TABLE "Intersection" 
    ADD FOREIGN KEY ("intersection_id") REFERENCES "CreatedAt" ("intersection_id");

ALTER TABLE "trafficData" 
    ADD FOREIGN KEY ("trafficData_id") REFERENCES "CreatedAt" ("trafficData_id");

ALTER TABLE "PropertyReference" 
    ADD FOREIGN KEY ("type_id") REFERENCES "trafficData" ("properties_id");

ALTER TABLE "StatisticsTraffic" 
    ADD FOREIGN KEY ("type_id") REFERENCES "trafficData" ("latest_stats");

like this.

Now for some reason I'm getting this error when i try to migrate this into the database.

error: migration failed: there is no unique constraint matching given keys for referenced table "CreatedAt" in line 0: CREATE TABLE "CreatedAt" (

I think I'm not setting the primary keys or reference keys properly. However, there are clear foreign keys, so I'm not sure why it wouldn't be able to connect these tables correctly together?

The struct in golang that the json is being unmarshaled into looks like this, the table schema i'm trying to think about so that i can add different data in the future.

type trafficData struct {
        Href         string      `json:"href,omitempty"`
        ID           int         `json:"id,omitempty"`
        Name         string      `json:"name,omitempty"`
        PublicName   interface{} `json:"public_name,omitempty"`
        Organization struct {
            Href string `json:"href,omitempty"`
            ID   int    `json:"id,omitempty"`
        } `json:"organization,omitempty"`
        Origin struct {
            Href string `json:"href,omitempty"`
            ID   int    `json:"id,omitempty"`
        } `json:"origin,omitempty"`
        Destination struct {
            Href string `json:"href,omitempty"`
            ID   int    `json:"id,omitempty"`
        } `json:"destination,omitempty"`
        Enabled          bool        `json:"enabled,omitempty"`
        Length           int         `json:"length,omitempty"`
        MinNumberOfLanes int         `json:"min_number_of_lanes,omitempty"`
        MinimumTt        int         `json:"minimum_tt,omitempty"`
        IsFreeway        bool        `json:"is_freeway,omitempty"`
        Direction        string      `json:"direction,omitempty"`
        Coordinates      interface{} `json:"coordinates,omitempty"`
        LatestStats      struct {
            IntervalStart        time.Time `json:"interval_start,omitempty"`
            TravelTime           int       `json:"travel_time,omitempty"`
            Delay                int       `json:"delay,omitempty"`
            Speed                int       `json:"speed,omitempty"`
            ExcessDelay          int       `json:"excess_delay,omitempty"`
            Congestion           int       `json:"congestion,omitempty"`
            Score                int       `json:"score,omitempty"`
            FlowRestrictionScore int       `json:"flow_restriction_score,omitempty"`
            AverageDensity       int       `json:"average_density,omitempty"`
            Density              int       `json:"density,omitempty"`
            EnoughData           bool      `json:"enough_data,omitempty"`
            Ignored              bool      `json:"ignored,omitempty"`
            Closed               bool      `json:"closed,omitempty"`
        } `json:"latest_stats"`

        Trend                 interface{}   `json:"trend,omitempty"`
        Incidents             []interface{} `json:"incidents,omitempty"`
        LinkParams            interface{}   `json:"link_params,omitempty"`
        ExcludedSourceIDTypes interface{}   `json:"excluded_source_id_types,omitempty"`
        EmulatedTravelTime    interface{}   `json:"emulated_travel_time,omitempty"`
        ClosedOrIgnored       interface{}   `json:"closed_or_ignored,omitempty"`
    }

Any ideas?

###Update

This is a update, still getting errors when trying to laod the script :D

CREATE TABLE createdAt (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  trafficData_id int,
  intersection_id int,
);

CREATE TABLE Intersection (
  id SERIAL PRIMARY KEY,
  intersection_id int,
  site_no int,
  latitude float8,
  longitude float8,
  site_name VARCHAR,
  relationships VARCHAR,
  type VARCHAR,
  op_status VARCHAR,
  road_classification VARCHAR,
  road_geometry VARCHAR,
  road_layout VARCHAR,
  suburb VARCHAR,
  switch_on_date timestamp,
  CONSTRAINT fk_createdAt
      FOREIGN KEY(id) 
      REFERENCES createdAt(intersection_id)
);

CREATE TABLE trafficData (
  id SERIAL PRIMARY KEY,
  trafficData_id int ,
  properties_id int,
  latest_stats int ,
  name VARCHAR,
  length int,
  min_number_of_lanes int8,
  minimum_tt int,
  is_freeway bool,
  direction VARCHAR,
   CONSTRAINT fk_tdata_createdat
      FOREIGN KEY(id) 
      REFERENCES createdAt(intersection_id)
);

CREATE TABLE PropertyReference (
  id SERIAL PRIMARY KEY,
  type_id int,
  name VARCHAR,
  href VARCHAR,
  id int
  CONSTRAINT fk_tdata
      FOREIGN KEY(id) 
      REFERENCES trafficData(properties_id)
);

CREATE TABLE StatisticsTraffic (
  id SERIAL PRIMARY KEY,
  type_id int,
  intervalStart timestamp,
  travelTime int,
  delay int,
  speed int,
  excessDelay int,
  congestion int,
  score int,
  flowRestrictionScore int,
  averageDensity int,
  density int,
  enoughData bool,
  ignored bool,
  closed bool,
  CONSTRAINT fk_stats
      FOREIGN KEY(id) 
      REFERENCES trafficData(latest_stats)

);

Answer 1

A foreign key references one row in the parent table. For this to happen, a column in the child table links to a column in the parent table that is unique in the parent table. Thus exactly one parent row gets referenced.

The rows in the CreatedAt table are uniquely identified by the column id (the primary key). But you are trying to create foreign keys referencing the columns intersection_id and trafficData_id. These are not unique and can hence not be used as a reference.

Your tables look weird, by the way. Your table Intersectionhas a column id and a column intersection_id. Why is that? What is the difference between an intersection's ID and an intersection's intersection ID?

And shouldn't the bridge table CreatedAt have foreign keys to its parent tables, which are Intersection and trafficData obviously, rather than vice versa?

This looks all very wrong to me.