Search Postgresql Archives

Circular references

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux