I was given a dump of an existing remote schema and database, and the
restore on my local system failed. Looking into it, I found a circular
parent-child/child-parent relationship, and I don't believe this existing
structure is viable. To summarize, the organization entity has an attribute
of creator, which is a foreign key to the user table, but the user has to
belong to an organization, which is a foreign key to the organization
table. Since neither are nullable, there is no way to create even an
initial record. My guess is one or both of the tables was first populated,
and then the FK constraint(s) created.
So, my question is just a request to confirm that I haven't lost my mind
and/or am missing something. Is there any way this could work? The relevant
table structures are listed below.
Thanks a million,
Melvin
\d organization
Table "project.organization"
Column | Type |
Modifiers
-----------------+--------------------------+------------------------------------------------------------------------
organization_id | bigint | not null default
nextval('organization_organization_id_seq'::regclass)
name | character varying(300) | not null
type_id | bigint | not null
description | text | not null default '-'::text
website | character varying(500) | default '-'::character varying
date_created | timestamp with time zone | not null default
('now'::text)::date
created_by | bigint | not null
date_updated | timestamp with time zone |
updated_by | bigint |
Indexes:
"p_key_org_id" PRIMARY KEY, btree (organization_id)
Foreign-key constraints:
"f_key_org_org_type_id" FOREIGN KEY (type_id) REFERENCES
organization_type(type_id)
"f_key_org_user_created_by" FOREIGN KEY (created_by) REFERENCES
"user"(user_id)
"f_key_org_user_updated_by" FOREIGN KEY (updated_by) REFERENCES
"user"(user_id)
Referenced by:
TABLE "program" CONSTRAINT "f_key_program_org_id" FOREIGN KEY
(organization_id) REFERENCES organization(organization_id)
TABLE ""user"" CONSTRAINT "f_key_user_org_id" FOREIGN KEY
(organization_id) REFERENCES organization(organization_id)
\d user
Table "project.user"
Column | Type |
Modifiers
-----------------+--------------------------+--------------------------------------------------------
username | character varying(100) | not null
password | character varying(100) | not null
date_created | timestamp with time zone | not null
date_updated | timestamp with time zone |
updated_by | bigint |
created_by | bigint | not null
person_id | bigint | not null
organization_id | bigint | not null
user_id | bigint | not null default
nextval('user_user_id_seq'::regclass)
user_role_id | bigint | not null
Indexes:
"p_key_user_id" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"f_key_user_org_id" FOREIGN KEY (organization_id) REFERENCES
organization(organization_id)
"f_key_user_person_id" FOREIGN KEY (person_id) REFERENCES
person(person_id)
"f_key_user_user_role_id" FOREIGN KEY (user_role_id) REFERENCES
user_role(user_role_id)
Referenced by:
TABLE "observation_parameter" CONSTRAINT
"f_key_observation_param_user_created_by" FOREIGN KEY (created_by)
REFERENCES "user"(user_id)
TABLE "observation_parameter" CONSTRAINT
"f_key_observation_param_user_updated_by" FOREIGN KEY (updated_by)
REFERENCES "user"(user_id)
TABLE "observation_tuple" CONSTRAINT
"f_key_observation_tuple_user_created_by" FOREIGN KEY (created_by)
REFERENCES "user"(user_id)
TABLE "observation_tuple" CONSTRAINT
"f_key_observation_tuple_user_updated_by" FOREIGN KEY (updated_by)
REFERENCES "user"(user_id)
TABLE "organization" CONSTRAINT "f_key_org_user_created_by" FOREIGN KEY
(created_by) REFERENCES "user"(user_id)
TABLE "organization" CONSTRAINT "f_key_org_user_updated_by" FOREIGN KEY
(updated_by) REFERENCES "user"(user_id)
TABLE "program_admin" CONSTRAINT "f_key_prog_admin_user_id" FOREIGN KEY
(user_id) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_created_by" FOREIGN KEY
(created_by) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_owner_id" FOREIGN KEY
(owner_id) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_updated_by" FOREIGN KEY
(updated_by) REFERENCES "user"(user_id)
