Principale utente con più risposte
Vincolo su Periodo

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)
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- Contrassegnato come risposta Mila Daniel Ovidiu giovedì 30 settembre 2010 15:05
-
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- Contrassegnato come risposta Mila Daniel Ovidiu giovedì 30 settembre 2010 15:05
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 -
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?
-
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 -
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- Contrassegnato come risposta Mila Daniel Ovidiu giovedì 30 settembre 2010 15:05
-
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?
-
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 -
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
-
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- Contrassegnato come risposta Mila Daniel Ovidiu giovedì 30 settembre 2010 15:05
-
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 -
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 RancatiPerfetta grazie mille!
-
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 RancatiSi, 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
-
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