开发者

pgAdmin - When trying to make a foreign key "referencing" gives no options

I have three tables: ModelingAgency.clients, ModelingAgency.models, ModelingAgency.Bookings. All three tables have a primary key column called id.

The table bookings has two columns that reference clients and models. In pgAdmin when I try to create a foreign key in bookings to either clients or models I get the following screens:

pgAdmin - When trying to make a foreign key "referencing" gives no options

pgAdmin - When trying to make a foreign key "referencing" gives no options

What am I overlooking here? I am new to PostgreSQL (This is my first test project with PostgreSQL -- I've always used MySQL and occasionally SQL Server) so it's probably something obvious (I just don't see it).

EDIT: Here is the DDL, as requested:

-- Table: "ModelingAgency.bookings"

-- DROP TABLE "ModelingAgency.bookings";

CREATE TABLE "ModelingAgency.bookings"
(
  id integer NOT NULL DEFAULT nextval('"ModelingAgency.Bookings_id_seq"'::regclass),
  "clientId" integer NOT NULL,
  "modelId" integer NOT NULL,
  "time" timestamp with time zone NOT NULL DEFAULT now(),
  "location" character varying(100) NOT NULL DEFAULT 'No Location Selected'::character varying,
  CONSTRAINT "bookingId" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "ModelingAgency.bookings" OWNER TO "MyBatisTutorial";


-- Table: "ModelingAgency.clients"

-- DROP TABLE "ModelingAgency.clients";

CREATE TABLE "ModelingA开发者_如何学编程gency.clients"
(
  id integer NOT NULL DEFAULT nextval('"ModelAgency.clients_id_seq"'::regclass),
  "name" character varying(45) NOT NULL,
  CONSTRAINT "clientId" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "ModelingAgency.clients" OWNER TO "MyBatisTutorial";


-- Table: "ModelingAgency.models"

-- DROP TABLE "ModelingAgency.models";

CREATE TABLE "ModelingAgency.models"
(
  id serial NOT NULL,
  "name" character varying(45) NOT NULL,
  CONSTRAINT "modelId" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "ModelingAgency.models" OWNER TO "MyBatisTutorial";


Looking into your posted DDL code I see that your table's names are written in wrong way (that causes your issue with pgAdmin):

"ModelingAgency.bookings"

It should be in format "schema"."tableName":

"ModelingAgency"."bookings"

After that Object browser looks like this (probably you need to create schema first using easily pgAdmin or with CREATE SCHEMA SQL statement):

pgAdmin - When trying to make a foreign key "referencing" gives no options

Here is working DDL code (I omitted some things like OIDS and OWNER TO, but that doesn't matter to your case, BTW OIDS are false on default):

DROP TABLE IF EXISTS  "ModelingAgency"."bookings";
CREATE TABLE "ModelingAgency"."bookings"
(
    id serial,
    "clientId" integer NOT NULL,
    "modelId" integer NOT NULL,
    "time" timestamp with time zone NOT NULL DEFAULT now(),
    "location" character varying(100) NOT NULL
        DEFAULT 'No Location Selected'::character varying,
    CONSTRAINT "bookingId" PRIMARY KEY (id)
);

DROP TABLE IF EXISTS "ModelingAgency"."clients";
CREATE TABLE "ModelingAgency"."clients"
(
    id serial,
    "name" character varying(45) NOT NULL,
    CONSTRAINT "clientId" PRIMARY KEY (id)
);

DROP TABLE IF EXISTS "ModelingAgency"."models";
CREATE TABLE "ModelingAgency"."models"
(
    id serial NOT NULL,
    "name" character varying(45) NOT NULL,
    CONSTRAINT "modelId" PRIMARY KEY (id)
)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜