Re: Table Structure ordering of columns


"Rodney Wise" <NSpamPlease_rodney1@xxxxxxxxxxxxx> wrote in message
news:4450cbfc$1@xxxxxxxxxxxxxxxxxxxxxxxxxxx
Leslie,

So, you're using a compound Primary key?

The reason I'm asking is this... If you have an inventory system (as
example) that keeps the same items at different locations, I would want
the
primary key on the unique item identifier value... (that may be a SKU
number)... But that same SKU may also exist at another location (another
database)... To combine all location databases would then create a
problem...

So, I identify each location with a unique 4 character value... I then
combine this 4 character value with the SKU value to create my primary
key... it is not a "compound key".... but the value in my primary key is
actually the combined value of Location and SKU. This way, I avoid
compound primary Key fields

Now, when seperate databases are compined, there will never be a primary
key
conflict.

Do you do it this way, or actually use a compound primary key?


Hi Rodney,

You are correct, a compound key is not required (nor desirable).

In my case, the Primary key is (currently) A14. It is populated by getting
the DatabaseID and then concatentating the Next Number.

The DatabaseID is the Date and time (to the millisecond) in numeric format.
This numeric format has been converted to octal and then into ascii (a total
of 7 bytes) - The value is almost unique. My Next Number is the number in
ascii format.

As you have said, a compound key is really too troublesome in that it
restricts you to having to write your own export facility (which I have got
in the form of a sync engine). By using a single primary key you can use any
application to extract the records and still have uniqueness.

Leslie.


.

Relevant Pages

  • Re: Using seek to find if a record exists
    ... a compound primary key cannot have Nulls in any of the fields, ... Using a Birthdate field as part of a primary key would not be a poor ...
    (comp.databases.ms-access)
  • Re: not allow for duplicate for a combination of two fields
    ... > I did what you said but the clientid on its own is not unique - it is ... Karl did not suggest a compound Primary Key. ... with its unique property set to yes. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table Structure ordering of columns
    ... you're using a compound Primary key? ... But that same SKU may also exist at another location (another ... I identify each location with a unique 4 character value... ...
    (comp.databases.paradox)
  • Re: Cascade updates on primary keys..
    ... When the primary key is used as a foreign key in another table, ... >> int identity field and use that as the fk to the other table. ... >3) Primary keys should be immutable, and should never use cascade updates. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cascade updates on primary keys..
    ... When the primary key is used as a foreign key in another table, ... >> int identity field and use that as the fk to the other table. ... >3) Primary keys should be immutable, and should never use cascade updates. ...
    (microsoft.public.dotnet.languages.vb)