• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Some Relational Database Design Question

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1) If I have a primary key say an id and a surrogate key say a customercode
Do I have to make a composite key for both keys in order to uniquely identify each record
or I just need the id, not the customer code?

2) If I create a composite key for both id and customercode for customer, there
is a constraint violation if I create a foreign key of that key with
another table say the log, what is the logic behind it?
That is
Customer
======
CustomerID
CustomerCode

Log
=====
LogID
CustomerCode
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A primary key is unique and mandatory for every record.
A surrogate key is normally an artificially-generated value (e.g. from a sequence) that is guaranteed to be unique for every record.
A candidate key is a value that is unique and mandatory for every record.
A table could have several candidate keys, and you pick one to be your primary key.
Or you might decide to create a surrogate key to use as your primary key.
A composite key consists of 2 or more attributes that are not unique individually, but are unique as a combination.
Is your customer ID unique and always present? If so, it is a candidate key.
Is your customer code unique and always present? If so, it is a candidate key.
If your customer ID and code are not unique individually, but are unique in combination, then they form a composite candidate key.
If you have one or more candidate keys, pick one to use as your primary key.
If you cannot find any suitable candidate keys, create a surrogate key using "autoincrement" or a sequence, depending on your database.
Finally, a foreign key has to contain all the elements of the parent primary key, but nothing else.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Chris,
That's really helpful.
Jack
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One more thought:

Even if you have some candidate keys, it can still be a good idea to set up a surrogate key (via sequence/auto-increment).

One reason is that you cannot be sure that a piece of business data e.g. Customer Code, will always be unique or reliable e.g. the business rules might change, or the system might need to merge data from another database with overlapping keys. If you use a surrogate key as your PK instead, you can be sure your PK will not be affected by changes like these.

Also, if your table has a composite key e.g. of 3 columns, then using that as the PK means that any children also have to include the same 3 columns in their FK. It can be easier to have a single surrogate key column as your PK in the parent, so you only have to use a single column for your FK in the children.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic