Generating values as part of a compound key

[Mark]

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.

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.

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

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!

Mark.

[Robert Klemme]

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

[Mark]

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" <bo******@gmx.net> wrote in message
news:3n************@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

[Robert Klemme]

Mark wrote:

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.

You are right, this table layout would not work with identity. However, I
figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert

[Mark]

> You are right, this table layout would not work with identity. However, I

figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert

OF COURSE! I should have seen that it would be silly to duplicate all of the
incident information for every pupil involved in a given incident.

Thank you immensely for your help!

Mark.