Article ID: 278387
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q278387
BUG #: 58055 (SQLBUG_70)
SYMPTOMS
The ORDINAL_POSITION column in INFORMATION_SCHEMA.KEY_COLUMN_USAGE returns incorrect results. For example:
create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int, c5 int, constraint pk_t1 PRIMARY KEY (c5,c4))
go
create table t2 (tc1 int not null, c1 int not null, c2 int not null, c5 char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4))
go
select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from information_schema.key_column_usage where TABLE_NAME in ('t1', 't2')
The results are:
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ORDINAL_POSITION
--------------- ---------- ----------- ----------------
pk_t1 t1 c4 4
pk_t1 t1 c5 5
fk_t2 t2 c2 3
fk_t2 t2 c1 2
Note that the values for the ORDINAL_POSITION column are incorrect. It should return the relative position of the column in the constraint definition.
The correct results should be:
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ORDINAL_POSITION
--------------- ---------- ----------- ----------------
pk_t1 t1 c4 2
pk_t1 t1 c5 1
fk_t2 t2 c2 2
fk_t2 t2 c1 1
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
Keywords: kbbug kbfix KB278387
