Circular references - Mailing list pgsql-general
| From | Melvin Call |
|---|---|
| Subject | Circular references |
| Date | |
| Msg-id | CADGQN56ErReL0i8K8qPHzWWka1wZW7j0SGno+kqYThSszS8L3A@mail.gmail.com Whole thread Raw |
| Responses |
Re: Circular references
|
| List | pgsql-general |
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)
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)
pgsql-general by date: