The problem

I got started with relational databases with mysql,  so I’m in the habit of making database changes with SQL scripts,  rather than using a GUI.  Microsoft SQL Server requires that we specify the name of a unique constraint when we want to drop it.  If you’re thinking ahead,  you can specify a name when you create the constraint;  if you don’t,  SQL Server will make up an unpredictable name,  so you can’t write a simple script to drop the constraint.

A Solution

In the spirit of “How To Drop A Primary Key in SQL Server“,  here’s a stored procedure that queries the data dictionary to find the names of any unique constraint on a specific table and column and drop them:

CREATE PROCEDURE [dbo].[DropUniqueConstraint]
    @tableName NVarchar(255),
    @columnName NVarchar(255)
AS
    DECLARE @IdxNames CURSOR
   
    SET @IdxNames = CURSOR FOR
        select sysindexes.name from sysindexkeys,syscolumns,sysindexes
            WHERE
                syscolumns.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND sysindexkeys.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND sysindexes.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND syscolumns.name=@columnName
                AND sysindexkeys.colid=syscolumns.colid
                AND sysindexes.[indid]=sysindexkeys.[indid]
                AND (
                    SELECT COUNT(*) FROM sysindexkeys AS si2
                    WHERE si2.id=sysindexes.id
                    AND si2.indid=sysindexes.indid
                )=1

    OPEN @IdxNames
    DECLARE @IdxName Nvarchar(255)
    FETCH NEXT FROM @IdxNames INTO @IdxName    

    WHILE @@FETCH_STATUS = 0 BEGIN
        DECLARE @dropSql Nvarchar(4000)

        SET @dropSql=
            N'ALTER TABLE  ['+@tableName+ N']
                DROP CONSTRAINT ['+@IdxName+ N']'
        EXEC(@dropSql)
           
        FETCH NEXT FROM @IdxNames
        INTO @IdxName
    END
CLOSE @IdxNames
DEALLOCATE @IdxNames

Usage is straightforward:

EXEC [dbo].[DropUniqueConstraint]
    @tableName='TargetTable',
    @columnName='TargetColumn'

This script has a limitation:  it only drops unique constraints that act on a single column,  not constraints that act on multiple columns.   It is smart enough,  however,  to not drop multiple-column constraints in case one of them involves @columnName.

Feedback from SQL stored procedure wizards would be mostly certainly welcome.

One of my pet peeves with Microsoft’s SQL Server is that it requires you to know the name of constraints and indexes that you’re dropping. This isn’t a problem when you’re working with the management studio, because it looks at the database metadata and generates code like

ALTER TABLE [dbo].[someTable]  DROP CONSTRAINT [PK__someTabl__3214EC07271AA44F]

It’s less convenient, however, when you’re writing a set of migration scripts in SQL to implement changes that you make over the database in time. Specifically, if you create the table twice in two different databases, the hexadecimal string in the name of the key will be different — the ALTER TABLE statement will fail when you try to drop the index later, since the name of the key won’t match.

Here’s a stored procedure that looks up the name of the primary key in the system catalog and uses dynamic SQL to drop the index:

CREATE PROCEDURE [dbo].[DropPrimaryKey]
    @tableName Varchar(255)
AS
    /*
       Drop the primary key on @TableName
       http://gen5.info/q/
       Version 1.1
       June 9, 2008
    */

    DECLARE @pkName Varchar(255)

    SET @pkName= (
        SELECT [name] FROM sysobjects
            WHERE [xtype] = 'PK'
            AND [parent_obj] = OBJECT_ID(N'[dbo].['+@tableName+N']')
    )
    DECLARE @dropSql varchar(4000)

    SET @dropSql=
        'ALTER TABLE [dbo].['+@tableName+']
            DROP CONSTRAINT ['+@PkName+']'
    EXEC(@dropSql)
GO

Once you've loaded this stored procedure,  you can write

EXEC [dbo].[DropPrimaryKey] @TableName='someTable'

It’s just that simple. Similar stored procedures can be written to convert fields from NOT NULL to NULL and do other operation which required a named constraint.

For years people have talked about an “impedance mismatch” between relational databases and object-oriented languages. Big answers have been proposed to this problem, such as object-relational mapping, but we’ve yet to see a big answer that’s entirely satisfactory — this article is one of a series that tries to break the problem into a set of smaller problems that have simple answers.

Many modern languages, such as Java, Perl, PHP and C#, are derived from C. In C, string literals are written in double quotes, and a set of double quotes can’t span a line break. One advantage of this is that the compiler can give clearer error messages when you forget the close a string. Some C-like languages, such as Java and C# are like C in that double quotes can’t span multiple lines. Other languages, such as Perl and PHP, allow it. C# adds a new multiline quote operator, @”" which allows quotes to span multiple lines and removes the special treatment that C-like languages give to the backslash character (\).

The at-quote is particularly useful on the Windows platform because the backslash used as a path separator interacts badly with the behavior of quotes in C, C++ and many other languages. You need to write

String path="C:\\Program Files\\My Program\\Some File.txt";

With at-quote, you can write

String path=@"C:\Program Files\My Program\Some File.txt";

which makes the choice of path separator in MS-DOS seem like less of a bad decision.

Now, one really great thing about SQL is that the database can optimize complicated queries that have many joins and subselects — however, it’s not unusual to see people write something like

command.CommandText = "SELECT firstName,lastName,(SELECT COUNT(*) FROM comments WHERE postedBy
=userId AND flag_spam='n') AS commentCount,userName,city,state,gender,birthdate FROM user,user
Location,userDemographics WHERE user.userId=userLocation.userId AND user.userId=userDemographi
cs.user_id AND (SELECT COUNT(*) FROM friendsReferred WHERE referringUser=userId)>10 ORDER BY c
ommentCount HAVING commentCount>3";

(line-wrapped C# example) Complex queries like this can be excellent, because they can run thousands of times faster than loops written in procedural or OO languages which can fire off hundreds of queries, but a query written like the one above is a bit unfair to the person who has to maintain it.

With multiline quotes, you can continue the indentation of your code into your SQL:

command.CommandText = @"
   SELECT
       firstName
      ,lastName
      ,(SELECT COUNT(*) FROM comments
         WHERE postedBy=userId AND flag_spam='n') AS commentCount
      ,userName
      ,city
      ,state
      ,gender
      ,birthdate
   FROM user,userLocation,userDemographics
   WHERE
      user.userId=userLocation.userId
      AND user.userId=userDemographics.user_id
      AND (SELECT COUNT(*) FROM friendsReferred
         WHERE referringUser=userId)>10
   ORDER BY commentCount
   HAVING commentCount>3
";

Although this might not be quite quite as cool as LINQ, it works with Mysql, Microsoft Access or any database you need to connect to — and it works in many other languages, such as PHP (in which you could use ordinary double quotes.) In languages like Java that don’t support multiline quotes, you can always write

String query =
   " SELECT"
  +"    firstName"
  +"   ,lastName"
  +"   ,(SELECT COUNT(*) FROM comments"
  +"      WHERE postedBy=userId AND flag_spam='n') AS commentCount"
  +"   ,userName"
  +"   ,city"
  +"   ,state"
  +"   ,gender"
  +"   ,birthdate"
  +" FROM user,userLocation,userDemographics"
  +"    WHERE"
  +"       user.userId=userLocation.userId"
  +"       AND user.userId=userDemographics.user_id"
  +"       AND (SELECT COUNT(*) FROM friendsReferred"
  +"          WHERE referringUser=userId)>10"
  +" ORDER BY commentCount"
  +" HAVING commentCount>3";

but that’s a bit more cumbersome and error prone.