Re: Generating values as part of a compound key

Mark wrote:
> BEGINNER QUESTION
>
> I have a table which has a compound primary key consisting of two
> columns.
>
> One of these columns is a foreign key which is generated in another
> table by an identity.

But with "generated" you don't mean it's also inserted into the table with
the compound key at the same time, do you?

> I want to be able to generate the other primary key column value
> automatically when an insert occurs but assume that I cannot use an
> identity because it would have to be unique for this table.

I don't see a problem here because identity *is* unique to your compound
key table.

> There will be potentially more than one user accessing this table so
> I want to avoid generating the key on the client side.

Yes, of course.

> How can I do this? Will it require some hardcore T-SQL?
>
> I hope this is clear (I suspect it isn't) I'd be happy to supply more
> info. I would be extremely grateful for any help!

Not fully to be honest. Maybe you post some DDL so we can see the table
layout. Also, it's not 100% clear to me when inserts in your main table
occur.

Kind regards

robert

.

Relevant Pages

  • Re: Cascade updates on primary keys..
    ... > When the primary key is used as a foreign key in another table, ... of whether a key is simple or compound. ... It's not "wasteful" beacuse what you loose in storing the longer foreign key ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Cascade updates on primary keys..
    ... > When the primary key is used as a foreign key in another table, ... of whether a key is simple or compound. ... It's not "wasteful" beacuse what you loose in storing the longer foreign key ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Trend towards artificial keys (GUIDs) sez my textbook...is AI next?
    ... Sometimes they conflict and one has to make tradeoffs. ... Sometimes no such ideal key exists. ... primary key that includes a PRIMARY key of the first table. ... table has a _compound_ primary key. ...
    (comp.databases.theory)
  • Re: Assign Foreign key
    ... The symbol field cannot be primary key in this table, ... I think the most likely candidate is a compound key ... A key should prevent duplicate data; ...
    (microsoft.public.access.tablesdbdesign)