Skip to main content
Database Administrators

Return to Answer

added 286 characters in body
Source Link
McNets
McNets
  • 23.9k
  • 11
  • 50
  • 89

I've found a good article about how to drop and recreate all foreign keys, perhaps you could use it on this issue.

I've found a good article about how to drop and recreate all foreign keys, perhaps you could use it on this issue.

added 870 characters in body
Source Link
McNets
McNets
  • 23.9k
  • 11
  • 50
  • 89

EditingEven though this works, let me try to add a new table:

CREATE TABLE MyTable2
(
    Id int,
    MyTable_Id int NOT NULL,
    CONSTRAINT [PK_MyTable2] PRIMARY KEY (Id),
    CONSTRAINT [FK_MyTable2] FOREIGN KEY (Id) REFERENCES MyTable(Id)
);

Yes, there is a FOREIGN KEY that references Id field of MyTable. Now try to execute the script again and...


Msg 3725 Level 16 State 0 Line 25

The constraint 'PK_MyTable' is being referenced by table 'MyTable2', foreign key constraint 'FK_MyTable2'. Msg 3727 Level 16 State 0 Line 25 Could not drop constraint. See previous errors.

dbfiddle here

Yes, bad news again, to drop a PRIMARY KEY, you should DROP all FOREIGN KEYS that references this field before.

Editing...

Even though this works, let me try to add a new table:

CREATE TABLE MyTable2
(
    Id int,
    MyTable_Id int NOT NULL,
    CONSTRAINT [PK_MyTable2] PRIMARY KEY (Id),
    CONSTRAINT [FK_MyTable2] FOREIGN KEY (Id) REFERENCES MyTable(Id)
);

Yes, there is a FOREIGN KEY that references Id field of MyTable. Now try to execute the script again and...


Msg 3725 Level 16 State 0 Line 25

The constraint 'PK_MyTable' is being referenced by table 'MyTable2', foreign key constraint 'FK_MyTable2'. Msg 3727 Level 16 State 0 Line 25 Could not drop constraint. See previous errors.

dbfiddle here

Yes, bad news again, to drop a PRIMARY KEY, you should DROP all FOREIGN KEYS that references this field before.

Source Link
McNets
McNets
  • 23.9k
  • 11
  • 50
  • 89

There is a big problem that you have not considered:

  • As far as I know you cannot remove IDENTITY from a column.
  • Hence, you can only convert this column to int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0

Not good news, but you could try another approach:

  • Add a new column
  • Fill it with new values
  • Drop PRIMARY KEY constraint.
  • Drop ID column
  • Rename new column as Id
  • Add a PRIMARY KEY constraint on Id column
CREATE TABLE MyTable
(
    Id int IDENTITY(1,1),
    F1 int,
    CONSTRAINT [PK_MyTable] PRIMARY KEY (Id)
);

INSERT INTO MyTable VALUES (10),(20),(30);

--= Add a new column
ALTER TABLE MyTable ADD Id2 uniqueidentifier DEFAULT NewID() NOT NULL;

--= Fill new field with values
UPDATE MyTable 
SET    Id2 = NewID();

--= Drop PRIMARY KEY
ALTER TABLE MyTable DROP CONSTRAINT [PK_MyTable];

--= Drop Id column
ALTER TABLE MyTable DROP COLUMN Id;

--= Rename Id2 column as Id
EXEC sp_RENAME 'MyTable.Id2', 'Id', 'COLUMN';

--= Add PRIMARY KEY
ALTER TABLE MyTable ADD CONSTRAINT [PK_MyTable] PRIMARY KEY (Id);
    
SELECT * FROM myTable;
GO

F1 | Id                                  
-: | :-----------------------------------
30 | e430b87c-6fbf-43c3-a88c-00abe925c2ea
20 | 67e6d0ac-3bb8-4c29-b19b-501ebdc2acee
10 | 287117fb-0188-480f-be54-ef42cacc123a

Caution: Changing any part of an object name could break scripts and stored procedures.

dbfiddle here

Editing...