In sp_fkeys, what do the values for Deferrability mean?

[PaulW]

Hi,

I am trying to write a t-sql script to march through the database and add
the "Not For Replication" option to all the foreign keys. I am using the
system sp sp_fkeys. There is one column in the returned results set that I
can not find any real information on, Deferrability. I am getting a value 7
returned for all the foreign keys. I would like to make this routine as
general as possible. Does any one have an idea of what the values of
Deferrability mean?

tia,
Paul

[Nigel Rivett]

If you look at sp_fkeys in master you will see that it
always returns 7 in this field.

-----Original Message-----
Hi,
I am trying to write a t-sql script to march through the

database and add

the "Not For Replication" option to all the foreign keys.

I am using the

system sp sp_fkeys. There is one column in the returned

results set that I

can not find any real information on, Deferrability. I am

getting a value 7

returned for all the foreign keys. I would like to make

this routine as

general as possible. Does any one have an idea of what

the values of

Deferrability mean?
tia,
Paul
.

[David Browne]

Post by PaulW
Hi,
I am trying to write a t-sql script to march through the database and add
the "Not For Replication" option to all the foreign keys. I am using the
system sp sp_fkeys. There is one column in the returned results set that I
can not find any real information on, Deferrability. I am getting a value 7
returned for all the foreign keys. I would like to make this routine as
general as possible. Does any one have an idea of what the values of
Deferrability mean?

this means
DEFERRABILITY = 7 /* SQL_NOT_DEFERRABLE */

A deferrable contraint (in Oracle at least) is a constraint for which you
can defer enforcement until the end of a transaction. This is different
than disabling the constraint since it only affects the current transaction,
and the constraint is enforced when the transaction is commited.

It's a handy feature for loading or deleting data with complicated foreign
key relationships. Once you defer the constraints, you can load or delete
the data in any order. Once you're done, commit the transaction and all the
constraints will be checked.

Anyway SQL Server does not support deferrable constraints, so I assume this
column was put in for "future growth".

David

[Louis Davidson]

Post by David Browne
Anyway SQL Server does not support deferrable constraints, so I assume this
column was put in for "future growth".

A more reasonable assumption is that this was put in to meet some standard
:) Let's hope this was put in for future growth, but if you are holding
your breath, make sure you are near something soft.
--
----------------------------------------------------------------------------
-----------
Louis Davidson (***@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)