| Bret Halford 2005-10-27, 8:21 am |
| Hi Frank,
There actually isn't a requirement that there be a primary key
constraint;
a unique index is sufficient, and there may be multiple unique indexes
that
satisfy the FK's requirement. That said,
First, determine the tablename the constraint was declared on:
select object_name(tableid)
from sysconstraints where constrid =
<constraintid>
go
Use sp_helpconstraint on that table name to get the definition of the
constraint (table and columns it is defined against).
Use sp_helpindex specifying referenced table name to get a list of
indexes
declared against that table. There may be one or more unique indexes
that qualify.
1> use tempdb
2> go
1> create table t1 (x int)
2> go
1> create unique clustered index t1idx on t1 (x)
2> go
1> create table t2 (x int)
2> go
1> alter table t2 add constraint fooconst foreign key (x) references t1
(x)
2> go
1> select * from sysconstraints where constrid = object_id("fooconst")
2> go
colid constrid tableid error status spare2
------ ----------- ----------- ----------- ----------- -----------
0 1332876423 1316876366 0 64 0
(1 row affected)
1> select object_name(tableid)
from sysconstraints where constrid =
object_id("fooconst")
2> go
------
t2
(1 row affected)
1> sp_helpindex t1
2> go
Object has the following indexes
index_name index_keys index_description index_max_rows_per_p
age
index_fillfactor index_reservepagegap
index_created
index_local
---------- ---------- ----------------- -----------------------
---------------- --------------------
--------------------------
------------
t1idx x clustered, unique 0
0 0 Oct 17 2005
2:28PM
Global Index
(1 row affected)
index_ptn_name index_ptn_seg
---------------- -------------
t1idx_1284876252 default
(1 row affected)
If you want more focused output, you can extract just the necessary code
from
the stored procedures.
-bret
Frank Rizzo wrote:
> I have the name of the foreign key and its constraint id. How can I
> deduce the name of the primary key that it references?
>
> Thanks
|