I have an existing table called Person which already has existing 1000 rows of data. In this table I have 5 fields:
The table already has an existing compound-key [PersonId, Pname] as the primary key.
Now I want to modify that existing compound-key to have one more field [PMid].
How can I write an ALTER statement to do this?
drop constraint and recreate it
alter table Persion drop CONSTRAINT <constraint_name>
alter table Persion add primary key (persionId,Pname,PMID)
edit:
you can find the constraint name by using the query below:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'
I think something like this should work
-- drop current primary key constraint
ALTER TABLE dbo.persion
DROP CONSTRAINT PK_persionId;
GO
-- add new auto incremented field
ALTER TABLE dbo.persion
ADD pmid BIGINT IDENTITY;
GO
-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
GO
-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
is a better solution because you have control over the naming of the primary_key.
It's better than just using
ALTER TABLE Persion ADD PRIMARY KEY(persionId,Pname,PMID)
which yeilds randomized names and can cause problems when scripting out or comparing databases
If you add primary key constraint
ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONSTRAINT NAME> PRIMARY KEY <COLUMNNAME>
for example:
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
There is already an primary key in your table. You can't just add primary key,otherwise will cause error. Because there is one primary key for sql table.
First, you have to drop your old primary key.
MySQL:
ALTER TABLE Persion
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Persion
DROP CONSTRAINT 'constraint name';
You have to find the constraint name in your table. If you had given constraint name when you created table,you can easily use the constraint name(ex:PK_Persion).
Second,Add primary key.
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persion ADD PRIMARY KEY (PersionId,Pname,PMID);
or the better one below
ALTER TABLE Persion ADD CONSTRAINT PK_Persion PRIMARY KEY (PersionId,Pname,PMID);
This can set constraint name by developer. It's more easily to maintain the table.
I got a little confuse when i have looked all answers. So I research some document to find every detail. Hope this answer can help other SQL beginner.
Necromancing.
Just in case anybody has 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 (it is very possible it does not have the name you think it has...)
-- 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)
;
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
The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values and column cannot contain NULL Values.
-- DROP current primary key
ALTER TABLE tblPersons DROP CONSTRAINT <constraint_name>
Example:
ALTER TABLE tblPersons
DROP CONSTRAINT P_Id;
-- ALTER TABLE tblpersion
ALTER TABLE tblpersion add primary key (P_Id,LastName)
Please try this-
ALTER TABLE TABLE_NAME DROP INDEX `PRIMARY`, ADD PRIMARY KEY (COLUMN1, COLUMN2,..);
alter table[Person] add ID int primary key IDENTITY (1,1)
This will add primary key and indentity seed and populate the new column.
ALTER TABLE TABLE_NAME ADD PRIMARY KEY(`persionId`,`Pname`,`PMID`)
Try using this code:
ALTER TABLE `table name`
CHANGE COLUMN `column name` `column name` datatype NOT NULL,
ADD PRIMARY KEY (`column name`) ;
I am surprised after all these years the answers to the question had not hit 100% what the question poster was asking.
The problem presented by the poster is: He has an existing table with existing data and existing compound-key. Now the task he wants to accomplish is to execute a script to replace the existing compound-key with a new compound-key.
First concern: the first requirement for fields to be a key is NOT NULL and they together need to be unique, otherwise you will get error when running the alter statement.
For that matter, I think the following is the right answer for the future answer seekers who have the same issue.
-- test your existing data suitable for new key or not
select 1 from Person p group by PMid, PersonId, PName having COUNT(*) > 1
If @@ROWCOUNT > 0 Print 'Not Ok'
ELSE
BEGIN
-- delete old key/compound-key
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PK_Person]') )
BEGIN
ALTER TABLE Person DROP CONSTRAINT PK_Person;
END;
BEGIN
-- add new key/compound-key, fulfil the required non-null quality
ALTER TABLE [dbo].[Person] ALTER COLUMN PMid NUMERIC(5,0) NOT NULL; -- 'not null' is required
ALTER TABLE [dbo].[Person] ALTER COLUMN PersonId NUMERIC(5,0) NOT NULL; -- 'not null' is required
ALTER TABLE [dbo].[Person] ALTER COLUMN Pname varchar(32) NOT NULL; -- 'not null' is required
END
ALTER TABLE [dbo].[Person] ADD CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PMid] ASC,
[PersonId] ASC,
[Pname] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
END
© 2022 - 2024 — McMap. All rights reserved.
personIdin your table. This in turn means if you join from a transaction (many) type table to this table on this key alone you'll get duplicate records, leading to 'double counting' of transaction records. – Livengood