0

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned expects every table to have a primary key. So I have to convert every PK and probably every FK index to a key.

When I tried one key at a time, I found that before converting a PK I have to find and drop ALL FK indexes.

Is there a good way to make an MS SQL script to convert the indexes as a batch? Or do I have to do the manual approach and build a script to drop each index, build all the PKs, then build all the FKs?

(I'm running MS SQL Server 2012.)

ETA the script I tried and its errors.

/****** Object:  Index [FK_PROJECT_ORGANIZATION]    Script Date: 7/16/2014 10:07:13 AM ******/
DROP INDEX [FK_PROJECT_ORGANIZATION] ON [dbo].[PROJECT]
GO

/****** Object:  Index [PK_ORGANIZATION]    Script Date: 7/16/2014 9:23:54 AM ******/
DROP INDEX [PK_ORGANIZATION] ON [dbo].[ORGANIZATION] WITH ( ONLINE = OFF )
GO

ALTER TABLE [dbo].[ORGANIZATION] ADD  CONSTRAINT [PK_ORGANIZATION] PRIMARY KEY CLUSTERED 
(
    [organization_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object:  Index [FK_PROJECT_ORGANIZATION]    Script Date: 7/16/2014 10:07:13 AM ******/
--Re-create the index as a foreign key
ALTER TABLE [dbo].[PROJECT]  WITH CHECK ADD  CONSTRAINT [FK_PROJECT_ORGANIZATION] FOREIGN KEY([organization_id])
REFERENCES [dbo].[ORGANIZATION] ([organization_id])
GO

ALTER TABLE [dbo].[PROJECT] CHECK CONSTRAINT [FK_PROJECT_ORGANIZATION]
GO

The error messages:

Msg 3723, Level 16, State 6, Line 3
An explicit DROP INDEX is not allowed on index 'dbo.ORGANIZATION.PK_ORGANIZATION'. It is being used for FOREIGN KEY constraint enforcement.
Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'PK_ORGANIZATION' already exists on table 'dbo.ORGANIZATION'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

ETA2: The CREATE TABLE script: (ETA3 - the script generated from the legacy database.)

/****** Object:  Table [dbo].[ORGANIZATION]    Script Date: 7/16/2014 12:22:47 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ORGANIZATION](
    [organization_id] [int] IDENTITY(1,1) NOT NULL,
    [user_name] [varchar](50) NULL,
    [user_phone] [varchar](30) NULL,
    [user_email_address] [varchar](50) NULL,
    [user_password] [varchar](50) NULL,
    [org_type] [varchar](20) NULL,
    [status] [varchar](20) NULL,
    [company] [varchar](100) NULL,
    [address_1] [varchar](40) NULL,
    [address_2] [varchar](40) NULL,
    [address_3] [varchar](40) NULL,
    [city] [varchar](40) NULL,
    [state_code] [varchar](10) NULL,
    [postal_code] [varchar](25) NULL,
    [business_poc_name] [varchar](50) NULL,
    [business_poc_title] [varchar](50) NULL,
    [business_poc_phone] [varchar](30) NULL,
    [business_poc_cell] [varchar](30) NULL,
    [business_poc_email_address] [varchar](50) NULL,
    [business_poc_city] [varchar](40) NULL,
    [business_poc_state_code] [varchar](10) NULL,
    [url] [varchar](50) NULL,
    [datetime_created] [smalldatetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Index [PK_ORGANIZATION]    Script Date: 7/16/2014 12:22:47 PM ******/
CREATE UNIQUE CLUSTERED INDEX [PK_ORGANIZATION] ON [dbo].[ORGANIZATION]
(
    [organization_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_ORGANIZATION_company]    Script Date: 7/16/2014 12:22:47 PM ******/
CREATE NONCLUSTERED INDEX [IX_ORGANIZATION_company] ON [dbo].[ORGANIZATION]
(
    [company] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_ORGANIZATION_organization_id_org_type]    Script Date: 7/16/2014 12:22:47 PM ******/
CREATE NONCLUSTERED INDEX [IX_ORGANIZATION_organization_id_org_type] ON [dbo].[ORGANIZATION]
(
    [organization_id] ASC,
    [org_type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_ORGANIZATION_user_email_address]    Script Date: 7/16/2014 12:22:47 PM ******/
CREATE NONCLUSTERED INDEX [IX_ORGANIZATION_user_email_address] ON [dbo].[ORGANIZATION]
(
    [user_email_address] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_ORGANIZATION_user_name]    Script Date: 7/16/2014 12:22:47 PM ******/
CREATE NONCLUSTERED INDEX [IX_ORGANIZATION_user_name] ON [dbo].[ORGANIZATION]
(
    [user_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

closed as off-topic by ypercubeᵀᴹ, Max Vernon, Mark Storey-Smith, Paul White, Jon Seigel Jul 17 '14 at 1:41

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, Max Vernon, Mark Storey-Smith, Paul White, Jon Seigel
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What do you mean with "all the primary and foreign keys were created as indexes" Are there no primary keys but only (unique) indexes? Are there no foreign key constraints but only indexes on the columns (that should have FK constraints)? The is no such thing as an "FK index". – ypercubeᵀᴹ Jul 16 '14 at 15:18
  • I suggest you add a sample of one table you (the legacy) database have and the command you have tried. – ypercubeᵀᴹ Jul 16 '14 at 15:20
  • 2
    You need to make sure "script indexes", "script foreign keys" and "script primary keys" are all true in Tools > Options > SQL Server Object Explorer > Scripting. And no, while identity property and primary key are often use together, there is no direct correlation there. You can have an identity column that is not a PK (and not even unique), and you can obviously have a PK that is not an identity column. – Aaron Bertrand Jul 16 '14 at 15:49
  • 1
    Some ideas for starting on this here, here, here... – Aaron Bertrand Jul 16 '14 at 15:56
  • 1
    I'm okay with closing the question as too localized. – Codes with Hammer Jul 16 '14 at 17:24
4

If you go into your SSMS options table and look at the scripting options for object explorer do you see foreign keys and primary keys enabled? That's pretty much the only thing I can think of that may be holding you back.

enter image description here

  • Yes, those are set to true. – Codes with Hammer Jul 16 '14 at 15:55
  • @Codes Yet your CREATE TABLE script in the question doesn't list any indexes, primary keys or foreign keys. Did you not copy it all? Did you set these options to true AFTER generating it? – Aaron Bertrand Jul 16 '14 at 15:56
  • @CodeswithHammer have to rebooted SSMS since enabling these options? – Zane Jul 16 '14 at 15:57
  • I had enabled those options quite some time ago, and rebooted the whole system since then. Thanks for suggesting the solution anyway. – Codes with Hammer Jul 16 '14 at 16:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.