CODEWITHSUNDEEP
sqloracle-database
user627093
user627093

Reputation: 11

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

SELECT * 
  FROM table 
 WHERE Col1 = 'SomeDate'

Upvotes: 1

Views: 213

Answers (3)

Ronnis
Ronnis

Reputation: 12843

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.

Upvotes: 1

KeithS
KeithS

Reputation: 71591

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.

Upvotes: 1

Jody
Jody

Reputation: 8301

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.

Upvotes: 5

Related Questions