changing table design, for the table which has data

I am using ms sql8 express edition and using ms sql server management studio
I wanted to change the primary key, but I cannot find a way to do it.

Do we save the copy of the data?  Or is there a better method?

All I wanted to do was move the primary key from PersonID to Username and then in the later steps delete the PersonID

Here is my code, not sure how to modify it so the data is safe.  Please help - thanks

USE [SA02012011]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PersonID_Department]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [FK_PersonID_Department]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PersonID_Titles]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [FK_PersonID_Titles]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CC$PersonID$Company$disallow_zero_length]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [SSMA_CC$PersonID$Company$disallow_zero_length]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CC$PersonID$MailUnit$disallow_zero_length]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [SSMA_CC$PersonID$MailUnit$disallow_zero_length]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CC$PersonID$PhoneInternal$disallow_zero_length]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [SSMA_CC$PersonID$PhoneInternal$disallow_zero_length]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CC$PersonID$SectionTeam$disallow_zero_length]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [SSMA_CC$PersonID$SectionTeam$disallow_zero_length]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CC$PersonID$Trade$disallow_zero_length]') AND parent_object_id = OBJECT_ID(N'[dbo].[PersonID]'))
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [SSMA_CC$PersonID$Trade$disallow_zero_length]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Person__6D9742D9]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Person__6D9742D9]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Compan__6E8B6712]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Compan__6E8B6712]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Depart__6F7F8B4B]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Depart__6F7F8B4B]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Sectio__7073AF84]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Sectio__7073AF84]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Contac__7167D3BD]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Contac__7167D3BD]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__passwo__725BF7F6]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__passwo__725BF7F6]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Title__73501C2F]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Title__73501C2F]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__DateEn__74444068]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__DateEn__74444068]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__HourRa__753864A1]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__HourRa__753864A1]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PersonID__Inacti__762C88DA]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PersonID] DROP CONSTRAINT [DF__PersonID__Inacti__762C88DA]
END

GO

USE [SA02012011]
GO

/****** Object:  Table [dbo].[PersonID]    Script Date: 03/17/2011 22:28:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PersonID]') AND type in (N'U'))
DROP TABLE [dbo].[PersonID]
GO

USE [SA02012011]
GO

/****** Object:  Table [dbo].[PersonID]    Script Date: 03/17/2011 22:28:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PersonID](
      [PersonID] [int] IDENTITY(1,1) NOT NULL,
      [Person] [nvarchar](100) NULL,
      [Company] [nvarchar](50) NULL,
      [PersonNotes] [nvarchar](max) NULL,
      [Trade] [nvarchar](50) NULL,
      [Supervisor] [nvarchar](60) NULL,
      [UserName] [nvarchar](50) NULL,
      [Department] [nvarchar](50) NULL,
      [Section] [nvarchar](50) NULL,
      [ContactType] [nvarchar](50) NULL,
      [passwordID] [nvarchar](50) NULL,
      [LoginType] [nvarchar](50) NULL,
      [eMail] [nvarchar](50) NULL,
      [Title] [nvarchar](50) NULL,
      [Address] [nvarchar](255) NULL,
      [Address_Shipping] [nvarchar](255) NULL,
      [HomePhone] [nvarchar](50) NULL,
      [OfficePhone] [nvarchar](50) NULL,
      [Fax] [nvarchar](50) NULL,
      [Pager] [nvarchar](50) NULL,
      [eMailPager] [nvarchar](50) NULL,
      [Cellular] [nvarchar](50) NULL,
      [OtherPhones] [nvarchar](50) NULL,
      [Misc] [nvarchar](50) NULL,
      [DateEntry] [datetime] NULL,
      [HourRate] [float] NULL,
      [OnCallPhone] [nvarchar](50) NULL,
      [xPictureSmall] [nvarchar](100) NULL,
      [xPictureLarge] [nvarchar](100) NULL,
      [SectionTeam] [nvarchar](50) NULL,
      [MailUnit] [nvarchar](50) NULL,
      [PhoneInternal] [nvarchar](50) NULL,
      [Inactive] [bit] NULL,
      [SSMA_TimeStamp] [timestamp] NOT NULL,
      [SSNumber] [nchar](50) NULL,
      [FedIDNumber] [nchar](50) NULL,
 CONSTRAINT [PersonID$PrimaryKey] PRIMARY KEY CLUSTERED
(
      [PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [FK_PersonID_Department] FOREIGN KEY([Department])
REFERENCES [dbo].[Department] ([Department])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [FK_PersonID_Department]
GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [FK_PersonID_Titles] FOREIGN KEY([Title])
REFERENCES [dbo].[Titles] ([Title])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [FK_PersonID_Titles]
GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [SSMA_CC$PersonID$Company$disallow_zero_length] CHECK  ((len([Company])>(0)))
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [SSMA_CC$PersonID$Company$disallow_zero_length]
GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [SSMA_CC$PersonID$MailUnit$disallow_zero_length] CHECK  ((len([MailUnit])>(0)))
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [SSMA_CC$PersonID$MailUnit$disallow_zero_length]
GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [SSMA_CC$PersonID$PhoneInternal$disallow_zero_length] CHECK  ((len([PhoneInternal])>(0)))
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [SSMA_CC$PersonID$PhoneInternal$disallow_zero_length]
GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [SSMA_CC$PersonID$SectionTeam$disallow_zero_length] CHECK  ((len([SectionTeam])>(0)))
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [SSMA_CC$PersonID$SectionTeam$disallow_zero_length]
GO

ALTER TABLE [dbo].[PersonID]  WITH CHECK ADD  CONSTRAINT [SSMA_CC$PersonID$Trade$disallow_zero_length] CHECK  ((len([Trade])>(0)))
GO

ALTER TABLE [dbo].[PersonID] CHECK CONSTRAINT [SSMA_CC$PersonID$Trade$disallow_zero_length]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('Unknown') FOR [Person]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('Unknown') FOR [Company]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('Unknown') FOR [Department]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('Unknown') FOR [Section]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('Unknown') FOR [ContactType]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('temp') FOR [passwordID]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ('Unknown') FOR [Title]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT (getdate()) FOR [DateEntry]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ((50)) FOR [HourRate]
GO

ALTER TABLE [dbo].[PersonID] ADD  DEFAULT ((0)) FOR [Inactive]
GO


goodkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ste5anConnect With a Mentor Senior DeveloperCommented:
hi,

you can only change the primary key, when no foreign keys references it in other tables. You can check this by running this SQL:

SELECT  OBJECT_NAME(parent_object_id) AS DefinedInTable ,
        OBJECT_NAME(referenced_object_id) AS ReferenceTable ,
        name AS ForeignKeyName
FROM    sys.foreign_keys
WHERE   OBJECT_NAME(parent_object_id) = 'PersonID' ;

Open in new window


When this is the case then you can simply run this against your existing table:

ALTER TABLE [dbo].[PersonID]
DROP CONSTRAINT [PersonID$PrimaryKey] ;

UPDATE  [dbo].[PersonID]
SET     [UserName] = [PersonID]
WHERE   [UserName] IS NULL ;

ALTER TABLE [dbo].[PersonID]
ALTER COLUMN [UserName] NVARCHAR(50) NOT NULL ;

ALTER TABLE [dbo].[PersonID]
ADD CONSTRAINT [PersonID$PrimaryKey] PRIMARY KEY ([UserName]) ;

Open in new window


btw, why do you want to do that? This will have some impact on performance and storage size. It is also not recommendable from a database design view, even when it is fully normalized.

mfG
--> stefan <--

P.S. Always make a backup of your entire database before testing things like this.
0
 
goodkAuthor Commented:
you know I will listen to your advice and will not change the field - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.