Generating values as part of a compound key

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.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|||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|||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|||> 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.

Generating Foreign Key Script

We sometimes get very large databases that we want to cut down to use for
testing.

The information is all related to a central accounts table.

The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.

After this I will restore the constraints back to their original state.

I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.

For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.

My idea was to go through the database saying:

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade

delete various accounts and related data

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)

Can anyone assist?

Thanks[posted and mailed, please reply in news]

Jason Madison (jason.madison@.btinternet.com) writes:
> We sometimes get very large databases that we want to cut down to use for
> testing.
> The information is all related to a central accounts table.
> The way I thought of doing this is to grab all the foreign constraints
> and turn them into cascade delete constraints, then delete as many
> accounts as I want.

Here is some code that I have lying around. It does not exactly do what
you are asking for - it is actually moving the fkeys to refer from a
previous generation a table to a new, but you should be able to work
from it.

It is actually part of a Perl script, which is why all @. are preceded
by \. Just remove these. There are also things that start with $. These
are Perl variables that are set in the beginning of the snippet.

# Determine parameters that depends on the SQL Server version.
if (version_ge($X->{SQL_version}, '7')) {
$vclen = 4000;
$varchar = 'nvarchar';
$maxlen = 127; # Max len for an object name.
$quotef = 'quotename'; # Function to quote a name.
$lenf = 'len'; # Function to get string length.
}
else {
die "The SQL generated by aba_tblfkey2 does not run on SQL 6.5\n";
}

# Stringify table names
$old_table = sql_string($old_table);
$this_table = sql_string($this_table);

$sql = <<SQLEND;
-- Move foreign keys from $old_table to $this_table.
SET NOCOUNT ON

DECLARE \@.dummy tinyint,
\@.fktbl sysname,
\@.fkcol sysname,
\@.refcol sysname,
\@.keyno smallint,
\@.constr sysname,
\@.prev_constr sysname,
\@.drop $varchar($vclen),
\@.add1 $varchar($vclen),
\@.add2 $varchar($vclen),
\@.tmpname sysname,
\@.no smallint,
\@.err int

DECLARE sql_fkey_cur INSENSITIVE CURSOR FOR
SELECT k = 1, constr = object_name(constid), fktbl = object_name(fkeyid),
fkcol = col_name(fkeyid, fkey), refcol = col_name(rkeyid, rkey),
keyno
FROM sysforeignkeys
WHERE rkeyid = object_id($old_table)
UNION
SELECT k = 2, NULL, NULL, NULL, NULL, NULL
ORDER BY k, constr, keyno
OPEN sql_fkey_cur

SELECT \@.no = 0
WHILE 1 = 1
BEGIN
FETCH sql_fkey_cur INTO \@.dummy, \@.constr, \@.fktbl, \@.fkcol, \@.refcol,
\@.keyno
IF \@.\@.fetch_status <> 0
BREAK

IF (\@.constr <> \@.prev_constr OR \@.constr IS NULL) AND
\@.prev_constr IS NOT NULL
BEGIN
-- Add the new foreign constraint.
PRINT \@.add1
PRINT \@.add2
EXECUTE(\@.add1 + \@.add2 + ')')
IF \@.\@.error <> 0 BREAK

-- Drop the old reference, once the new is in place. If we drop first,
-- and something goes wrong, we would lose information.
PRINT \@.drop
EXECUTE(\@.drop)
IF \@.\@.error <> 0 BREAK

-- Rename the new reference to the real name.
EXEC \@.err = sp_rename \@.tmpname, \@.prev_constr
SELECT \@.err = coalesce(nullif(\@.\@.error, 0), \@.err)
IF \@.err <> 0 BREAK
END

IF \@.keyno = 1
BEGIN
-- Set up command to create new reference under a temporary name. Use
-- place holders for the column lists.
SELECT \@.tmpname = 'new_' + ltrim(str(\@.no))
SELECT \@.tmpname = \@.tmpname +
substring(\@.constr, 1, $maxlen - $lenf(\@.tmpname))
SELECT \@.no = \@.no + 1
SELECT \@.add1 = 'ALTER TABLE ' + $quotef(\@.fktbl) +
' ADD CONSTRAINT ' + $quotef(\@.tmpname) +
' FOREIGN KEY (' + $quotef(\@.fkcol),
\@.add2 = ') REFERENCES ' + $quotef($this_table) + '(' +
$quotef(\@.refcol)

-- And set up command to drop current constraint.
SELECT \@.drop = 'ALTER TABLE ' + $quotef(\@.fktbl) +
' DROP CONSTRAINT ' + $quotef(\@.constr)
END
ELSE
BEGIN
-- Add more columns to \@.add command.
SELECT \@.add1 = \@.add1 + ', ' + $quotef(\@.fkcol),
\@.add2 = \@.add2 + ', ' + $quotef(\@.refcol)
END

SELECT \@.prev_constr = \@.constr
END

DEALLOCATE sql_fkey_cur
SQLEND

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generate Script

Hi
I am looking for a script that will generate all the foreign keys, defaults
and check constraints only from all the tables in my database.
Can anyone point me in the right direction?Right click on the tables in EM, All Tasks--> Generate Script
Go to the options tab and select what you want
http://sqlservercode.blogspot.com/
"Jaco" wrote:

> Hi
> I am looking for a script that will generate all the foreign keys, default
s
> and check constraints only from all the tables in my database.
> Can anyone point me in the right direction?|||sorry, should have said I want to do this without EM
"SQL" wrote:
> Right click on the tables in EM, All Tasks--> Generate Script
> Go to the options tab and select what you want
> http://sqlservercode.blogspot.com/
> "Jaco" wrote:
>|||Jaco,
SQL-DMO? Check out the Script method in the SQL BOL.
HTH
Jerry
"Jaco" <Jaco@.discussions.microsoft.com> wrote in message
news:3E4BAF12-3CFC-49E3-95ED-43B2E193FDDF@.microsoft.com...
> sorry, should have said I want to do this without EM
> "SQL" wrote:
>