|
|
Posted by Mark on 10/01/87 11:25
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@gmx.net> wrote in message
news:3nj2krF1nuhtU1@individual.net...
> 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
>
Navigation:
[Reply to this message]
|