How to Alter a Primary Key Constraint Using SQL Syntax

How can I alter a primary key constraint using SQL syntax?

Yes. The only way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

Alter Primary key constraint

The only and one way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

Query to Alter Primary Key to any column with keyword

If you want to define existing autonumber fields as primary key, consider:

ALTER TABLE tablename ADD CONSTRAINT fieldname PRIMARY KEY(fieldname)

However, field cannot already be set with an index and table cannot already have a primary key. Again, use ALTER TABLE to remove index.

ALTER TABLE tablename DROP CONSTRAINT indexname

Will have to run this SQL for each table that must be modified. If this is a one-time only requirement (why would it not be?), probably just as fast to open each table and manually modify design.

MS documentation https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/alter-table-statement-microsoft-access-sql

The alternative is VBA using TableDefs to modify table structure. Search web for examples.

How to alter primary key column in SQL Server using T-SQL script?

You can accomplish this using T-SQL. Let's say your table name is branch.

So first we will get primary key constraint name using following query:

Query

SELECT name
FROM sys.key_constraints
WHERE [type] = 'PK'
  AND [parent_object_id] = Object_id('dbo.branch');

Result

'PK_Branch'

Now we use that result in another query to drop primary key. This query will remove primary key from branch table.

Query

ALTER TABLE dbo.branch
DROP CONSTRAINT PK_Branch

Add multiple primary keys to a table

I find one solution.

select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='avgEnt'
and type_desc LIKE '%CONSTRAINT'

Find constraint of table PK_avgent

After drop primary on table

-- drop current primary key constraint
ALTER TABLE dbo.avgEnt
DROP CONSTRAINT PK_avgent;
GO

After add two column

-- create new primary key constraint
ALTER TABLE dbo.avgEnt
ADD CONSTRAINT PK_avgent PRIMARY KEY NONCLUSTERED (SrNo, TrnDate);
GO

it is work form me.

Remove primary key(s) - Foreign key constraint is incorrectly formed

The index is still needed for the existing FK constraints.

Adding the following index (first) should satisfy that requirement:

CREATE INDEX xxx ON employee (User, Company);

Test case

SQL Server Change Primary Key Data Type

You can't change primary key column,unless you drop it..Any operations to change its data type will lead to below error..

The object 'XXXX' is dependent on column 'XXXX'.

Only option is to

1.Drop primary key

2.change data type

3.recreate primary key

ALTER TABLE t1  
DROP CONSTRAINT PK__t1__3213E83F88CF144D;   
GO  

alter table t1 
alter column id varchar(10) not null

alter table t1 add primary key (id)

From 2012,there is a clause called (DROP_EXISTING = ON) which makes things simple ,by dropping the clustered index at final stage and also keeping old index available for all operations..But in your case,this clause won't work..

So i recommend

1.create new table with desired schema and indexes,with different name

2.insert data from old table to new table

3.finally at the time of switch ,insert data that got accumulated

4.Rename the table to old table name

This way you might have less downtime

---

Related Topics