How can i alter a primary key on an existing table ?

Question

User1253338400 posted

Hi ,

I have a table with a primary key as follows

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='MyTable')

BEGIN CREATE TABLE [dbo].[MyTable]( [Id] [uniqueidentifier] NOT NULL,

[CustId] [uniqueidentifier] NULL,

[FirstName] [nvarchar] (510) NULL,

[LastName] [nvarchar] (510) NULL,

[CustSelect] [nvarchar] (510) NULL,   

PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] END;

How can i change it so that the primaru key is on Id, CustId,FirstName, LastName and CustSelect ?

The table already exists , so  do i need to do an alter table and update ?

thanks

Answer 1

User-719153870 posted

Hi robby32,

robby32

How can i change it so that the primaru key is on Id, CustId,FirstName, LastName and CustSelect ?

Are you trying to set all your table fields as primary key?

If so, then you are trying to make somethinf called a SQL Composite Key.

To achieve this goal, you will need to follow below steps:

• First, you will need to find your already exist primary key CONSTRAINT and then drop it.
• Then, alter all of your fields to NOT NULL allowed.
• Finally, alter your table to create a composite key.

Please refer to below code:

------------------below you created your original table
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='MyTable')

BEGIN CREATE TABLE [dbo].[MyTable]( [Id] [uniqueidentifier] NOT NULL,

[CustId] [uniqueidentifier] NULL,

[FirstName] [nvarchar] (510) NULL,

[LastName] [nvarchar] (510) NULL,

[CustSelect] [nvarchar] (510) NULL,   

PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] END;

-------------------below you select all your primary key constraint from your table
SELECT DB_NAME() AS Database_Name
,sc.name AS 'Schema_Name'
,o.Name AS 'Table_Name'
,i.Name AS 'Index_Name'
,c.Name AS 'Column_Name'
,ic.key_ordinal
,i.type_desc AS 'Index_Type'
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.is_primary_key = 1
AND o.name = 'MyTable'
ORDER BY o.Name, i.Name, ic.key_ordinal

--------------------below you drop your existed primary key constraint
ALTER TABLE MyTable  
DROP CONSTRAINT PK__MyTable__3214EC0776969D2E; 

--------------------below you set all your fields to NOT NULL
ALTER TABLE MyTable ALTER COLUMN [CustId] [uniqueidentifier] NOT NULL
ALTER TABLE MyTable ALTER COLUMN [FirstName] [nvarchar] (510) NOT NULL
ALTER TABLE MyTable ALTER COLUMN [LastName] [nvarchar] (510) NOT NULL
ALTER TABLE MyTable ALTER COLUMN [CustSelect] [nvarchar] (510) NOT NULL

--------------------below you created a composite key.
ALTER TABLE MyTable 
   ADD CONSTRAINT PK_CUSTID PRIMARY KEY (Id, CustId,FirstName, LastName, CustSelect);

Notice: set all your fields as primary key is not a good practice and it will cause unkown problems.

Best Regard,

Yang Shen

Answer 2

User1120430333 posted

What you are talking about and primary-key of multiple table columns is not optimal. 

Answer 3

User1253338400 posted
I need to as i insert using nhibernate and when i do a save of the same row it actually inserts a second row instead of updating. So i thought if i set all fields as primary then nhibernate will know that this already exists so it updates instead.. is that the the recommended approach ?

Answer 4

User753101303 posted

Hi,

It should do what you asked for. I'm using EF but I suspect the problem  could be you don't pass back a key value and so nhibernate infers that you want a new entity rather than updating the current one.

Stricly speaking you can sometimes have to handle people with the same names. Also from a db point of view you could now insert the same Id twice...