Keys in SQL Server are Global - Why?

Question

Indexes and Constraints in MS SQL Server are local to the table they are defined in. So you can create the same index or constraint name in more than one table with no trouble.

Foreign Key Constraints (which as everyone should know are NOT keys, but constraints) will appear in the Keys subfolder and their names appear to be globally scoped.

Anybody know why "keys" have globally scoped names? I do understand the differences between keys and indexes. I don't understand why there should be such a difference in name scoping. In all the cases they are "local" to the table they are defined for.

TIA

Answer 1

According to "SQL-99 Complete, Really" by Peter Gulutzan and Trudy Pelzer, constraint names are qualified by the schema in which they live, and the table that the constraint affects must live in the same schema. But the constraint name is not qualified by that table name. The constraint name must be unique within the schema it lives in.

• YES: myschema.some_primary_key
• NO: myschema.mytable.some_primary_key

As for "why" this is the case, I assume it's related to a kind of constraint in SQL-99 called an ASSERTION. An assertion doesn't "belong to" a single table, as is true for primary key, foreign key, and check constraints. An assertion declares a condition that must be true for any INSERT/UPDATE/DELETE to succeed, and the assertion can reference multiple tables. It wouldn't make sense to qualify these constraint names by any table.

I don't know how many RDBMS brands implement SQL assertions (actually I don't know a single one), but they might as well conform to the standard SQL rules for constraint names, so in the future they can choose to implement the assertion feature according to the standard.