Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

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


0
goodk
Asked:
goodk
1 Solution
 
ste5anSenior 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now