Programmatically picking apart foreign-key constraints

Another quickie today. Don't ask why, but this morning I was trying to programmatically work out which tables have foreign-key references to table Sales, because they're preventing fast partition switching on table Sales.

After more spelunking around I thought I had it with this code:

SELECT * FROM sys.all_objects
WHERE [type] = 'F' AND [parent_object_id] = OBJECT_ID ('Sales');
GO

But that gives me the foreign-key references FROM the Sales table, not TO the Sales table.

Eventually I found the sys.foreign_key_columns table which does the trick, using the following code:

SELECT OBJECT_NAME ([parent_object_id]) AS 'Referencing Table', * FROM sys.foreign_key_columns
WHERE [referenced_object_id] = OBJECT_ID ('Sales');
GO

And it also gives me the column IDs in both tables involved in the constraint. 

Enjoy!

How to change a default constraint

While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll discuss the details more in the fragmentation series I'm starting). As part of the demo, we wanted to change the column default for the leading key of a table from NEWID() to NEWSEQUENTIALID() – problem was that none of us could remember the exact syntax, so we worked it out together. I thought it would make an interesting post, so here it is.

First off, here's my table with a poor clustered index key:

CREATE TABLE BadKeyTable (
    c1 UNIQUEIDENTIFIER DEFAULT NEWID () ROWGUIDCOL,
    c2 SMALLDATETIME DEFAULT GETDATE (),
    c3 CHAR (400) DEFAULT 'a',
    c4 VARCHAR(MAX) DEFAULT 'b');
GO
CREATE CLUSTERED INDEX BadKeyTable_CL ON BadKeyTable (c1);
GO

INSERT INTO BadKeyTable DEFAULT VALUES;
GO

(And you'll notice that I've given up doing nice colors in the T-SQL I post – it's too time consuming). The default we're interested in is in bold above. To change the default, we first need to find the constraint name so we can drop it. There are two queries you can use:

SELECT [name] FROM sys.objects
WHERE [parent_object_id] = OBJECT_ID ('BadKeyTable');
GO

or

SELECT [name] FROM sys.default_constraints
WHERE [parent_object_id] = OBJECT_ID ('BadKeyTable');
GO

The second is obviously the more supported way, as the first will return the names of all sub-objects of this table – all constraints, and all internal tables, such as XML indexes. The second query returns:

name
——————————-
DF__BadKeyTable__c1__7C8480AE
DF__BadKeyTable__c2__7D78A4E7
DF__BadKeyTable__c3__7E6CC920
DF__BadKeyTable__c4__7F60ED59

The constraint we're interested in is the one for the first column – DF__BadKeyTable__c1__7C8480AE. Now we need to drop the constraint and then add the new one as there's no way to simply alter the constraint in-place. We do that using:

ALTER TABLE BadKeyTable DROP CONSTRAINT DF__BadKeyTable__c1__7C8480AE;
GO

ALTER TABLE BadKeyTable ADD CONSTRAINT DF__BadKeyTable__c1
DEFAULT NEWSEQUENTIALID() FOR c1;
GO

And we're done.

Conference Questions Pot-Pourri #8: How to move constraint indexes?

It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post.

This is a question that came up yesterday – can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints? Let's check it out.

First up I'm going to create a couple of tables. Table t1 has a unique constraint backed by a nonclustered index. Table t1 has a primary key constraint backed by a clustered index.

CREATE DATABASE ConstraintTest;
GO
USE ConstraintTest;
GO

CREATE TABLE UniqueConstraint (c1 INT UNIQUE NONCLUSTERED);
GO
INSERT INTO UniqueConstraint VALUES (1);
GO

CREATE TABLE PrimaryKeyConstraint (c2 INT PRIMARY KEY CLUSTERED);
GO
INSERT INTO PrimaryKeyConstraint VALUES (1);
GO

(Oops – in the original post I c&p'd the wrong code and had the second table as a unique nonclustered constraint too – sorry for the mixup) 

Now I'll add another filegroup that we'll try to move the indexes into.

ALTER DATABASE ConstraintTest ADD FILEGROUP ExtraFilegroup;
GO

ALTER DATABASE ConstraintTest ADD FILE (
   NAME = ExtraFile1,
   FILENAME = 'C:\SQLskills\ExtraFile1.ndf',
   SIZE = 5MB)
TO FILEGROUP ExtraFilegroup;
GO

Now I'll try moving the nonclustered index enforcing the unique constraint.

SELECT [name], [index_id] FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('UniqueConstraint')
GO

name                           index_id
—————————— ——–
NULL                           0
UQ__UniqueConstraint__7C8480AE 2

The index we want is index ID=2, so we should be able to move it as follows:

CREATE UNIQUE NONCLUSTERED INDEX UQ__UniqueConstraint__7C8480AE
ON UniqueConstraint (c1) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO

No problem! Now let's try the same thing for the clustered primary key constraint.

SELECT [name], [index_id] FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('PrimaryKeyConstraint')
GO

name                           index_id
—————————— ——–
PK__PrimaryKeyConstr__7E6CC920 1

We only have one choice, so we should be able to rebuild it using:

CREATE CLUSTERED INDEX PK__PrimaryKeyConstr__7E6CC920
ON PrimaryKeyConstraint (c2) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO

Msg 1907, Level 16, State 1, Line 1
Cannot recreate index 'PK__PrimaryKeyConstr__7E6CC920'. The new index definition does not match the constraint being enforced by the existing index.

Hmm – what am I missing? I'll check sys.indexes again and not filter the columns this time:

SELECT * FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('PrimaryKeyConstraint')
GO

object_id  name                           index_id type type_desc is_unique
———- —————————— ——– —- ——— ———
2105058535 PK__PrimaryKeyConstr__7E6CC920 1        1    CLUSTERED 1

data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor
————- ————– ————– ——————– ———–
1             0              1              0                    0

is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks
——— ———– ————— ————— —————-
0
         0           0               1               1

Ah – I was missing the unique attribute on the index. What I need to do is the following:

CREATE UNIQUE CLUSTERED INDEX PK__PrimaryKeyConstr__7E6CC920
ON PrimaryKeyConstraint (c2) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO

And that works fine. So – the answer is yes, you can use CREATE INDEX … WITH DROP_EXISTING to move indexes that enforce constraints. This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX. That method is bad, as it creates a window of opportunity for someone to enter data that violates the constraint while the index is dropped.