USE [JunkDB];
go

--DROP TABLE [dbo].[TaxInformation]
--DROP TABLE [dbo].[Employees]

CREATE TABLE [dbo].[Employees]
(   EmployeeID      int             identity
                    CONSTRAINT [Employees_EmployeeID_PK]
                        PRIMARY KEY CLUSTERED,
    LastName        nvarchar(128)   NOT NULL,
    FirstName     nvarchar(128)   NOT NULL,
    NationalID      char(9)         NOT NULL
                    CONSTRAINT [Employees_NationalID_UK]
                        UNIQUE NONCLUSTERED,
)
GO

EXEC [sp_help] '[dbo].[Employees]';
go

INSERT [dbo].[Employees] 
    VALUES (N'Tripp', N'Kimberly', '123456789')
    , (N'Randal', N'Paul', '987654321');
go

SELECT * FROM [dbo].[Employees];
go

CREATE TABLE [dbo].[TaxInformation]
(   [NationalID]    char(9) 
                    CONSTRAINT [TaxInformation_NationalID_PK]
                        PRIMARY KEY CLUSTERED
                    , CONSTRAINT [TaxInformation_NationalID_FK]
                        FOREIGN KEY ([NationalID])
                        REFERENCES [dbo].[Employees]([NationalID]),
    Dependants      tinyint   NOT NULL
                    CONSTRAINT [TaxInformation_Dependants_DF]
                        DEFAULT 0
    -- etc...
);
go

INSERT [dbo].[TaxInformation]
    VALUES ('987654321', 2)
    , ('123456789', 0);
go

SELECT * FROM [dbo].[TaxInformation];
go

-- OK, everything's setup. Now you decide you need the following new index:
-- DROP INDEX [dbo].[Employees].[QueryTuningIndex]

CREATE UNIQUE INDEX [QueryTuningIndex]
ON [dbo].[Employees] ([NationalID]) 
INCLUDE ([LastName], [FirstName]);
go

EXEC [sp_SQLskills_helpindex] '[dbo].[Employees]';
go

ALTER TABLE [dbo].[Employees] 
DROP CONSTRAINT [Employees_NationalID_UK];
go

-- Sadly, that doesn't work (even though it SHOULD!)
-- And, it implies that you need to reference a constraint
-- when you don't...

ALTER TABLE [dbo].[TaxInformation]
DROP CONSTRAINT [TaxInformation_NationalID_FK];
go

ALTER TABLE [dbo].[Employees] 
DROP CONSTRAINT [Employees_NationalID_UK];
go -- now this works

ALTER TABLE [dbo].[TaxInformation]
ADD CONSTRAINT [TaxInformation_NationalID_FK]
    FOREIGN KEY ([NationalID])
    REFERENCES [dbo].[Employees]([NationalID]);
go -- no errors! this is now using the UNIQUE index for referential integrity

EXEC [sp_SQLskills_helpindex] '[dbo].[Employees]';
go

-- Now, this index cannot be dropped (unless the FK is removed first)
DROP INDEX [dbo].[Employees].[QueryTuningIndex]

--Msg 3723, Level 16, State 6, Line 89
--An explicit DROP INDEX is not allowed on index 'dbo.Employees.QueryTuningIndex'. It is being used for FOREIGN KEY constraint enforcement.
