ADD the Lang COLUMN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Merchandising_Snippets' AND table_schema = 'dbo' AND column_name = 'Lang')
BEGIN
ALTER TABLE Merchandising_Snippets ADD Lang nvarchar(10)
END
GO
UPDATE Merchandising_Snippets SET Lang='en' WHERE Lang is null
GO
IF EXISTS (SELECT * FROM Information_schema.columns
WHERE table_name = 'Merchandising_Snippets'
AND column_name = 'Lang'
AND is_nullable = 'YES')
BEGIN
ALTER TABLE Merchandising_Snippets
ALTER COLUMN Lang nvarchar(10) not null
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Merchandising_Snippet_Resources' AND table_schema = 'dbo' AND column_name = 'Lang')
BEGIN
ALTER TABLE Merchandising_Snippet_Resources ADD Lang nvarchar(10)
END
GO
UPDATE Merchandising_Snippet_Resources SET Lang='en' WHERE Lang is null
GO
IF EXISTS (SELECT * FROM Information_schema.columns
WHERE table_name = 'Merchandising_Snippet_Resources'
AND column_name = 'Lang'
AND is_nullable = 'YES')
BEGIN
ALTER TABLE Merchandising_Snippet_Resources
ALTER COLUMN Lang nvarchar(10) not null
END
GO
--DELETE FOREIGN and PRIMARY KEYS
IF EXISTS (SELECT * FROM sys.foreign_keys
WHERE name='FK_Merchandising_Schedule_Merchandising_Snippets'
AND parent_object_id = OBJECT_ID(N'[dbo].[Merchandising_Schedule]'))
BEGIN
alter table dbo.Merchandising_Schedule drop constraint FK_Merchandising_Schedule_Merchandising_Snippets
END
GO
IF EXISTS (SELECT * FROM sys.indexes
WHERE name='PK_Merchandising_Snippet_Resources'
AND object_id = OBJECT_ID(N'[dbo].[Merchandising_Snippet_Resources]'))
BEGIN
alter table Merchandising_Snippet_Resources drop constraint PK_Merchandising_Snippet_Resources
END
GO
IF EXISTS (SELECT * FROM sys.foreign_keys
WHERE name='FK_Merchandising_Snippet_Resources_Merchandising_Snippets'
AND parent_object_id = OBJECT_ID(N'[dbo].[Merchandising_Snippet_Resources]'))
BEGIN
alter table Merchandising_Snippet_Resources drop constraint FK_Merchandising_Snippet_Resources_Merchandising_Snippets
END
GO
IF EXISTS (SELECT * FROM sys.indexes
WHERE name='PK_Merchandising_Snippets'
AND object_id = OBJECT_ID(N'[dbo].[Merchandising_Snippets]'))
BEGIN
alter table Merchandising_Snippets drop constraint PK_Merchandising_Snippets
END
GO
--CREATE THE PRIMARY and FOREIGN KEYS
ALTER TABLE [dbo].[Merchandising_Snippets]
ADD CONSTRAINT [PK_Merchandising_Snippets] PRIMARY KEY CLUSTERED
(
[SnippetID] ASC,
[Lang] ASC
)
ALTER TABLE [dbo].[Merchandising_Snippet_Resources]
ADD CONSTRAINT [PK_Merchandising_Snippet_Resources] PRIMARY KEY CLUSTERED
(
[SnippetID] ASC,
[ResourceID] ASC,
[Lang] ASC
)
ALTER TABLE [dbo].[Merchandising_Snippet_Resources] WITH CHECK ADD CONSTRAINT [FK_Merchandising_Snippet_Resources_Merchandising_Snippets]
FOREIGN KEY([SnippetID],[Lang]) REFERENCES [dbo].[Merchandising_Snippets]([SnippetID],[Lang])
--ADD BACK THE FK FROM Merchandising_Schedule?
--ALTER TABLE [dbo].[Merchandising_Schedule] WITH CHECK ADD CONSTRAINT [FK_Merchandising_Schedule_Merchandising_Snippets]
--FOREIGN KEY([SnippetID]) REFERENCES [dbo].[Merchandising_Schedule]([SnippetID])
GO
/*REP ADD ARTICLE Merchandising_Snippet_Resources*/
/*REP ADD ARTICLE Merchandising_Snippets*/