Queries on compound pk

Question

when using compound pk, can insertion of values be the same ? I illustrate an example to you guys:

Creation of table:

Create table test
(
testno number(5) not null,
testpaper varchar(2) not null,
time date
CONSTRAINT Pa_Pks PRIMARY KEY (testno, testpaper) 
)

Then this is the values that i would like insert:

Testno          Testpaper         Time
12345           22                14-JUL-2011
12345           23                15-JUL-2011
12345           22                16-JUL-2011

As you can see that my primary keys have the same values during insertion. The reason of why i would like to do that is the same testno and testpaper can happen on different dates.

How can i do that if i would like to add in same values but label it as primary key?

This should be the standard and correct way to do it:

Create table TEST
(
 Testid number(1) not null,
 testno number(5) not null,
 testpaper varchar(2) not null,
 time date
 CONSTRAINT Pa_Pks PRIMARY KEY (Testid) 
)

Thanks for any clarification.

Answer 1

No you cant do that, Primary Key has to be Unique. However what you can do is that you can add Test Time as part of the Key. Or you can have another column name as Id, this will be an Auto Increment Key. So your Primary Key will be ID (Auto Increment).

In oracle if you want to have an AutoIncrement Key, then you will have to write sequence and trigger also. A sequence is simply a series of number. The purpose of trigger will be to increment the number whenever new row is inserted, and store it in the column id. , And you may also wanna increase the size of Number. Number(1) might not be good enough size for primary key.(If you want Oracle to have an auto increment Id) OR Simple you can add Time to your primary Key.

The standard depends on your requirement. Both approaches are followed.

Answer 2

If time is nullable then it cannot form part of a PRIMARY KEY but it can be part of e UNIQUE constraint. Note that a key is a key and designating a key as 'primary' is arbitrary and not compulsory.

Adding Testid as a surrogate key will only work when you have a natural key to begin with. Therefore:

EITHER natural key only:

Create table TEST
(
 testno number(5) not null,
 testpaper varchar(2) not null,
 time date, 
 CONSTRAINT Pa_Uqs UNIQUE (testno, testpaper, time) 
);

OR natural key plus surrogate key:

Create table TEST
(
 Testid number(1) not null,
 testno number(5) not null,
 testpaper varchar(2) not null,
 time date, 
 CONSTRAINT Pa_Pks PRIMARY KEY (Testid), 
 CONSTRAINT Pa_Uqs UNIQUE (testno, testpaper, time) 
);