Home » SQL & PL/SQL » SQL & PL/SQL » best practice for mapping table composite key or Pk with unique constraint ?
best practice for mapping table composite key or Pk with unique constraint ? [message #393612] Mon, 23 March 2009 10:51 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have Audit and Attachments table.

One Audit may have multiple attachemnts and attachemnts is common table serving many entities.

to do this I created a mapping table


Audit_Attachemnts with FK to Attachemnts and FK to Audit , now i need suggestions on should I create a PK for this mapping table and add unique contraint for Audit_id and Attachment_id or
create no PK and just a composite key, which is better and why ?

Re: best practice for mapping table composite key or Pk with unique constraint ? [message #393614 is a reply to message #393612] Mon, 23 March 2009 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>which is better and why ?
It depends.

What do your benchmark tests show?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: best practice for mapping table composite key or Pk with unique constraint ? [message #393617 is a reply to message #393612] Mon, 23 March 2009 10:57 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why not create a PK on Audit_id and Attachment_id?
Re: best practice for mapping table composite key or Pk with unique constraint ? [message #393618 is a reply to message #393614] Mon, 23 March 2009 11:02 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I understand the difference If I want to use the combination of Audit and Attachment in some other table of course I need PK and if I will not be using it any where them I can go with composite key , right now we don't have any need to refer audit_attachments in any other table but in future we never know , so my question is fetching data with sql queries will be faster with the additional PK or composite KEY?
Re: best practice for mapping table composite key or Pk with unique constraint ? [message #393619 is a reply to message #393612] Mon, 23 March 2009 11:09 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise that Primary keys can also be composite keys don't you?
Previous Topic: Analyzing Index Problem
Next Topic: convert long raw to long
Goto Forum:
  


Current Time: Sun Dec 11 00:42:37 CST 2016

Total time taken to generate the page: 0.03976 seconds