Oracle Primary Keys

Can you retriev a data record in oracle using only a portion of a composite primary key?

example PK = Col1 + Col2 + Col3

SE开发者_如何学运维LECT * 
  FROM table 
 WHERE Col1 = 'SomeDate'

You can pose that query, but it may not give you a single record unless you have a unique constraint on that column. Though if you did, I'm not sure why you'd have the composite primary key.

Sure, but because it's a composite primary key, the query is not guaranteed to return a unique or empty result. There is only guaranteed to be one unique combination of Col1+Col2+Col3; there could thus be many columns with the same Col1, unless as Jody says you have specified an additional unique constraint on the one column.

Yes, you can, and it's perfectly normal. You do it all the time with many-to-many tables.

Here is a table with a composite primary key.

create table student_grade(
  course_id  varchar2(6) not null
 ,student_id varchar2(12) not null
 ,grade number            not null
 ,primary key(student_id, course_id)
);

...with some test data:

COURSE STUDENT_ID        GRADE
------ ------------ ----------
DB101  Ronnis               70
DB102  Ronnis               70
DB103  Ronnis               70
DB101  user627093           70

Selecting on parts of the key would be completely normal.

select *
  from student_grade
 where course_id = 'DB101';

COURSE STUDENT_ID        GRADE
------ ------------ ----------
DB101  Ronnis               70
DB101  user627093           70

However, note that you may never ever rely on a query on subset of a key to return a single record. Sooner or later that query will return more than one row, and any logic that depends on it will break.