Programatticaly finding constraint "check existing data on creatio

A relationship between 2 tables has a property "check existing data on
creation" , "Enforce relationship for replication" , "Enforce relationship
for INSERTs and UPDATEs"
How to determine these programmaticaly from sysconstraints, sysrelationship
or from any other table?.
It appears that this information is hiding in the "status" field in
sysconstraints.
Any help ?
Check properties (CnstIsNotTrusted, CnstIsNotRepl, CnstIsDisabled) using
function objectproperty, they are the contrary.
select
object_name([id]),
object_name(constid),
~ cast(objectproperty(constid, 'CnstIsNotTrusted') as bit) as 'Check
existing data on creation',
~ cast(objectproperty(constid, 'CnstIsNotRepl') as bit) as 'Enforce
relationship for replication',
~ cast(objectproperty(constid, 'CnstIsDisabled') as bit) as 'Enforce
relationship for INSERTs and UPDATEs'
from
sysconstraints
where
object_name([id]) = 'order details'
and object_name(constid) = 'FK_Order_Details_Orders'
AMB
"swami" wrote:

> A relationship between 2 tables has a property "check existing data on
> creation" , "Enforce relationship for replication" , "Enforce relationship
> for INSERTs and UPDATEs"
> How to determine these programmaticaly from sysconstraints, sysrelationship
> or from any other table?.
> It appears that this information is hiding in the "status" field in
> sysconstraints.
> Any help ?

Programatticaly finding constraint "check existing data on creatio

A relationship between 2 tables has a property "check existing data on
creation" , "Enforce relationship for replication" , "Enforce relationship
for INSERTs and UPDATEs"
How to determine these programmaticaly from sysconstraints, sysrelationship
or from any other table?.
It appears that this information is hiding in the "status" field in
sysconstraints.
Any help ?Check properties (CnstIsNotTrusted, CnstIsNotRepl, CnstIsDisabled) using
function objectproperty, they are the contrary.
select
object_name([id]),
object_name(constid),
~ cast(objectproperty(constid, 'CnstIsNotTrusted') as bit) as 'Check
existing data on creation',
~ cast(objectproperty(constid, 'CnstIsNotRepl') as bit) as 'Enforce
relationship for replication',
~ cast(objectproperty(constid, 'CnstIsDisabled') as bit) as 'Enforce
relationship for INSERTs and UPDATEs'
from
sysconstraints
where
object_name([id]) = 'order details'
and object_name(constid) = 'FK_Order_Details_Orders'
AMB
"swami" wrote:

> A relationship between 2 tables has a property "check existing data on
> creation" , "Enforce relationship for replication" , "Enforce relationship
> for INSERTs and UPDATEs"
> How to determine these programmaticaly from sysconstraints, sysrelationshi
p
> or from any other table?.
> It appears that this information is hiding in the "status" field in
> sysconstraints.
> Any help ?

Programatticaly finding constraint "check existing data on creatio

A relationship between 2 tables has a property "check existing data on
creation" , "Enforce relationship for replication" , "Enforce relationship
for INSERTs and UPDATEs"
How to determine these programmaticaly from sysconstraints, sysrelationship
or from any other table?.
It appears that this information is hiding in the "status" field in
sysconstraints.
Any help ?Check properties (CnstIsNotTrusted, CnstIsNotRepl, CnstIsDisabled) using
function objectproperty, they are the contrary.
select
object_name([id]),
object_name(constid),
~ cast(objectproperty(constid, 'CnstIsNotTrusted') as bit) as 'Check
existing data on creation',
~ cast(objectproperty(constid, 'CnstIsNotRepl') as bit) as 'Enforce
relationship for replication',
~ cast(objectproperty(constid, 'CnstIsDisabled') as bit) as 'Enforce
relationship for INSERTs and UPDATEs'
from
sysconstraints
where
object_name([id]) = 'order details'
and object_name(constid) = 'FK_Order_Details_Orders'
AMB
"swami" wrote:
> A relationship between 2 tables has a property "check existing data on
> creation" , "Enforce relationship for replication" , "Enforce relationship
> for INSERTs and UPDATEs"
> How to determine these programmaticaly from sysconstraints, sysrelationship
> or from any other table?.
> It appears that this information is hiding in the "status" field in
> sysconstraints.
> Any help ?

Programatically finding if a row has a foreign key constraint

Hello,

Does anyone know a way from within a .net application to find out if a row has a foreign key constraint before updating it? Or possibly in a SQL Stored Procedure if necessary.

What I am trying to do is basicly simulate a delete of a row in a SQL 2005 database from a .net application. Instead of actually deleting the row I want to set a bool column as deleted. The problem is that I can't orphan records that have a relationship to the one deleted.

This is normaly taken care of automaticly with refrential integrity working properly, but not actually calling delete comands I can't seem to find that out without writing a lot of code to check manually in other tables for that key.

Any help would be appreciated greatly,

Thanks,

Patrick

The following statement will bring you the references tables:

Select TC2.Table_Name from INFORMATION_SCHEMA.Table_constraints TC

INNER JOIN INFORMATION_SCHEMA.Referential_constraints RC

ON TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.Table_constraints TC2

ON TC2.CONSTRAINT_NAME = RC.Constraint_NAME

WHERE TC.TABLE_NAME ='Category' AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks. That worked