sql server (alternate): [quoted text, click to view]

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

[quoted text, click to view]

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

[quoted text, click to view]

Yes, of course.

[quoted text, click to view]

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

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.


[quoted text, click to view]

[quoted text, click to view]

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

[quoted text, click to view]

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.

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.