Re: composite primary key vs. primary key on additional single column
From: <johnbhurley_at_sbcglobal.net>
Date: Mon, 23 Mar 2009 10:32:11 -0700 (PDT)
Message-ID: <126a609a-73db-4cc6-9cac-1fdc2607ea51_at_v39g2000yqm.googlegroups.com>
On Mar 22, 5:05 pm, ciapecki <ciape..._at_gmail.com> wrote:
> we have 2 tables: individuals1 with primary key on ind1_id (numeric
> field) and individuals2 on ind2_id (varchar2 field)
>
> individuals1 has as well ind2_id populated for some rows (not all)
> individuals2 has as well ind1_id populated for some rows (not all)
> both tables have common attributes e.g. first_name, last_name
>
> As a requirement I need to have a new table (combined_inds) with all
> the data from individuals1 and from individuals2 which will look like
> this:
> [ind1_id, ind2_id, first_name, last_name]
> this structure will have many NULLs in both ind1_id and many NULLs in
> ind2_id, but at least one non-NULL value for these attributes for
> every row.
>
> now I would like to create a primary key on that new table.
>
> Is it better to have a composite primary key on both ind1_id, ind2_id?
> or maybe it's better to populate a new column that would hold
> concatenation of both of those columns, and then do the primary_key
> constraint on that?
>
> thanks,
> chris
Date: Mon, 23 Mar 2009 10:32:11 -0700 (PDT)
Message-ID: <126a609a-73db-4cc6-9cac-1fdc2607ea51_at_v39g2000yqm.googlegroups.com>
On Mar 22, 5:05 pm, ciapecki <ciape..._at_gmail.com> wrote:
> we have 2 tables: individuals1 with primary key on ind1_id (numeric
> field) and individuals2 on ind2_id (varchar2 field)
>
> individuals1 has as well ind2_id populated for some rows (not all)
> individuals2 has as well ind1_id populated for some rows (not all)
> both tables have common attributes e.g. first_name, last_name
>
> As a requirement I need to have a new table (combined_inds) with all
> the data from individuals1 and from individuals2 which will look like
> this:
> [ind1_id, ind2_id, first_name, last_name]
> this structure will have many NULLs in both ind1_id and many NULLs in
> ind2_id, but at least one non-NULL value for these attributes for
> every row.
>
> now I would like to create a primary key on that new table.
>
> Is it better to have a composite primary key on both ind1_id, ind2_id?
> or maybe it's better to populate a new column that would hold
> concatenation of both of those columns, and then do the primary_key
> constraint on that?
>
> thanks,
> chris
It sounds like you are being forced into a situation that is very bad from the get go. It would be much better to just have one of the original tables ... being forced to have both around and then merge them together ... ouch.
Primary keys in oracle cannot have any columns that may contain nulls ... it just does not work that way.
If you cannot get away from current design I would shot in the dark guess that an artificial primary key be used ( based on a sequence ) and you do additional indexing on the other "supposed to be key" columns maybe.
I don't see any gain in concatenating the two dissimilar fields ... what's the point? Received on Mon Mar 23 2009 - 12:32:11 CDT