USE EnVisage
GO

;WITH duplicates AS
(
	SELECT	Id	,
			ROW_NUMBER() OVER (PARTITION BY TeamId, ScenarioId ORDER BY Allocation DESC) AS rn
	FROM	dbo.Team2Scenario
) DELETE dbo.Team2Scenario WHERE Id IN
(
	SELECT	Id
	FROM	duplicates
	WHERE	rn > 1
)

IF NOT EXISTS 
(
	SELECT	1 
	FROM	sys.objects 
	WHERE	name = 'UQ_Team2Scenario'
)
ALTER TABLE Team2Scenario ADD CONSTRAINT UQ_Team2Scenario UNIQUE (TeamId, ScenarioId)