none
Vincolo su Periodo RRS feed

  • Domanda

  • Salve a tutti,

    Ho una tabella dove tra l'altro, ho due campi di tipo data PeridoDa e PeriodoA che mi indicano inizio e fine di un periodo.
    Dovrei aggiungere un vincolo di univocità su questo periodo e fin qui il database mi mette a disposizione gli indici univoci ma dovrei anche garantire un vincolo per il quale due periodi in due record differenti non si intersechino.

    Come implementereste (e anche dove) questo vincolo?
    Ciao e grazie

    • Modificato G Luca mercoledì 29 settembre 2010 15:54 Errore di digitazione
    • Spostato Mila Daniel Ovidiu giovedì 30 settembre 2010 07:48 sql (Da:Microsoft Visual C# Forum)
    mercoledì 29 settembre 2010 15:47

Risposte

  • Ho una tabella dove tra l'altro, ho due campi di tipo data PeridoDa e PeriodoA che mi indicano inizio e fine di un periodo.
    Dovrei aggiungere un vincolo di univocità su questo periodo e fin qui il database mi mette a disposizione gli indici univoci ma dovrei anche garantire un vincolo per il quale due periodi in due record differenti non si intersechino.

    Come implementereste (e anche dove) questo vincolo?


    Ciao Luca,

    I DBMS offrono i constraint UNIQUE o PRIMARY KEY per definirire un vincolo di univocità, mentre gli indici sono strutture atte a garantire elevate prestazioni nell'accesso ai dati. E' poi vero che dal punto di vista fisico un constraint UNIQUE o una PK vengono implementati mediante un indice unique, ma questo potrebbe tranquillamente cambiare in futuro.

    Ora, bisognerebbe capire dettagliatamente cosa intendi con "intersecazione" dei periodi. Prova a postare un esempio completo con la struttura semplificata della tabella (CREATE TABLE), alcune righe di prova (INSERT INTO) ed un ulteriore comando di INSERT che dovrebbe fallire.

    Ciao!


    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.microsoft.com/Forums/it-IT/sqlserverit
    giovedì 30 settembre 2010 09:05
    Moderatore
  • Ciao,

    Il penultimo dovrebbe fallire perchè interseca con il primo
    L'ultimo dovrebbe fallire perchè interseca con i primi due
     


    Ciao Luca,

    ti propongo una soluzione che utilizza una funzione e un vincolo check a livello di tabella.

    Use tempdb
    Go
    
    -- Creo la tabella
    Create Table dbo.periodi
    (Id Int Identity Primary Key Clustered,
     PeriodoDa Datetime not null,
     PeriodoA Datetime not null,
     Constraint Ck_Periodi Check(PeriodoDa!>PeriodoA)
     )
     Go
     
     -- Creo la funzione: 
     -- Ritorna 1 se il periodo che viene passato 
     -- si sovrappone ai dati già esistenti 
     
     Create Function dbo.ufn_CheckIsPeriodiSovrapposti
     (@Id Int,
     @PeriodoDa Datetime,
     @PeriodoA Datetime
     )
     Returns Bit As
     Begin
     
      Declare @Retvalue Bit 
      
      Select Top 1 @Retvalue = 1
      From dbo.periodi 
      Where Id!=@Id 
      And Not (PeriodoDa>@PeriodoA Or PeriodoA<@PeriodoDa)
      Order By PeriodoDa
      
      Return IsNull(@Retvalue,0)
      
     end
     Go
     
     -- Creo il vincolo sulla tabella
     Alter Table dbo.Periodi
     Add Constraint Ck_PeriodiSovrapposti Check (dbo.ufn_CheckIsPeriodiSovrapposti(Id,PeriodoDa,PeriodoA)=0)
     Go
     
     
     --Inserisco alcuni periodi
     Insert Into dbo.periodi (PeriodoDa,PeriodoA)
     Values ('20100301','20100310'),
      ('20100316','20100320'),
      ('20100401','20100410'),
      ('20100416','20100420')
     
    
    -- Select dei dati
    Select * From dbo.periodi
     
     -- Inserisco un periodo che si sovrappone ad altri
     Insert Into dbo.periodi (PeriodoDa,PeriodoA)
     Values ('20100305','20100307')
    
     
    -- Pulizia
     Drop Table dbo.periodi
     Drop function dbo.ufn_CheckIsPeriodiSovrapposti
    
    /* Risultato
     
    Id   PeriodoDa    PeriodoA
    ----------- ----------------------- -----------------------
    1   2010-03-01 00:00:00.000 2010-03-10 00:00:00.000
    2   2010-03-16 00:00:00.000 2010-03-20 00:00:00.000
    3   2010-04-01 00:00:00.000 2010-04-10 00:00:00.000
    4   2010-04-16 00:00:00.000 2010-04-20 00:00:00.000
    
    (Righe interessate: 4)
    
    Messaggio 547, livello 16, stato 0, riga 15
    L'istruzione INSERT è in conflitto con il vincolo CHECK "Ck_PeriodiSovrapposti". Il conflitto si è verificato nella tabella "dbo.periodi" del database "tempdb".
    L'istruzione è stata interrotta.
    
    */ 
    

     
    Poi vedi tu se inserire un indice univoco sul campo PeriodoDa per velocizzare la ricerca dei periodi sovrapposti, visto il numero di righe che può ospitare la tabella (max 365 per ogni anno) credo sia inutile.

    Ciao
    Giorgio Rancati

    giovedì 30 settembre 2010 12:29
    Moderatore

Tutte le risposte

  • Ciao G Luca,

    You wrote on 29/09/2010 :

    Ho una tabella dove tra l'altro, ho due campi di tipo data PeridoDa a PeriodoA che mi indicano inizio e fine di un periodo.
    Dovrei aggiungere un vincolo di univocità su questo periodo e fin qui il da mi mette a disposizione gli indici univoci ma dovrei anche garantire un vincolo per il quale due periodi in due record differenti non si intersechino.

    Come implementereste (e anche dove) questo vincolo?

    se lo vuoi fare a livello di db mi sa che l'unica mi sa che sono i trigger, con tutto quello che comportano, ma essendo fondamentalmente logica di business io lo farei a livello applicativo in fase di insert/update probabilmente.

    .m


    Mauro Servienti
    {C67C0157-5D98-4733-A75E-93CAEE4BADC8}
    Microsoft MVP - Visual C# / MCTS
    http://mvp.support.microsoft.com
    blog @ http://milestone.topics.it
    whynot [ at ] topics [ dot ] it
    giovedì 30 settembre 2010 06:17
  • Ciao G Luca,

    You wrote on 29/09/2010 :

    Ho una tabella dove tra l'altro, ho due campi di tipo data PeridoDa a PeriodoA che mi indicano inizio e fine di un periodo.
    Dovrei aggiungere un vincolo di univocità su questo periodo e fin qui il da mi mette a disposizione gli indici univoci ma dovrei anche garantire un vincolo per il quale due periodi in due record differenti non si intersechino.

    Come implementereste (e anche dove) questo vincolo?

    se lo vuoi fare a livello di db mi sa che l'unica mi sa che sono i trigger, con tutto quello che comportano, ma essendo fondamentalmente logica di business io lo farei a livello applicativo in fase di insert/update probabilmente.

    .m


    Mauro Servienti
    {C67C0157-5D98-4733-A75E-93CAEE4BADC8}
    Microsoft MVP - Visual C# / MCTS
    http://mvp.support.microsoft.com
    blog @ http://milestone.topics.it
    whynot [ at ] topics [ dot ] it


    Ciao,

    Se lo facessi lato applicativo però, prima di una insert o update dovrei controllare che non esistesse un periodo che interserchi e se non c'è fare l'insert/update.

    Il fatto è che tra la fine del controllo e l'insert c'è sempre la possibilità che qualcunaltro inserisca un record che intereschi quindi anche se remota c'è una possibilità che il sistema fallisca.

    mi sbaglio?

    giovedì 30 settembre 2010 07:14
  • Ciao G Luca,

    You wrote on 30/09/2010 :

    Il fatto è che tra la fine del controllo e l'insert c'è sempre la possibilità che qualcunaltro inserisca un record che intereschi quindi anche se remota c'è una possibilità che il sistema fallisca.

    mi sbaglio?

    transaction is the word :-)

    .m


    Mauro Servienti
    {C67C0157-5D98-4733-A75E-93CAEE4BADC8}
    Microsoft MVP - Visual C# / MCTS
    http://mvp.support.microsoft.com
    blog @ http://milestone.topics.it
    whynot [ at ] topics [ dot ] it
    giovedì 30 settembre 2010 08:43
  • Ho una tabella dove tra l'altro, ho due campi di tipo data PeridoDa e PeriodoA che mi indicano inizio e fine di un periodo.
    Dovrei aggiungere un vincolo di univocità su questo periodo e fin qui il database mi mette a disposizione gli indici univoci ma dovrei anche garantire un vincolo per il quale due periodi in due record differenti non si intersechino.

    Come implementereste (e anche dove) questo vincolo?


    Ciao Luca,

    I DBMS offrono i constraint UNIQUE o PRIMARY KEY per definirire un vincolo di univocità, mentre gli indici sono strutture atte a garantire elevate prestazioni nell'accesso ai dati. E' poi vero che dal punto di vista fisico un constraint UNIQUE o una PK vengono implementati mediante un indice unique, ma questo potrebbe tranquillamente cambiare in futuro.

    Ora, bisognerebbe capire dettagliatamente cosa intendi con "intersecazione" dei periodi. Prova a postare un esempio completo con la struttura semplificata della tabella (CREATE TABLE), alcune righe di prova (INSERT INTO) ed un ulteriore comando di INSERT che dovrebbe fallire.

    Ciao!


    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.microsoft.com/Forums/it-IT/sqlserverit
    giovedì 30 settembre 2010 09:05
    Moderatore
  • Ciao G Luca,

    You wrote on 30/09/2010 :

    Il fatto è che tra la fine del controllo e l'insert c'è sempre la possibilità che qualcunaltro inserisca un record che intereschi quindi anche se remota c'è una possibilità che il sistema fallisca.

    mi sbaglio?

    transaction is the word :-)

    .m


    Mauro Servienti
    {C67C0157-5D98-4733-A75E-93CAEE4BADC8}
    Microsoft MVP - Visual C# / MCTS
    http://mvp.support.microsoft.com
    blog @ http://milestone.topics.it
    whynot [ at ] topics [ dot ] it


    Ciao,

    come dovrei usare le transazioni? Ad esempio, in questo caso :

    1.Apro Transazione

    2.Controllo se il periodo che sto per inserire interseca con qualcuno(esito : no)

    3.Intanto qualcuno inserisce un periodo che interseca

    4.Committo e non mi accorgo di nulla

    Oppure con controllo successivo

    1.Apro Transazione

    2.Inserisco Periodo

    3.Controllo se interseco con un altro(esito : no)

    4. Intanto qualcuno inserisce un periodo che interseca

    5.Committo e non mi accorgo di nulla

     

    Cosa mi sfugge?

     

    giovedì 30 settembre 2010 09:13
  • Ciao,

    CREATE TABLE [dbo].[Periodi](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [PeriodoDa] [datetime] NOT NULL,
    [PeriodoA] [datetime] NOT NULL,
    CONSTRAINT [PK_Periodi] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    
    insert into periodi (PeriodoDa,PeriodoA) values ('20100101','20100115');
    insert into periodi (PeriodoDa,PeriodoA) values ('20100116','20100131');
    
    insert into periodi (PeriodoDa,PeriodoA) values ('20100105','20100107');
    insert into periodi (PeriodoDa,PeriodoA) values ('20100110','20100120');
    
    
    

    Il penultimo dovrebbe fallire perchè interseca con il primo
    L'ultimo dovrebbe fallire perchè interseca con i primi due

     

     

     

    giovedì 30 settembre 2010 09:32
  • Salve a tutti,

    Ho una tabella dove tra l'altro, ho due campi di tipo data PeridoDa e PeriodoA che mi indicano inizio e fine di un periodo.
    Dovrei aggiungere un vincolo di univocità su questo periodo e fin qui il database mi mette a disposizione gli indici univoci ma dovrei anche garantire un vincolo per il quale due periodi in due record differenti non si intersechino.

    Come implementereste (e anche dove) questo vincolo?
    Ciao e grazie


    Salve,

    Questo post è un po border line ma mi interessa molto sapere anche la soluzione lato applicativo, non sono del tutto sicuro di voler affidare questo compito al database

     

    giovedì 30 settembre 2010 09:34
  • Ciao,

    Il penultimo dovrebbe fallire perchè interseca con il primo
    L'ultimo dovrebbe fallire perchè interseca con i primi due
     


    Ciao Luca,

    ti propongo una soluzione che utilizza una funzione e un vincolo check a livello di tabella.

    Use tempdb
    Go
    
    -- Creo la tabella
    Create Table dbo.periodi
    (Id Int Identity Primary Key Clustered,
     PeriodoDa Datetime not null,
     PeriodoA Datetime not null,
     Constraint Ck_Periodi Check(PeriodoDa!>PeriodoA)
     )
     Go
     
     -- Creo la funzione: 
     -- Ritorna 1 se il periodo che viene passato 
     -- si sovrappone ai dati già esistenti 
     
     Create Function dbo.ufn_CheckIsPeriodiSovrapposti
     (@Id Int,
     @PeriodoDa Datetime,
     @PeriodoA Datetime
     )
     Returns Bit As
     Begin
     
      Declare @Retvalue Bit 
      
      Select Top 1 @Retvalue = 1
      From dbo.periodi 
      Where Id!=@Id 
      And Not (PeriodoDa>@PeriodoA Or PeriodoA<@PeriodoDa)
      Order By PeriodoDa
      
      Return IsNull(@Retvalue,0)
      
     end
     Go
     
     -- Creo il vincolo sulla tabella
     Alter Table dbo.Periodi
     Add Constraint Ck_PeriodiSovrapposti Check (dbo.ufn_CheckIsPeriodiSovrapposti(Id,PeriodoDa,PeriodoA)=0)
     Go
     
     
     --Inserisco alcuni periodi
     Insert Into dbo.periodi (PeriodoDa,PeriodoA)
     Values ('20100301','20100310'),
      ('20100316','20100320'),
      ('20100401','20100410'),
      ('20100416','20100420')
     
    
    -- Select dei dati
    Select * From dbo.periodi
     
     -- Inserisco un periodo che si sovrappone ad altri
     Insert Into dbo.periodi (PeriodoDa,PeriodoA)
     Values ('20100305','20100307')
    
     
    -- Pulizia
     Drop Table dbo.periodi
     Drop function dbo.ufn_CheckIsPeriodiSovrapposti
    
    /* Risultato
     
    Id   PeriodoDa    PeriodoA
    ----------- ----------------------- -----------------------
    1   2010-03-01 00:00:00.000 2010-03-10 00:00:00.000
    2   2010-03-16 00:00:00.000 2010-03-20 00:00:00.000
    3   2010-04-01 00:00:00.000 2010-04-10 00:00:00.000
    4   2010-04-16 00:00:00.000 2010-04-20 00:00:00.000
    
    (Righe interessate: 4)
    
    Messaggio 547, livello 16, stato 0, riga 15
    L'istruzione INSERT è in conflitto con il vincolo CHECK "Ck_PeriodiSovrapposti". Il conflitto si è verificato nella tabella "dbo.periodi" del database "tempdb".
    L'istruzione è stata interrotta.
    
    */ 
    

     
    Poi vedi tu se inserire un indice univoco sul campo PeriodoDa per velocizzare la ricerca dei periodi sovrapposti, visto il numero di righe che può ospitare la tabella (max 365 per ogni anno) credo sia inutile.

    Ciao
    Giorgio Rancati

    giovedì 30 settembre 2010 12:29
    Moderatore
  • Questo post è un po border line ma mi interessa molto sapere anche la soluzione lato applicativo, non sono del tutto sicuro di voler affidare questo compito al database

    Ciao Luca,

    io non avrei dubbi, implementerei il vincolo a livello di database.
    Considera che potresti avere la necessità di popolare o aggiornare la tabella con i dati provenienti da un'altra tabella o da un file csv in cui potrebbero già esistere periodi sovrapposti o che potrebbero sovrapporsi ai dati già esistenti.
    Considera anche che queste operazioni potrebbero essere eseguite da altre applicazioni (Bulk Insert, MsAccess collegato al db, ecc ecc)


    Ciao
    Giorgio Rancati
    giovedì 30 settembre 2010 12:52
    Moderatore
  • Ciao,

    Il penultimo dovrebbe fallire perchè interseca con il primo
    L'ultimo dovrebbe fallire perchè interseca con i primi due
     


    Ciao Luca,

    ti propongo una soluzione che utilizza una funzione e un vincolo check a livello di tabella.

    Use tempdb
    
    Go
    
    
    
    -- Creo la tabella
    
    Create Table dbo.periodi
    
    (Id Int Identity Primary Key Clustered,
    
     PeriodoDa Datetime not null,
    
     PeriodoA Datetime not null,
    
     Constraint Ck_Periodi Check(PeriodoDa!>PeriodoA)
    
     )
    
     Go
    
     
    
     -- Creo la funzione: 
    
     -- Ritorna 1 se il periodo che viene passato 
    
     -- si sovrappone ai dati già esistenti 
    
     
    
     Create Function dbo.ufn_CheckIsPeriodiSovrapposti
    
     (@Id Int,
    
     @PeriodoDa Datetime,
    
     @PeriodoA Datetime
    
     )
    
     Returns Bit As
    
     Begin
    
     
    
     Declare @Retvalue Bit 
    
     
    
     Select Top 1 @Retvalue = 1
    
     From dbo.periodi 
    
     Where Id!=@Id 
    
     And Not (PeriodoDa>@PeriodoA Or PeriodoA<@PeriodoDa)
    
     Order By PeriodoDa
    
     
    
     Return IsNull(@Retvalue,0)
    
     
    
     end
    
     Go
    
     
    
     -- Creo il vincolo sulla tabella
    
     Alter Table dbo.Periodi
    
     Add Constraint Ck_PeriodiSovrapposti Check (dbo.ufn_CheckIsPeriodiSovrapposti(Id,PeriodoDa,PeriodoA)=0)
    
     Go
    
     
    
     
    
     --Inserisco alcuni periodi
    
     Insert Into dbo.periodi (PeriodoDa,PeriodoA)
    
     Values ('20100301','20100310'),
    
     ('20100316','20100320'),
    
     ('20100401','20100410'),
    
     ('20100416','20100420')
    
     
    
    
    
    -- Select dei dati
    
    Select * From dbo.periodi
    
     
    
     -- Inserisco un periodo che si sovrappone ad altri
    
     Insert Into dbo.periodi (PeriodoDa,PeriodoA)
    
     Values ('20100305','20100307')
    
    
    
     
    
    -- Pulizia
    
     Drop Table dbo.periodi
    
     Drop function dbo.ufn_CheckIsPeriodiSovrapposti
    
    
    
    /* Risultato
    
     
    
    Id  PeriodoDa  PeriodoA
    
    ----------- ----------------------- -----------------------
    
    1  2010-03-01 00:00:00.000 2010-03-10 00:00:00.000
    
    2  2010-03-16 00:00:00.000 2010-03-20 00:00:00.000
    
    3  2010-04-01 00:00:00.000 2010-04-10 00:00:00.000
    
    4  2010-04-16 00:00:00.000 2010-04-20 00:00:00.000
    
    
    
    (Righe interessate: 4)
    
    
    
    Messaggio 547, livello 16, stato 0, riga 15
    
    L'istruzione INSERT è in conflitto con il vincolo CHECK "Ck_PeriodiSovrapposti". Il conflitto si è verificato nella tabella "dbo.periodi" del database "tempdb".
    
    L'istruzione è stata interrotta.
    
    
    
    */ 
    
    

     
    Poi vedi tu se inserire un indice univoco sul campo PeriodoDa per velocizzare la ricerca dei periodi sovrapposti, visto il numero di righe che può ospitare la tabella (max 365 per ogni anno) credo sia inutile.

    Ciao
    Giorgio Rancati

     

    Perfetta grazie mille!

    giovedì 30 settembre 2010 13:46
  • Questo post è un po border line ma mi interessa molto sapere anche la soluzione lato applicativo, non sono del tutto sicuro di voler affidare questo compito al database

    Ciao Luca,

    io non avrei dubbi, implementerei il vincolo a livello di database.
    Considera che potresti avere la necessità di popolare o aggiornare la tabella con i dati provenienti da un'altra tabella o da un file csv in cui potrebbero già esistere periodi sovrapposti o che potrebbero sovrapporsi ai dati già esistenti.
    Considera anche che queste operazioni potrebbero essere eseguite da altre applicazioni (Bulk Insert, MsAccess collegato al db, ecc ecc)


    Ciao
    Giorgio Rancati

    Si, concordo con quello che dici anche se faccio alcune considerazioni,

    il mio progetto ha un DAL proprio per essere libero di "usare un DB differente" e vorrei orientarmi il più possibile in questa direzione.

    Per le importazioni/interfacciamenti ad altri software, rendo sempre disponibile delle dll per l'inserimento dei dati(che sono comunque quelle che usa anche il mio applicaztivo). Se riuscissi a implementare il vincolo anche in questo strato, sarei comunque sicuro che la funzionalità sia gestita a prescindere dal DB.

    E' chiaro che il mio DB di riferimento è Sql server e qul controllo sarà nella definizione del database, ma gestirlo anche nel codice, sarebbe di sicuro un valore aggiunto.

    ciao e grazie

    giovedì 30 settembre 2010 13:52
  • Si, concordo con quello che dici anche se faccio alcune considerazioni,

    il mio progetto ha un DAL proprio per essere libero di "usare un DB differente" e vorrei orientarmi il più possibile in questa direzione.

    Per le importazioni/interfacciamenti ad altri software, rendo sempre disponibile delle dll per l'inserimento dei dati(che sono comunque quelle che usa anche il mio applicaztivo). Se riuscissi a implementare il vincolo anche in questo strato, sarei comunque sicuro che la funzionalità sia gestita a prescindere dal DB.

    E' chiaro che il mio DB di riferimento è Sql server e qul controllo sarà nella definizione del database, ma gestirlo anche nel codice, sarebbe di sicuro un valore aggiunto.


    Ciao Luca,

    lato applicativo bisognerebbe fare:
    1) Aprire una transazione
    2) Leggere le righe della tabella impostando un blocco a livello di tabella che impedisca la lettura/scrittura ad altri utenti
    3) Se durante la lettura non trovo periodi che si intersecano, scrivo il record
    4) Chiudere la transazione

    rimane il fatto che il codice non va bene per tutti i databases perchè l'hint per il blocco della tabella non è universale, ogni databases ha la sua sintassi.

    Ciao
    Giorgio Rancati 

    venerdì 1 ottobre 2010 10:32
    Moderatore