Holy Foreign Keys and Indexes

  • Jack Corbett

    SSC Guru

    Points: 184381

    I don't think I'd call this a bug. SQL Server can use the Primary Key or a Unique Constraint/index to as the reference column(s) for a foreign key. In this case (or these cases for those who have experienced it), SQL Server is probably using a unique clustered index to enforce a foreign key because it believes that it will improve performance. I'd have to assume that the lead column in the unique clustered index is the same column as the primary key. I'd be checking to see if both indexes are needed and perhaps make the unique clustered index the PK.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • rstelma

    SSC Enthusiast

    Points: 187

    Hi,

    I hope you guys can help me get something straightened out here. I ran this query on the Master DB:

    select f.name

    , i.name

    , object_name(i.object_id) as tablename

    , i.is_unique,i.is_primary_key

    , i.type_desc

    , f.key_index_id

    from sys.foreign_keys f

    join sys.indexes i

    on i.object_id = f.referenced_object_id

    and i.index_id = f.key_index_id

    With these results.

    name name tablename is_unique is_primary_key type_desc key_index_id

    FK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED 1

    FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED 1

    FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1

    FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1

    FK_ProjectWorkOrderEmployees_ProjectWorkOrders PK_ProjectWorkOrders ProjectWorkOrders 1 1 CLUSTERED 1

    I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.

    Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys.

    Very sorry if this is too far off topic. If so I will repost as a new thread.

    Thank you,

    Richard

  • Jack Corbett

    SSC Guru

    Points: 184381

    rstelma (10/11/2011)


    Hi,

    I hope you guys can help me get something straightened out here. I ran this query on the Master DB:

    select f.name

    , i.name

    , object_name(i.object_id) as tablename

    , i.is_unique,i.is_primary_key

    , i.type_desc

    , f.key_index_id

    from sys.foreign_keys f

    join sys.indexes i

    on i.object_id = f.referenced_object_id

    and i.index_id = f.key_index_id

    With these results.

    name name tablename is_unique is_primary_key type_desc key_index_id

    FK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED 1

    FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED 1

    FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1

    FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1

    FK_ProjectWorkOrderEmployees_ProjectWorkOrders PK_ProjectWorkOrders ProjectWorkOrders 1 1 CLUSTERED 1

    I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.

    Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys.

    Very sorry if this is too far off topic. If so I will repost as a new thread.

    Thank you,

    Richard

    I wouldn't expect to see any results for user tables when running that query. Those DMV's are scoped to the database that you are in. If there are FK's showing in master that means the tables, indexes, and foreign keys were created in master instead of the user database or in addition to the user database.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • rstelma

    SSC Enthusiast

    Points: 187

    Hi Jack,

    Damn database gremlins.

    Now when I run the query on the Master I get no results and 156 on the DB where the tables exist and where I expect to see the Foreign Keys. I swear I ran that on the Master and kept switching back and forth.

    Anyway... thank you. Very much appreciate your reply.

    Richard

  • Misha_SQL

    SSCertifiable

    Points: 5401

    Thank you for sharing this obscure issue. I hope it saves me time someday.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    if i understand this correctly there are two unique indexes that were created on the same column or columns. one clustered unique index and on PK unclustered. any reason for that? why not just use one or the other.

    if this is the case it doesn't sound like a bug to me

  • Dan Guzman

    Default port

    Points: 1481

    SQL Server allows a foreign key to reference any column(s) that is guaranteed to be unique. The Books Online states "FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table."

    When the foreign key is created, SQL Server will bind the foreign key to the index. There is no documented behavior on which constraint or index will be chosen when multiple candidate unique indexes exist. The rules SQL Server uses are apparently different between SQL 2005 and SQL 2008+. I don't think it's correct to classify a change in behavior as a defect unless it's contrary to documentation.

    With some experimentation, I learned that SQL 2005 chooses the clustered index on a table and the first non-clustered (lowest index_id) on a heap. However, SQL 2008 and SQL 2008 R2 choose the first non-clustered index on both tables and heaps; the clustered index is used only if no candidate non-clustered indexes exist.

Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply