Composite PKs

[gemma-the-husky]

if you DO have the number and it is ALWAYS known, then there is no reason not to use it as a PK

personally, i would still be inclined to use an autonumber, and just keep the CHI as a keyed field in the master table, but if you can't , you can't.

It's more the general principle of having the PK as a bit of data that you will always know.

Your post seemed to indicate you had "issues" or "problems" with them - eg CHI's being entered incorrectly, and needing to be amended. You are better off not using a PK that may need to change.


OK - text or number

number (long data type) is limited to 2 billion approx - so if the CHI numbers exceed this, then you can't use a number - so this effectively limits you to a 9 digit number - also if the number ever starts with a zero, then it is text, not number anyway.

I think a serial number is generally TEXT, even if it is all numeric, if you see what I mean - I think that is your conclusion, anyway.

 

[rodmc]

Thanks Dave, Bob & Nigel, got it sorted now, there was actually nothing wrong with the tables, I was thinking about how the forms would work and think thats what got me in a twist.

 

[Galaxiom]

I tend to avoid autonumber and prefer to allocate numbered keys myself because they can be controlled. Where I work we have a several databases where records include numbers that make excellent natural keys.

When faced with a situation where a record is required but no number is yet allocated I use a negative number as an interim key. It is easily recognised as an out of scope value.