keys (1 Viewer)

[tweakin]

anyone care to explain keys ...primary,secondary,composite,foreign.
Preferably not just a textbook meaning.

 

[iptteacherssuck]

no

 

[user]

primary key - unique identifier, cannot be null, cannot repeat. identifies the record

secondary key- is not unique, can be null (i think)

composite key- a unique identifier created by combing two (or more?) fields (eg last name and date of birth)

foreign- not sure: think it's a primary key in another table

eg

table 1
primarykey
customername
customer addy
order no

table2
orderno
order details
product

order no (i think in table 1) would be the foreign key

 

[hurrotisrobbo]

I would've thought that:

** Primary Key
++ Foreign Key
From the perspective of Table 1, Table 2's 'orderno' is the foreign key.

table 1
orderno **
customername
customer addy

table2
orderno ++
order details
product



As far as I know, at least.

 

[wogboy]

From what I know, I agree with hurrotisrobbo because the relationship is one to many (one customer to many products, not one product to many customers!!). Each customer can make only one order (at once), and this order includes at least one product (possibly many).

Therefore, in table one, orderno is a primary key since it must be unique to each customer (each customer may only have one order number). In the other table however, the same orderno can appear more than once (if the person has ordered more than one product).

This means that the orderno in the first table is a PRIMARY key, and the orderno in the second table is a FOREIGN key just like hurrotisrobbo said.

 

[user]

ok i know when i'm beaten



but can there be two primary keys in the same table?

cos, to quote meself

table 1
primarykey
customername
customer addy
order no

table2
orderno
order details
product


that would kinda be like a concataneted key then, except it's redundant


just a bad example perhaps? it's like a half normalised form. it's so wonderfully confusing, it's the kind of example they would give in the exam!

 

[wogboy]

You can put as many primary keys in a table as you want. You can even make all the fields primary keys if you wanted!! And you don't have to create a primary key if you don't want to (MSACCESS always nags you to make one though, because if you don't make one and you save it, it gives you warning message that recommends you to put a primary key in)

There's normally no need to put in more than one primary key. I can't think of an example where you would have to put in multiple primary keys in. Or wait a sec, maybe an example would be for an electronics shop having a table that holds information about different VCRs and one primary key could be the CAT# and another could be the SERIAL#. All a primary key does is that it ensures that multiple records don't hold the same value for that particular field.

(BTW CAT# = catalogue number, SERIAL# = serial number)

 

[freestyler__54]

so whats the go with a secondary key?

 

[wogboy]

A secondary key is just any old type of field, the values in it don't have to be unique like in the primary key (e.g. in the example above, fields such as customername, customer addy, product etc) are secondary keys. Nothing special about them.

 

[iptteacherssuck]

when u index a field other than primary keyed field then its secondary key...

 

[Nelly]

Just think of it as when you're signing into MSN as a new person

Single Key - Username. e.g. nn99. Unique

OR

Composite Key - (if you dont have a username) it might be just Address + DOB. Unique

THEN

Primary Key - DOB, Agree to terms and conditions etc. Needed

Secondary Key - Hobbies, Proffesion. Not needed

Foreign Key - Key from a different table.

This is the easiest way to learn it.

Good luck in 3 hrs!

 

[iptteacherssuck]

nah secondary keys are not necessaryly all other fields other than primary..they are only the ones that are indexed and u use it to do search on it