Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Replacing primary key

    Hi all the client am working with wants to get rid of thier primarykey which is on two tables and they are natural keys, they want them to be replaced with a number they dont want to enter in their own keys. As it stands now their primary key for the old database is the first and lastname together and they want rid of that. I am unsure a to how to do that because I already have the relationships established and i want this to be a smoth and easy process. How would I do that?? I already have 3 child tables under them
    people_tbl

    Code:
    CREATE TABLE [dbo].[People_tbl](
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[StudtID] [nvarchar](50) NULL,
    	[Date of Referral] [nvarchar](50) NULL,
    	[FirstName] [nvarchar](50) NULL,
    	[LastName] [nvarchar](50) NULL,
    	[SS#] [nvarchar](50) NULL,
    	[DOB] [datetime] NULL,
    	[____] [nvarchar](50) NULL,
    	[Telephone #] [nvarchar](50) NULL,
    	[Message #] [nvarchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[Zip] [nvarchar](50) NULL,
    	[E Mail Address] [nvarchar](50) NULL CONSTRAINT [DF_People_tbl_E Mail Address]  DEFAULT (N'Email Address'),
    	[Tribal Affiliation] [nvarchar](50) NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANF staff making Referral] [nvarchar](50) NULL,
    	[ReferralLocation] [nvarchar](50) NULL,
    	[RegistrationDate] [datetime] NULL,
    	[Type Participant] [nvarchar](50) NULL,
    	[ScannedDocuments] [ntext] NULL CONSTRAINT [DF_People_tbl_ScannedDocuments]  DEFAULT (N'ScannedDocuments'),
    	[EnrollmentStatus] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [int] NULL,
    	[WPH] [nvarchar](50) NULL,
    	[ReferralStatus] [nvarchar](50) NULL,
    	[ServiceArea] [nvarchar](50) NULL,
    	[Month] [nvarchar](50) NULL,
    	[Week] [nvarchar](50) NULL,
    	[UpdateID] [int] NULL,
     CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
    (
    	[Parent ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  2. #2
    Join Date
    Oct 2003
    Posts
    60
    add the autoinc field.

    add the a field to each of the child tables to hold the new PK value.

    update the value of the new column in the child field

    drop old pk / create new pk on the peeps table.

    drop the existing fk's and create the new one based on the new autoinc field.

    then change all your code to work with the new structure.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what's in the ____ column? that's so neat!

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I think that was a mistake

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by jezemine
    what's in the ____ column? that's so neat!
    I think that's how they separated personal info fields from phones and addresses fields, in other words improved readability of the table definition
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by rdjabarov
    I think that's how they separated personal info fields from phones and addresses fields, in other words improved readability of the table definition
    And for that "____" they use an NVARCHAR(50), because that's their default data type.
    Hmm. Somehow reminds me of the the article Top 10 Things I Hate About SQL Server Item #2.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •