Solved

changing table design, for the table which has data

Posted on 2011-03-17
2
491 Views
Last Modified: 2012-05-11
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
Comment
Question by:goodk
2 Comments
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 35163905
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
 

Author Closing Comment

by:goodk
ID: 35167718
you know I will listen to your advice and will not change the field - thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now