A long time ago, Pinal Dave, a.k.a SQLAuthority (B|T) wrote a post on Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database at the end of which he asked the readers to come up with queries that would re-create the original definition of the primary and foreign keys.

Scripting primary keys is quite easy, but scripting foreign keys is a challenge because of the following:

1. We have both referenced and referencing tables/columns in the mix
2. We may have multiple columns and getting the order of the columns in both – referenced and referencing column lists is important (a column A cannot cross-reference to corresponding column B)
3. We need to (at least everyone should!) specify the update and delete actions

After many years, I got around to this action item and wrote the following script to re-create the original foreign key definition.

USE AdventureWorks2012
GO
;
WITH    ReferentialKeys ( ConstraintName,
                          ReferencedConstraint,
                          DeleteRule,
                          UpdateRule,
                          ReferencingTableSchema,
                          ReferencingTable,
                          ReferencedTableSchema,
                          ReferencedTable )
          AS (
               SELECT   DISTINCT
                        isrc.CONSTRAINT_NAME AS ConstraintName,
                        isrc.UNIQUE_CONSTRAINT_NAME AS ReferencedConstraint,
                        isrc.DELETE_RULE AS DeleteRule,
                        isrc.UPDATE_RULE AS UpdateRule,
                        ReferencingConstraint.TABLE_SCHEMA AS ReferencingTableSchema,
                        ReferencingConstraint.TABLE_NAME AS ReferencingTable,
                        ReferencedConstraint.TABLE_SCHEMA AS ReferencedTableSchema,
                        ReferencedConstraint.TABLE_NAME AS ReferencedTable
               FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS isrc
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
                                    ON isrc.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
                                   AND isrc.CONSTRAINT_NAME = ReferencingConstraint.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencedConstraint
                                    ON isrc.UNIQUE_CONSTRAINT_SCHEMA = ReferencedConstraint.CONSTRAINT_SCHEMA
                                   AND isrc.UNIQUE_CONSTRAINT_NAME = ReferencedConstraint.CONSTRAINT_NAME
             ) ,
        ReferencingColumns ( ReferencingConstraint,
                             ReferencedConstraint,
                             ReferencingColumn )
          AS (
               SELECT   ReferentialKeys.ConstraintName AS ReferencingConstraint,
                        ReferentialKeys.ReferencedConstraint AS ReferencedConstraint,
                        CAST((
                               SELECT   ( ','
                                          + QUOTENAME(ReferencingConstraint.COLUMN_NAME) )
                               FROM     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
                               WHERE    ReferencingConstraint.CONSTRAINT_NAME = ReferentialKeys.ConstraintName
                               ORDER BY ReferencingConstraint.ORDINAL_POSITION
                             FOR
                               XML PATH('')
                             ) AS NVARCHAR(MAX)) AS ReferencedColumn
               FROM     ReferentialKeys
             ) ,
        ReferencedColumns ( ReferencingConstraint, ReferencedConstraint, ReferencedColumn )
          AS (
               SELECT   ReferentialKeys.ConstraintName AS ReferencingConstraint,
                        ReferentialKeys.ReferencedConstraint AS ReferencedConstraint,
                        CAST((
                               SELECT   ( ','
                                          + QUOTENAME(ReferencedConstraint.COLUMN_NAME) )
                               FROM     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencedConstraint
                               WHERE    ReferencedConstraint.CONSTRAINT_NAME = ReferentialKeys.ReferencedConstraint
                               ORDER BY ReferencedConstraint.ORDINAL_POSITION
                             FOR
                               XML PATH('')
                             ) AS NVARCHAR(MAX)) AS ReferencedColumn
               FROM     ReferentialKeys
             )
    SELECT  'IF OBJECT_ID(''' + QUOTENAME(ReferentialKeys.ConstraintName)
            + ''',''F'') IS NOT NULL
    BEGIN
        ALTER TABLE ' + QUOTENAME(ReferentialKeys.ReferencingTableSchema)
            + '.' + QUOTENAME(ReferentialKeys.ReferencingTable)
            + '
            WITH CHECK ADD CONSTRAINT '
            + QUOTENAME(ReferentialKeys.ConstraintName) + ' FOREIGN KEY ('
            + SUBSTRING(ReferencingColumns.ReferencingColumn, 2,LEN(ReferencingColumns.ReferencingColumn))
            + ') REFERENCES '
            + QUOTENAME(ReferentialKeys.ReferencedTableSchema) + '.'
            + QUOTENAME(ReferentialKeys.ReferencedTable) + ' ('
            + SUBSTRING(ReferencedColumns.ReferencedColumn, 2,LEN(ReferencedColumns.ReferencedColumn)) + ')'
            + ' ON DELETE ' + ReferentialKeys.DeleteRule + ' ON UPDATE ' + ReferentialKeys.UpdateRule + ';
    END
GO'
    FROM    ReferentialKeys
            INNER JOIN ReferencingColumns
                    ON ReferentialKeys.ConstraintName = ReferencingColumns.ReferencingConstraint
                   AND ReferentialKeys.ReferencedConstraint = ReferencingColumns.ReferencedConstraint
            INNER JOIN ReferencedColumns
                    ON ReferentialKeys.ConstraintName = ReferencedColumns.ReferencingConstraint
                   AND ReferentialKeys.ReferencedConstraint = ReferencedColumns.ReferencedConstraint
    ORDER BY ReferentialKeys.ReferencingTableSchema,
             ReferentialKeys.ReferencingTable,
             ReferentialKeys.ConstraintName
GO

It’s now time to take this query around for a test drive. Here’s the definition of “FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID” that my query produces (formatted for better readability):

IF OBJECT_ID('[FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]','F') IS NOT NULL
    BEGIN
        ALTER TABLE [Sales].[SalesOrderDetail]
            WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
                       FOREIGN KEY ([SpecialOfferID],[ProductID])
                       REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID],[ProductID])
                       ON DELETE NO ACTION
                       ON UPDATE NO ACTION;
    END
GO

And here’s the query that SSMS produces when I ask it to script out the key for me.

IF NOT EXISTS (SELECT *
               FROM sys.foreign_keys
               WHERE object_id = OBJECT_ID(N'[Sales].[FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]')
                 AND parent_object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]'))
    ALTER TABLE [Sales].[SalesOrderDetail]
        WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
                   FOREIGN KEY([SpecialOfferID], [ProductID])
                   REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
GO

Fairly accurate, wouldn’t you say? 🙂

Do share any such useful scripts that you may have in the Scripts page on this site.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is not specific to SQL Server, but to a general developer mindset. It all started with the following tip that came up when launching Visual Studio 6.0 (yes, I do work on products written in VC++ 6.0, and I always have Visual Studio show me tips on launch).

Code, just like software architecture is a dynamic thing. Over time, the code would undergo various transformations (to incorporate various requirements), become more configurable and would require corrections as and when defects are discovered.

In my many years of experience, I have worked with two kinds of developers:

1. One group comprises of those who rely solely on memory and/or search engines and believe all pieces of code can be written from scratch every time
2. Then comes the other group who religiously maintain a repository of all pieces of code that they have found useful

As time passes, the later group of developers would have invested significant time in documenting, indexing and improving upon these code snippets to ensure that they can reach out to the ever evolving and self-correcting code faster. Whereas the former group of people might end up making the same mistakes over and over again.

Learning from this group, I maintain an SSMS solution that contains all the possibly re-usable and PoC level T-SQL code that I have ever written. I find that this helps me write a query faster and with improved quality.

I encourage every developer to start out with a simple repository of all possible re-usable code that they own and once it becomes a habit, watch it evolve over time. It will surely help to write the code right the first time!

Until we meet next time,

Be courteous. Drive responsibly.

I continue to be surprised by the various practices that developers use to achieve the same result.

The common understanding with SQL Server DDL is that in most cases, there is only one method to implement a given design element. For example, to ensure that a column does not accept NULL values, the solution that comes to mind is to define it as a NOT NULL column.

Recently, I came across a trainee who proposed to implement the NOT NULL requirement for a given column in a way that’s not conventional – he proposed to use CHECK constraints. I had never heard of anyone using CHECK constraints to enforce a NOT NULL check, but I liked the thought process and hence wrote a quick prototype to demonstrate why this was not a good idea.

The following is how I explained why we don’t use CHECK constraints to enfore NOT NULL values (for sake of brevity, I am demonstrating with a table that has only one column).

USE tempdb ;
GO

--Safety Check
IF OBJECT_ID('dbo.NotNullCheckConstraint', 'U') IS NOT NULL 
    BEGIN
        DROP TABLE dbo.NotNullCheckConstraint ;
    END
GO

--Table kept short for sake of brevity
CREATE TABLE dbo.NotNullCheckConstraint ( Age INT ) ;
GO

ALTER TABLE dbo.NotNullCheckConstraint
    ADD CONSTRAINT chk_AgeSpecified CHECK ( Age > 0 ) ;
GO

On most environments, though, this will not stop the user from entering NULLs. Let’s try it out:

USE tempdb;
GO
--Insert Test Data to prove that the CHECK constraint does not stop NULL values
INSERT  INTO dbo.NotNullCheckConstraint ( Age )
VALUES  ( NULL ),
        ( NULL ) ;
GO

--Select data from the table
SELECT  Age
FROM    dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
NULL
NULL
*/

The only real way to enforce a NOT NULL values via CHECK constraint is to alter the constraint to the following:

--Truncate the test table
TRUNCATE TABLE dbo.NotNullCheckConstraint;
GO

--Drop the old constraint
ALTER TABLE dbo.NotNullCheckConstraint 
    DROP CONSTRAINT chk_AgeSpecified ;
GO

--Create a new constraint
ALTER TABLE dbo.NotNullCheckConstraint
    ADD CONSTRAINT chk_AgeSpecifiedWithNotNullCheck CHECK ( Age IS NOT NULL
                                                            AND Age > 0 ) ;
GO

Let us now try to insert NULL values into the table:

USE tempdb ;
GO
--Insert Test Data to prove that the new CHECK constraint did stop NULL values
INSERT  INTO dbo.NotNullCheckConstraint ( Age )
VALUES  ( NULL ),
        ( NULL ) ;
GO

--Select data from the table
SELECT  Age
FROM    dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
*/

We encounter the following error message:

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint “chk_AgeSpecifiedWithNotNullCheck”. The conflict occurred in database “tempdb”, table “dbo.NotNullCheckConstraint”, column ‘Age’.
The statement has been terminated.

(0 row(s) affected)

In this case, the CHECK constraint did indeed prevent us from inserting NULL values into the table.

But, is this a good idea?

The answer, in my humble opinion is – No.

And, I have two reaons for it:

1. CHECK constraints are ideally suited to enforce business rules and business logic rather than data integrity




• They are evaluated after the SQL Server database engine is sure that the input values do not violate any properties or boundaries of the defined column


• I even wrote an article about the constraint execution sequence on SQLServerCentral.com here – Leveraging Constraint Evaluation Sequence in SQL Server. The examples in the article demonstrate how the NOT NULL constraint is evaluated before CHECK constraints


2. A CHECK constraint can be disabled by a user having sufficient priviledges thereby allowing the application to insert NULL values – a NOT NULL column definition on the other hand would remain in effect

Here’s a quick check for point #2 (disabling a CHECK constraint may allow to enter invalid data).

USE tempdb;
GO
--Now, disable the constraint
ALTER TABLE dbo.NotNullCheckConstraint
    NOCHECK CONSTRAINT chk_AgeSpecifiedWithNotNullCheck;
GO

--Insert Test Data to prove that the new CHECK constraint did NOT stop NULL values
INSERT  INTO dbo.NotNullCheckConstraint ( Age )
VALUES  ( NULL ),
        ( NULL ) ;
GO

--Select data from the table
SELECT  Age
FROM    dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
NULL
NULL
*/

/********************************************************************************/
USE tempdb ;
GO
--Now, re-enable the constraint
--Notice no error is reported
ALTER TABLE dbo.NotNullCheckConstraint
    CHECK CONSTRAINT chk_AgeSpecifiedWithNotNullCheck;
GO

--Insert Test Data to prove that the new CHECK constraint did stop NULL values
--This will report an error
INSERT  INTO dbo.NotNullCheckConstraint ( Age )
VALUES  ( NULL ),
        ( NULL ) ;
GO

--Select data from the table - results from earlier run are still available
SELECT  Age
FROM    dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
NULL
NULL
*/

Summary

Roughly defined, patterns are common solutions to common problems. There is a reason why Patterns and Practices were developed and this a classic example. A CHECK constraint can be used to enforce a NOT NULL check, but it is not a normal pattern and the reaons are:

1. CHECK constraints are evaluated after NOT NULL validation


2. CHECK constraints may be disabled allowing the user to enter invalid data into the table

Further Reading

• Leveraging Constraint Evaluation Sequence in SQL Server [Link]


• Using Regular Expressions with CHECK constraints [Link]


• Defining CHECK constraints on computed columns [Link]


• Defining “complex” CHECK constraints [Link]

Until we meet next time,

Be courteous. Drive responsibly.

I was attempting to write an ad-hoc query which aggregated some data using the GROUP BY clause for a quick data check when a copy-paste error made me stumble upon the following error:

Msg 164, Level 15, State 1, Line 8
Each GROUP BY expression must contain at least one column that is not an outer reference.

The query that returned the error looked like:

USE AdventureWorks2008R2 ;
GO
DECLARE @accountNumber VARCHAR(30) = '10-4020-000676'

SELECT  YEAR(OrderDate) AS PurchaseYear,
        @accountNumber AS AccountNumber,
        SUM(TotalDue) AS TotalPurchase
FROM    Sales.SalesOrderHeader
WHERE   AccountNumber = @accountNumber
GROUP BY YEAR(OrderDate),
        @accountNumber ;
GO

What I typically do when I work with the group by clause is that I take the columns from the SELECT clause, remove the aggregations and use that list for the GROUP BY clause. In that process, I ended up with a variable (@accountNumber) in the GROUP BY list. A variable is ultimately an expression which was treated by SQL Server as an outer reference – which is not allowed in T-SQL. The solution therefore is to change the list for the GROUP BY to not use variables.

USE AdventureWorks2008R2 ;
GO
DECLARE @accountNumber VARCHAR(30) = '10-4020-000676';

SELECT  YEAR(OrderDate) AS PurchaseYear,
        @accountNumber AS AccountNumber,
        SUM(TotalDue) AS TotalPurchase
FROM    Sales.SalesOrderHeader
WHERE   AccountNumber = @accountNumber
GROUP BY YEAR(OrderDate),
        AccountNumber ;  --Notice that the variable has been replaced 
                         --with the corresponding field name
GO

[Edit – 02/16/2014 – 01:20AM IST]:

Based on a couple of comments that I received on this post, I would like to elaborate further on the behaviour that is exhibited by SQL Server.

• The GROUP BY clause can operate on any expressions except single-row, single-column sub-queries


• This expression must contain at least one column belonging to the tables referenced in the FROM clause of the statement where GROUP BY is being applied


• A variable is a single-row, single-column expression (SELECT @accountNumber is perfectly valid), and does not reference any column from the tables used in the statement – this is what makes the variable invalid in the GROUP BY clause

Until we meet next time,

Be courteous. Drive responsibly.

A couple of months ago, I read a post from Madhivanan [B|T] which taught me how to drop multiple tables in a single DROP TABLE statement [Link]. Last week I was working on a deployment script when I it hit me that I could drop multiple constraints (and columns) at the same time using the ALTER TABLE…DROP statement.

This method works on check, default and unique constraints.

Here’s a simple example:

USE AdventureWorks2012;
GO
ALTER TABLE HumanResources.Employee
    DROP CONSTRAINT CK_Employee_BirthDate,
                    CK_Employee_Gender,
                    CK_Employee_HireDate,
                    CK_Employee_MaritalStatus,
                    CK_Employee_SickLeaveHours,
                    CK_Employee_VacationHours,
                    DF_Employee_CurrentFlag,
                    DF_Employee_ModifiedDate,
                    DF_Employee_rowguid,
                    DF_Employee_SalariedFlag,
                    DF_Employee_SickLeaveHours,
                    DF_Employee_VacationHours;
GO

You can write a similar script for dropping multiple columns in the same manner.

Further Reading

• ALTER TABLE [MSDN/BOL Link]
• Drop multiple tables in a single DROP TABLE statement [Link from Madhivanan [B|T]]
• Leveraging constraint evaluation sequence in SQL Server [Link]

Until we meet next time,

Be courteous. Drive responsibly.