[sql] Change primary key column in SQL Server

UPDATE

Here are the constraints as a result of the query

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'history'

CONSTRAINT_NAME   COLUMN_NAME  ORDINAL_POSITION
PK_history        userKey       1
PK_history        name          2

Here is the result of the query

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'history'

CONSTRAINT_NAME   CONSTRAINT_TYPE  IS_DEFERRABLE  INITIALLY_DEFERRED
PK_history        PRIMARY KEY      NO             NO

END UPDATE

My host provides an interface to my SQL Server DB via ASP.NET Enterprise Manager.

I have 3 columns in my history table:

  • userId (key, int, NULL not allowed)
  • name (key, string, NULL not allowed)
  • id (not key, int, NULL allowed)

I want to make the id column the only key.

To do that, I believe I need to:

  1. Make sure there are no NULLs in that column for any row
  2. Set the column to not allow NULLs
  3. Add the column as a primary key
  4. Remove the other 2 columns as keys

However, when I use the UI provided, it never works. Sometimes it'll just look like it tries to do something but it never changes when I refresh the view of the columns. It occasionally creates a temp table that looks like it tried to do some of the operation, but that never gets copied/overwrites the original table that I'm trying to change.

When I try using a query, the changes don't show up either. Here are the queries I think I need:

    SELECT * from history WHERE id is NULL     <---- This shows 0 results

    ALTER TABLE history
    ALTER COLUMN id int NOT NULL

    ALTER TABLE history ADD PRIMARY KEY (id)

    ALTER TABLE history
    DROP CONSTRAINT userId
    DROP CONSTRAINT name
    GO

I've only gotten to the attempt to disallow NULLs and to add the primary key for the id column. It doesn't seem to work. Can someone point me in the right direction? Thanks!

This question is related to sql sql-server database shared-hosting

The answer is


Necromancing.
It looks you have just as good a schema to work with as me... Here is how to do it correctly:

In this example, the table name is dbo.T_SYS_Language_Forms, and the column name is LANG_UID

-- First, chech if the table exists...
IF 0 < (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'T_SYS_Language_Forms'
)
BEGIN
    -- Check for NULL values in the primary-key column
    IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)
    BEGIN
        ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL 

        -- No, don't drop, FK references might already exist...
        -- Drop PK if exists 
        -- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name 
        --DECLARE @pkDropCommand nvarchar(1000) 
        --SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
        --WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
        --AND TABLE_SCHEMA = 'dbo' 
        --AND TABLE_NAME = 'T_SYS_Language_Forms' 
        ----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
        --))
        ---- PRINT @pkDropCommand 
        --EXECUTE(@pkDropCommand) 

        -- Instead do
        -- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';


        -- Check if they keys are unique (it is very possible they might not be) 
        IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)
        BEGIN

            -- If no Primary key for this table
            IF 0 =  
            (
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
                AND TABLE_SCHEMA = 'dbo' 
                AND TABLE_NAME = 'T_SYS_Language_Forms' 
                -- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
            )
                ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)
            ;

            -- Adding foreign key
            IF 0 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms') 
                ALTER TABLE T_ZO_SYS_Language_Forms WITH NOCHECK ADD CONSTRAINT FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms FOREIGN KEY(ZOLANG_LANG_UID) REFERENCES T_SYS_Language_Forms(LANG_UID); 
        END -- End uniqueness check
        ELSE
            PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...' 
    END -- End NULL check
    ELSE
        PRINT 'FSCK, need to figure out how to update NULL value(s)...' 
END 

Assuming that your current primary key constraint is called pk_history, you can replace the following lines:

ALTER TABLE history ADD PRIMARY KEY (id)

ALTER TABLE history
DROP CONSTRAINT userId
DROP CONSTRAINT name

with these:

ALTER TABLE history DROP CONSTRAINT pk_history

ALTER TABLE history ADD CONSTRAINT pk_history PRIMARY KEY (id)

If you don't know what the name of the PK is, you can find it with the following query:

SELECT * 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 WHERE TABLE_NAME = 'history'

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to shared-hosting

How to access site through IP address when website is on a shared host? PHP session lost after redirect Change primary key column in SQL Server