SQL模式在PostgreSQL中无法运行吗?

huangapple go评论83阅读模式
英文:

SQL schema won't run with postgres?

问题

我想创建这个表结构

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");

像这样。

SQL模式在PostgreSQL中无法运行吗?

现在由于某种原因,当我尝试将其迁移到数据库时,出现以下错误。

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

我认为我没有正确设置主键或引用键。然而,有明确的外键,所以我不确定为什么无法正确连接这些表?

在golang中,json被解组成的结构如下,我正在考虑的表结构,以便将来可以添加不同的数据。

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"`
	}

有什么想法吗?

###更新

这是一个更新,当我尝试加载脚本时仍然出现错误: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)

);
英文:

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.

SQL模式在PostgreSQL中无法运行吗?

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 SQL模式在PostgreSQL中无法运行吗?

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)

);

答案1

得分: 4

外键引用父表中的一行。为了实现这一点,子表中的一列链接到父表中的一列,在父表中是唯一的。因此,只有一个父行被引用。

CreatedAt表中的行通过id列(主键)唯一标识。但是您正在尝试创建引用intersection_idtrafficData_id列的外键。这些列不是唯一的,因此不能用作引用。

顺便说一下,您的表看起来很奇怪。您的Intersection表有一个id列和一个intersection_id列。这是为什么?一个交叉口的ID和一个交叉口的交叉口ID之间有什么区别?

而且,桥接表CreatedAt应该有对其父表(显然是IntersectiontrafficData)的外键引用,而不是反过来。

这看起来对我来说都是错误的。

英文:

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.

huangapple
  • 本文由 发表于 2021年8月20日 14:10:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/68857669.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定