asked on

Table Modification

I need help to modify this table. See my question below. Still very new to database design.

This is my original table.

CREATE TABLE [dbo].[PcatDocuments](
      [Program] [varchar](25) NOT NULL,
      [NewUsed] [char](1) NOT NULL,
      [CoverPage] [varchar](100) NULL,
      [EligibilityGuidelines] [varchar](100) NULL,
      [RateCreditEligibility] [varchar](100) NULL,
      [RateType] [char](1) NOT NULL,
      [State] [varchar](2) NULL
) ON [PRIMARY]

Question:

We need to change the primary key from a compose key (Program, NewUsed,RateType) to an ID. The primary key should be an Identify column. We will also need a unique constraint on Program, NewUsed, RateType, State.
Finally will we need a new table for State which will be foreign key to the state column in PcatDocument table. The state column in PcatDocument should be nullable.
This table has about 700 records, so you will need a script to save the data to a temp table, make the changes and then repopulate the table.

SOLUTION

QPR

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

deviprasadg

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Favorable

ASKER

I get confused creating the foreign key table, because it's going to reference the state column in PcatDocument table. Could you correct this please.

CREATE TABLE [dbo].[State](
[State] [varchar](2) NOT NULL
) ON [PRIMARY]

ASKER CERTIFIED SOLUTION

deviprasadg

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Favorable

ASKER

thank you very much