Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

changing table design, for the table which has data

Posted on 2011-03-17
2
Medium Priority
?
574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 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