Foreign Key, Big deal? (SQL DB)

  • Thread starter Thread starter Jax
  • Start date Start date
J

Jax

I'm making my first ever database for my program.
I understand the concept of one to many relationships but
fail to see the advantage of using a secondary key over a
primary one.
I have a lot of tables and link them together by one key
the CaseKey.
What is the disadvantages of this method? Why should I
include more detailed item keys (e.g an arraylist of
customer credit items would be stored in the
CustomerCreditItem table surely it's better for them to
share a universal primary key rather then following the
key from case to customer and then to creditItem, that
needing three keys).

Can anyone provide me with excellent advise? (i'll accept
mediocre if it's all you've got though, and happily :))

Many thanks

jax
 
I think you may be confused. A primary key is the thing that uniquely
identifies a record in a table. There is no "secondary key."

A foreign key is when you use that primary key in another table to reference
the other table. An example:

Table: Department
int DepartmentID <--------- Primary key
varchar(30) DepartmentName

Table: Employee
int EmployeeID <------------Primary key
int DepartmentID <------------Foreign key
varchar(30) EmployeeName

In this example, the DepartmentID is a primary key in the Department table,
but a foreign key in the Employee table. It is used to identify which
department an employee belongs to.

Pete
 
Oh, hang on, I get it, its for databases where all of the
data isn't necessarily relative.
As my database is, it would be better to use just the
primary. Is that it?
 
I completely did not understand your question.
I have no idea what is the CaseKey and the design you have for you tables.

But anyway.

Foreign Key exists only to enforce the Database integrity.
So you will not end up with OrderItems without Order itself.

PrinaryKey and ForeignKey are not interchangeable.
You can not use one over another.

PrimaryKey exists only to Identify the record in the table.


George.
 
Honestly, and I mean no offense by this, but I don't think you do get it. I
hope my other message clears it up a bit. Database design is not a simple
subject. There can be a great deal of complexity in it and it's very
important to do it right as doing it wrong can have a number of negative
consequences in the code. Everything from poor performance to loss of data
integrity.

Do a search on google for the following words: database normalization
tutorial

There are plenty of links that can help you understand database
normalization. Once you've read through some, I think it will start to make
more sense to you.

This one might be a good start:

http://www.vbmysql.com/articles/normalize.html

It's very simple and explains the first 3 normal forms. That's what you
really need to know.

Pete
 
If you have any tables that are related, then you MUST be using foreign
keys. What you said in your last message makes no sense to me. Saying the
tables are "completely relative" doesn't mean anything to me.

Pete
 
I understand, the reason I was asking is that I wasn't
using a foreign key (as all the tables are completely
relative) and I felt as though I was missing out on
functionality that would help.
Many thanks

jax
 
I understand, the reason I was asking is that I wasn't
using a foreign key (as all the tables are completely
relative) and I felt as though I was missing out on
functionality that would help.
Click to expand...

Just from what you wrote above I can tell you need to study relational
database principles. A table is not related unless keys are involed. You
cannot have a related table without a foreign key.
 
Hi Jax,

There's no room here for a full discussion of Third Normal Form, or
Relational design.

However, the point of a foreign key is to allow two different items to be
related, yet different.

One of the values of this is that you could have a one-to-many relationship
(100 people, all work for the same company. You'd need employee information
for each person, but the employer information should only be entered once).
If you have a Universal Key, then which key would you use for the company
record? Joe's key? Mary's key? You see the point?

And how would you create a many-to-many relationship if you had one record
in table A that refers to many records in table B, but you also have one
record in Table B referring to many records in Table A. Without unique
primary keys in each table, (and in this case, a third table), you can't do
it with relational databases.

Note: in relational theory, if you use a foreign key (which is the primary
key on another table) as the primary key, you have created a
one-to-zero-or-one relationship. In other words, one record in the main
table could link to one record in the dependent table or not. This is
sometimes used as a way to reduce the space consumed in a database for large
numbers of empty columns by placing the "sparse" columns in a dependent
table of this kind. It adds a good bit of useless complexity, and is often
not worth doing.

Normally, if you have the same key being used as the primary key in two
tables, you'd be better off moving all the fields to a single table.

I have no idea if this is helping you.

--- Nick
 
Well I have no problem with you guys telling me I dont understand, because I probably dont
My understanding of whole primary foreign thing is way off, (but thinking back to previous books i've read on the subject i'm finally starting to remember)
Actually thinking about it now (shoulda done that long ago) I do need other keys then just the one i've been talking abou
 
Back
Top