I have a table structured as such:
PrimaryKey int set as PK SomeForeignKey int set as FK... linked to some other table's PK SomeVarChar varchar SomeDate datetime When I right-click on Properties of my PK, I noticed that Identity is set to False, Identity Seed is set to 0 and Identity Icrement is set to 0.
However, I am unable to modify these properties. How can I change these values?
Here is a screen shot.
You change the table, not the constraint. Management Studio will take care of the other stuff for you (note that it has to drop the table and re-create it, so if the table is large, be prepared to grab a coffee and hurry up and wait).
In the properties pane, under "Identity Column", choose the correct column:
Click the Save icon (or Ctrl+S):
Note that you may also have to un-check the option Tools > Designers > Table and Database Designers > Prevent saving changes that require table re-creation.
Normally I would suggest making changes to schema using DDL instead of the hokey and bug-ridden GUI, but this is one rare case where the GUI actually requires less work than typing the requisite commands would. Changing the IDENTITY property is one thing DDL just hasn't caught up with, and the nonsense it has to do to work around it is ridiculous (in this case I added a new column, and scripted out the change required to make that the identity column instead - and you can't even see all the nonsense here, because of the non-resizable dialog):
Here is the actual script (not something you probably want to run very often, never mind come up with on your own):
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_foo ( ID int NOT NULL, [Foo INT] int NOT NULL IDENTITY (1, 1) ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_foo SET (LOCK_ESCALATION = TABLE) GO SET IDENTITY_INSERT dbo.Tmp_foo OFF GO IF EXISTS(SELECT * FROM dbo.foo) EXEC('INSERT INTO dbo.Tmp_foo (ID) SELECT ID FROM dbo.foo WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.foo GO EXECUTE sp_rename N'dbo.Tmp_foo', N'foo', 'OBJECT' GO ALTER TABLE dbo.foo ADD CONSTRAINT PK__foo__3214EC274CF5691D PRIMARY KEY CLUSTERED (ID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO COMMIT If you want to change this property in Management Studio, you right click on the table, select Design, and then select the primary key column, and see its properties in the bottom, where you can open the "Identity Specification" section. There, you can edit the setting "IsIdentity". Then save the changes.
