June 18, 2025 at 9:09 pm
I will have to test this next week, but will not have a chance before Friday. My question is, if I backup table data, drop a PK to add it back as an identity PK, will the foreign keys referencing the PK still be in tact? The new PK (now with identity) will keep the same column name and constraint name (obviously it would break if those changed). Thanks in advance. Steve
June 19, 2025 at 6:34 am
Why add an IDENTITY? You can use a SEQUENCE object, and set it as the default value for the field. Then you dont need to change anything else. You can also use the SEQ in the code to get the next value if you need it up front.
June 19, 2025 at 7:05 am
Consider a table like this
CREATE TABLE dbo.MyTable (
ID int NOT NULL PRIMARY KEY CLUSTERED
, DateAdded datetime NOT NULL DEFAULT GETDATE()
);
You are probably adding data in a manner similar to this
DECLARE @NextID int;
DECLARE @InsertedRecord table (ID int NOT NULL, DateAdded datetime NOT NULL);
SET @NextID = ISNULL((SELECT MAX(ID) FROM dbo.MyTable), 0) + 1;
INSERT INTO dbo.MyTable ( ID )
OUTPUT Inserted.ID, Inserted.DateAdded
INTO @InsertedRecord(ID, DateAdded)
VALUES ( @NextID );
SELECT * FROM @InsertedRecord;
GO
If you create a SEQ object
CREATE SEQUENCE dbo.MyTableID
AS int
START WITH 1
INCREMENT BY 1
CACHE 1000
NO CYCLE;
GO
ALTER TABLE dbo.MyTable ADD CONSTRAINT DF_MyTable_ID DEFAULT (NEXT VALUE FOR MyTableID) FOR ID;
GO
you have multiple options ...
DECLARE @NextID int;
DECLARE @InsertedRecord table (ID int NOT NULL, DateAdded datetime NOT NULL);
SET @NextID = NEXT VALUE FOR MyTableID;
INSERT INTO dbo.MyTable ( ID )
OUTPUT Inserted.ID, Inserted.DateAdded
INTO @InsertedRecord(ID, DateAdded)
VALUES ( @NextID );
SELECT * FROM @InsertedRecord;
GO
DECLARE @InsertedRecord table (ID int NOT NULL, DateAdded datetime NOT NULL);
INSERT INTO dbo.MyTable ( ID )
OUTPUT Inserted.ID, Inserted.DateAdded
INTO @InsertedRecord(ID, DateAdded)
VALUES ( DEFAULT );
SELECT * FROM @InsertedRecord;
GO
Even the original code still works, if you no longer wish to use the SEQ object.
June 19, 2025 at 7:17 am
In SQLServer you cannot drop a PK when it is being reference by FK(s). It will produce an error.
That being said, as long as you don't change the data type and length of the pk column, fk can be put back without an issue.
For data safety, this modification should be executed in a single transaction.
Also keep in mind, applications which currently insert data in your pk-table will have to be modified, because they are no longer allowed to specify the identity column in their insert statement.
If you chose to use a sequence to handle the existing columns value, you can use
ADD CONSTRAINT <name> DEFAULT NEXT VALUE FOR dbo.YourSequence FOR YourColumn.
However, since that is a default constraint, people inserting data can still specify that columns value ! ( potentially messing up your sequencers goal )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 19, 2025 at 1:59 pm
Primary Key and Identity are different "things". A PRIMARY KEY is a CONSTRAINT, where as IDENTITY is a property of a column/table. A PRIMARY KEY isn't an IDENTITY.
DROPing your PRIMARY KEY CONSTRAINT wouldn't allow you to recreate is as an IDENTITY as that is tied to the column itself, and you can't ALTER a column to be an IDENTITY; you can only create a new column which has the IDENTITY property, which also wouldn't help you, as the values would be applied arbitrarily.
If you had to implement an IDENTITY then you'd actually likely need to create a new table, with the PRIMARY KEY column defined as an IDENTITY and the INSERT the data from your existing table into the new table, with IDENTITY_INSERT enabled on the new table. Then you'd need to amend any FOREIGN KEY constraints pointing to the old table to point to the new table, DROP the old table, and rename the new one.
As such, Des' suggestion of using a SEQUENCE would likely be the least "invasive", as you're not not going to have to actually "play around" with the objects.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 20, 2025 at 5:28 pm
So of the answers here, although good info, don't really pertain to my topic.
My question was if I would have to drop the dependencies on the PK, and I don't. Because I am removing the data into a backup table, then recreating the table with the same column name on the PK, and the same constraint name on the PK, the dependent tables don't even know anything changed. I just got done testing it today.
The reason I asked is because my colleague did a similar method, and kept all the constraints in place (except the PK, of course that was being changed to Identity). He removed data into a backup table, drop PK and constraint name, re-add the PK with the same column name and constraint name this time with Identity (this is a company decision, not mine, I don't have other options like to use sequencing). Everything is working in his method, much to my surprise.
So in conclusion, I did a lot of extra hard work with my method. I thought all of the dependencies should be removed (and all constraints in the main table (meaning whatever table my script is currently changing), then Identity insert the data from the backup table, then rebuild all constraints dependencies and local constraints. I was worried his method of leaving the constraints in place would cause issues. Perhaps because the main table is empty is why it works without error? I know if the data was there it wouldn't work.
My whole career I've rebuilt tables from the ground up to make big changes, especially the PK, but it turns out I have not had to do that, at least with SQL server 2016 and beyond. He just removed and readded the PK column, rest of the table in tact, and dependencies in place as well. I believe even Microsoft documentation suggest to drop all constraints, then build them back again. Apparently not the case. I'm not arguing that it isn't "best practice" to drop all dependencies, but until I see any issues with my colleague's method of leaving the table in tact (and dropping the one PK column/constraint) I am going to stick with it.
June 23, 2025 at 6:48 am
You seem to have a process. I suggest you try it out on a test system to see if anything breaks.
There are many ways where things could break. If the new PK value is different to the old PK value then all the rows in dependant tables that referenced the old PK value will need to be updated to reflect the new PK value. Not doing this will mean the dependant rows are now linking to a different parent. Remember that in most systems, not all dependencies will have the luxury of a defined FK.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 23, 2025 at 7:03 am
I think what puzzles me the most is you seem to have a few years SQL experience but are not challenging the initial decision to change the column definition. You cite company-level policy.
For me, there is no such thing as a one-size-fits-all for existing databases. You can apply a standard to a new DB and with luck it might still be in place by the time it goes live. Mostly after 2 years have passed another standard will be in place.
For existing DBs everything has to be pragmatic and based on cost and business risk. The change you are proposing to me fails at being pragmatic, and looks to be high on cost and risk.
My advice is to challenge the requirement to do the change. Has anyone done a cost-benefit analysis or looked at the risk of data corruption.
If your company really is ruled by a technocrat 'my way or the highway' then look at getting on the highway. You need to build your skills and general savvy beyond doing stuff for the sake of doing it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 23, 2025 at 8:45 am
Interesting debate and technique. I tend to like looking for creative solutions that might not impact users. A new table, if this one isn't too active with changes, is a good idea. Can reduce impact, though there might be data cleanup from changes during the time when you make the change.
I dislike long transactions, so I always seek to minimize those. I might tend to do what you did, though I'd use a new table and then in a transaction do a few renames to flip things around.
June 24, 2025 at 6:02 pm
It's interesting that my co-worker was able to complete this by just dropping the PK and the PK constraint in the parent table, and it is working just fine. The foreign keys referencing the PK in the child tables did not have a problem.
My method, of dropping the entire parent table with the PK and constraint also worked, it just involved gathering all of the referencing tables information, which took some time. One table had 30 referencing tables! I dropped all of the their referencing foreign keys, after moving the data into a backup table, rebuilt the table and ALL of the referencing keys, did an identity insert and everything is fine. I believed my co-workers method might cause problems (not RE creating the foreign keys again) but they are working just fine as well. His took half the time. I learned something about foreign keys - they aren't as picky as I thought (provided the data is removed from the parent table and put back in in one transaction). Thanks all for your input!
June 26, 2025 at 10:08 am
I don't understand how a referenced PK constraint can be dropped without an error.
I just tried that, and even having disabled a referencing FK I get an error.
Does this come up all 0s?
SELECT
name,
is_disabled,
is_not_trusted
FROM sys.foreign_keys
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply