Solved

changing table design, for the table which has data

Posted on 2011-03-17
2
502 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 33

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…

786 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