Choosing primary key / Unique Index

[rAinDeEr]

I have a Parts Table with the following structure..
I need to decide which makes the primary key of the table.
I cant have any sequences nor can I add any new fields in this Table.

Table Structure ::

Create table Parts
(Parts_desig char(10),
Sequence_no integer,
Effective_Date Date,
Amount integer,
Expiry_Date Date,
Status char(1));
First I had

Parts_desig,Sequence_no,Effective_Date,Amount as the Primary Key

Part001, 0, jan-1-2006, 100, Mar-31-2006, Y
Part001, 0, jan-1-2006, 100, Dec-31-2006, N

can not be added Since it shows Primary Key Violation and a part with
same
Parts_desig,Sequence_no,Effective_Date,Amount can exist but with a
status N.

Then I had
Parts_desig,Sequence_no,Effective_Date,Amount,Expi ry_Date as the
Primary Key

Part001, 0, jan-1-2006, 100, Mar-31-2006, Y
Part001, 0, jan-1-2006, 100, Dec-31-2006, Y

is not valid data coz a part cannot exist with same effective date and
status Y.

Conditions
-----------

A Part may be Valid in an Year for different amounts with diferent
status.
E.g.

Part001 with sequence 0 may be valid from Jan 1 2006 to Dec 31 2006
for year 2006 with status N.
Part001 with sequence 0 may be valid from Jan 1 2007 to Mar 31 2006
for year 2007 with status Y.
Part001 with sequence 0 may be valid from Apr 1 2007 to Dec 31 2006
for year 2007 with status Y.

We are validating from the GUI, but I need to check it from back end
also to make sure that only valid data is entered.

Can I have a set of primary key which is different from the unique
index ??

regards.
RainDeEr

[Knut Stolze]

rAinDeEr wrote:

I have a Parts Table with the following structure..
I need to decide which makes the primary key of the table.
I cant have any sequences nor can I add any new fields in this Table.

Table Structure ::

Create table Parts
(Parts_desig char(10),
Sequence_no integer,
Effective_Date Date,
Amount integer,
Expiry_Date Date,
Status char(1));
First I had

Parts_desig,Sequence_no,Effective_Date,Amount as the Primary Key

Part001, 0, jan-1-2006, 100, Mar-31-2006, Y
Part001, 0, jan-1-2006, 100, Dec-31-2006, N

can not be added Since it shows Primary Key Violation and a part with
same
Parts_desig,Sequence_no,Effective_Date,Amount can exist but with a
status N.

Then I had
Parts_desig,Sequence_no,Effective_Date,Amount,Expi ry_Date as the
Primary Key

Part001, 0, jan-1-2006, 100, Mar-31-2006, Y
Part001, 0, jan-1-2006, 100, Dec-31-2006, Y

is not valid data coz a part cannot exist with same effective date and
status Y.

Conditions
-----------

A Part may be Valid in an Year for different amounts with diferent
status.
E.g.

Part001 with sequence 0 may be valid from Jan 1 2006 to Dec 31 2006
for year 2006 with status N.
Part001 with sequence 0 may be valid from Jan 1 2007 to Mar 31 2006
for year 2007 with status Y.
Part001 with sequence 0 may be valid from Apr 1 2007 to Dec 31 2006
for year 2007 with status Y.

We are validating from the GUI, but I need to check it from back end
also to make sure that only valid data is entered.

Then I'd think you will have to pick all columns to be part of the primary
key / unique constraint.

Can I have a set of primary key which is different from the unique
index ??

When designing tables at the conceptual level, completely ignore indexes.
An index belongs to the internal level of database design.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[rAinDeEr]

Hi,

I tried making all the keys Primary but the following is an invalid
data set coz
Part001 is having the same effective_date in an year, with status Y
where as the inserts into DB shouldnt allow that.

Part001 0 2006-01-01 100 2006-02-02 Y
Part001 0 2006-01-01 100 2006-06-06 Y
Part001 0 2006-01-01 100 2006-12-31 N
Part001 0 2006-01-01 100 2006-12-31 Y

Thanks.....

[Mark A]

"rAinDeEr" <ta**********@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

Can I have a set of primary key which is different from the unique
index ??

regards.
RainDeEr

The Primary Key as a whole must be unique.

Since you have effective_date and expiray_date as columns you should include
the effective_date in the PK, and create insert and update triggers to make
sure there are no overlapping date ranges for the same part (taking into
account all the other columns in the PK).

[Bill Medland]

rAinDeEr wrote:

Hi,

I tried making all the keys Primary but the following is an invalid
data set coz
Part001 is having the same effective_date in an year, with status Y
where as the inserts into DB shouldnt allow that.

Part001 0 2006-01-01 100 2006-02-02 Y
Part001 0 2006-01-01 100 2006-06-06 Y
Part001 0 2006-01-01 100 2006-12-31 N
Part001 0 2006-01-01 100 2006-12-31 Y

Thanks.....

Sorry but you are just going to have to have the primary key being all
fields and then use different methods (e.g. triggers or logic in your GUI)
to add the remaining date constraint. The problem is that the dates are
atomic objects but you have a further restriction that is based upon
structure within the dates (the year).

Are you sure you cannot modify the structure of the table?

--
Bill Medland

[rAinDeEr]

Thanks Mark, Knut, Bill for the response....
Solved the issue....with Triggers

Thanks.....

[rAinDeEr]

It seems I have to aviod triggers..
can i have a check constraint instead ??

Any suggestions.

Thanks

[Knut Stolze]

rAinDeEr wrote:

It seems I have to aviod triggers..
can i have a check constraint instead ??

No, unfortunately not. A check constraint is actually a row constraint.
Thus, you only have the values of the current row to evaluate if the
constraint is satisfied or violated. You can't query other (or the same)
tables. But since you want to prevent overlapping intervals, you have to
check against other rows in the table.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany