------------------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.
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 ?
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...
Member
229 Points
904 Posts
How can i alter a primary key on an existing table ?
Oct 11, 2019 06:29 AM|robby32|LINK
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
Participant
1700 Points
531 Posts
Re: How can i alter a primary key on an existing table ?
Oct 11, 2019 07:35 AM|Yang Shen|LINK
Hi robby32,
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:
Please refer to below code:
Notice: set all your fields as primary key is not a good practice and it will cause unkown problems.
Best Regard,
Yang Shen
Contributor
3921 Points
3313 Posts
Re: How can i alter a primary key on an existing table ?
Oct 11, 2019 08:52 AM|DA924|LINK
What you are talking about and primary-key of multiple table columns is not optimal.
Member
229 Points
904 Posts
Re: How can i alter a primary key on an existing table ?
Oct 11, 2019 09:23 AM|robby32|LINK
All-Star
45720 Points
16620 Posts
Re: How can i alter a primary key on an existing table ?
Oct 11, 2019 09:50 AM|PatriceSc|LINK
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...