Re: Generating values as part of a compound key

I hope it may be clearer if I outline what the tables are for:

I'm basically writing an application that stores information about
'behaviour incidents' at a school. The table in question is the 'incidents'
table which is used to record information about individual incidents of
negative behaviour (ok - let's call it being naughty).

The primary key for the 'incidents' table is made up of an 'incidentID' and
'pupilID'. The pupilID indicates the pupil(s) who were involved in the
incident and is itself a foreign key into a 'pupils' table.

This is to reflect the possibility that more than one pupil can be involved
in the same incident. In this case, there may be for example three rows with
the same 'incidentID' - each having a unique pupilID to reflect one incident
in which three different pupils were involved.

My question really revolves around how to generate the 'incidentID' that is
unique at the time of insertion but allows duplicates if more than one pupil
is involved.

Can I insert the first row and retrieve the identity with a scope_identity
and then just insert the rest of the rows with the same incidentID? Wouldn't
that return an error as the identity column wouldn't contain all unique
values.

I hope this is clearer.

Thanks for your time!

Mark.


"Robert Klemme" <bob.news@xxxxxxx> wrote in message
news:3nj2krF1nuhtU1@xxxxxxxxxxxxxxxxx
> 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

  • A2K3: design for pupil incidents database, help needed
    ... I need to knock something up quickly to allow a teacher to record incidents ... So I have tblPupils, ... events can involve a pupil and an event could relate to many pupils. ... Now I see how I can create a form based on tblEvents and have a subform ...
    (comp.databases.ms-access)
  • Re: Generating values as part of a compound key
    ... > I'm basically writing an application that stores information about ... > 'behaviour incidents' at a school. ... The pupilID indicates the pupilwho ... > to reflect one incident in which three different pupils were involved. ...
    (comp.databases.ms-sqlserver)
  • Re: there must be more important stuff
    ... How many of those incidents would have been legal 20 years ago? ... against teachers and other pupils being legal 20 years ago. ... IIRC beating children in state schools was banned in the mid-1980's so, ...
    (uk.legal)