SQL : Savoir si on peut supprimer une ligne

Avant de savoir si l'on peut supprimer une ligne dans une base de données, il est bon de se poser la question : Est-elle référencée dans d'autres tables cette ligne ?

Pour se faire il suffit de suivre à l'envers les contraintes de foreign key de la base de données

voici une procédure utile :

CREATE PROCEDURE dbo.can_delete
  @table VARCHAR(100), @Id INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Command VARCHAR(MAX) 

  SELECT @Command = ISNULL(@Command + ' UNION ALL ', '') + ' SELECT ''' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name  +
    ''' AS [table] WHERE EXISTS(SELECT * FROM ' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name
    + ' WHERE ' + col.name + ' = ' + CAST(@Id AS VARCHAR) + ')' 
  FROM sys.foreign_key_columns fkc
  INNER JOIN sys.tables tbl ON fkc.parent_object_id = tbl.object_id
  INNER JOIN sys.columns col ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
  WHERE OBJECT_NAME(fkc.referenced_object_id) = @table

  EXECUTE (@Command);
END


A Noter dans cette procédure :
On créer une variable @commande qui va contenir une requête SQL construite dynamiquement et qui renvéra le résultat. Noter que le paramètre @command de la commande EXECUTE est entre parenthèses.

La requête fait appel à la table système de SQL SERVER ; sys.foreign_key_column qui maintient les Foreign Keys de la base de données

ATTENTION : Cette procédure ne fonctionne que si la clé primaire de la table concernée, n'est formé que d'une seule colonne (en général un entier auto incrémenté)

ServiceStack DataAnotation

ServiceStack est une librairie fort utile que je ne presente pas ici...
Petit mémo de la partie DataAnotation qui permet de faire un mapping Relationnel / Objet très simplement.


L'attribut :  [Alias("")] : Permet de mapper une table (s'il est mis sur une classe) ou une colonne s'il est mis sur une propriété.
La propriété doit toujours être de la forme : public [type] [propriété] { get; set;}
Attention aux types mappés : n'utiliser que les types valeur de base (int, double, decimal (pour les money  Sql Server), string, bool).
Vous pouvez utilisez les types nullables (int?, double?, decimal?, string, bool?)


L'attribut : [Ignore] : Permet d'ajouter des propriétés à l'objet sans les mapper à une colonne.
Exemple pour les enum je câble souvent une propriété :

public enum ValeursPossible
{
   val1 = 1,
   val2 = 2,
  ....
}

[Alias("maColonne")]
public int MonEnumInt {get; set;}

[Ignore]
public ValeursPossible MonEnum
{
   get
   {
      return (ValeurPossible)this.MonEnumInt;
   }
   set
  {
     this.MonEnumInt = (int)value;
  }
}

L'attribut : [Autoincr] est très important pour la propriété qui mappe une colonne auto-incrémentée de Sql Server : A ne pas oublier !


Enfin : Très utile !!
L'attribut : [Schema("xxx")] permet de mapper des tables qui ne sont pas dans le schéma par défaut (DBO pour Sql Server).

SQL Server : Indexer le colonnes de foreign key systématiquement

Une pratique qui permet d'obtenir un bon premier résultat lors de l’indexation d'une base de données SQL Server est de créer les indexes correspondants aux colonnes de clés étrangères.
En effet lorsque l'on fait des requêtes avec des jointures, d'un côté, la colonne de clé primaire auquel on accède est indexée par l'index de clé primaire, mais de l'autre, la colonne de clé étrangère elle, si elle n'est pas indexée, SQL Server doit parcourir l'ensemble des données.

Attention cette méthode est une première approche. Elle ne doit pas empêcher l'analyse des requêtes et structures des tables pour trouver les meilleurs index en fonction des contextes connus.

Voici ma requête qui me permet de trouver et créer les index absents lorsque la structure de la base a changé :

SELECT t.*
, 'IF (NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + t.parent_schema_name + '].[' + t.parent_table_name + ']'') AND name = N'''+ t.index_name + '''))
BEGIN
   CREATE NONCLUSTERED INDEX [' + t.index_name  + '] ON [' + t.parent_schema_name + '].[' + t.parent_table_name + '] 
(
[' + t.parent_column_name + '] 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]
END
' AS requete
FROM (
        SELECT fk.Name AS foreign_key_name, cfk.constraint_column_id,  pos.name AS parent_schema_name, po.name AS parent_table_name, cpo.name AS parent_column_name
         , oc.name AS referenced_table_name, cpc.name AS referenced_column_name
         , 'idx_' + LOWER(pos.name) + '_' + LOWER(po.name) + '_colonne_fk_' + LOWER(cpo.name) AS index_name
        FROM sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns cfk ON fk.object_id = cfk.constraint_object_id
        INNER JOIN sys.objects po ON cfk.parent_object_id = po.object_id
        INNER JOIN sys.columns cpo ON cpo.object_id = po.object_id AND cpo.column_id = cfk.parent_column_id
        INNER JOIN sys.schemas pos ON po.schema_id = pos.schema_id
        INNER JOIN sys.objects oc ON cfk.referenced_object_id = oc.object_id
        INNER JOIN sys.columns cpc ON cpc.object_id = oc.object_id AND cpc.column_id = cfk.referenced_column_id
     ) t
LEFT JOIN (SELECT ix.name AS index_name, tx.name AS table_name, sx.name AS schema_name, ix.object_id
FROM sys.indexes ix
INNER JOIN sys.tables tx ON ix.object_id = tx.object_id
           INNER JOIN sys.schemas sx ON tx.schema_id = sx.schema_id
) i ON i.schema_name = t.parent_schema_name
             AND i.table_name = t.parent_table_name
             AND i.index_name = t.index_name
WHERE i.object_id IS NULL

Le colonne "requete" une fois sélectionnées et exécutée crée les index manquants.
Ajuster le texte en fonction des besoins (surtout par rapport aux options de l'index créé)