-- ========================================
-- Script SIMPLE d'ajout de contraintes UNIQUE
-- Pour tables avec peu de données (pas de suppression de doublons)
-- Date: 2025-10-10
-- ========================================

USE pra_geo;

-- Vérifier d'abord s'il y a des doublons
SELECT 'Vérification doublons ope_users...' as status;
SELECT fk_operation, fk_user, COUNT(*) as count
FROM ope_users
GROUP BY fk_operation, fk_user
HAVING count > 1;

SELECT 'Vérification doublons ope_users_sectors...' as status;
SELECT fk_operation, fk_user, fk_sector, COUNT(*) as count
FROM ope_users_sectors
GROUP BY fk_operation, fk_user, fk_sector
HAVING count > 1;

-- Ajouter les contraintes UNIQUE directement
-- Si des doublons existent, MySQL retournera une erreur explicite
ALTER TABLE ope_users
ADD UNIQUE KEY `idx_operation_user` (`fk_operation`, `fk_user`);

ALTER TABLE ope_users_sectors
ADD UNIQUE KEY `idx_operation_user_sector` (`fk_operation`, `fk_user`, `fk_sector`);

-- Vérification
SHOW INDEX FROM ope_users WHERE Key_name = 'idx_operation_user';
SHOW INDEX FROM ope_users_sectors WHERE Key_name = 'idx_operation_user_sector';

SELECT 'TERMINÉ ✓' as status;
