英文:
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");
像这样。
现在由于某种原因,当我尝试将其迁移到数据库时,出现以下错误。
> 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.
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
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_id
和trafficData_id
列的外键。这些列不是唯一的,因此不能用作引用。
顺便说一下,您的表看起来很奇怪。您的Intersection
表有一个id
列和一个intersection_id
列。这是为什么?一个交叉口的ID和一个交叉口的交叉口ID之间有什么区别?
而且,桥接表CreatedAt
应该有对其父表(显然是Intersection
和trafficData
)的外键引用,而不是反过来。
这看起来对我来说都是错误的。
英文:
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 Intersection
has 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论