Newsgroup: it.comp.software.database
Titolo: Regola di convalida dati  (SQL server 2003)

Premesso che so usare discretamente Access, ma ho mosso appena due passi su
SQL Server (2003), mi trovo in una condizione dalla quale non so come
uscire.
Ho una struttra in cui debbo gestire degli elementi che possono appartenere
a diverse classi (ogni elemento appartiene ad una sola classe). Ogni
elemento possiede poi diversi parametri che sono caratteristici della classe
a cui appartiene: percui ogni elemento della stessa classe ha gli stessi
parametri.
Ho dunque strutturato la cosa così:
Tab 1 - lista delle classi disponibili
Tab 2 - lista degli elementi disponibili
Tab 3 - lista dei parametri disponibili
ho poi relazionato le tabelle 2 e 3 con la 1 in maniera che ogni elemento e
ogni parametro fosse legato alla classe di appartenenza.
C'è poi una Tab 4 in cui debbo registrare le eventuali variazioni dei valori
dei parametri degli elementi:
Campo A - Nome Elemento che subisce la modifica.
Campo B - Nome Parametro che subisce la modifica.
Campo C - Nuovo valore del Parametro.
 
Il problema è fare in modo che nella Tab 4 sia possibili inserire coppie
Elementi-Parametri appartenenti alla stessa classe.
Grazie a tutti!
--------------------------------
Inviato via 
http://arianna.libero.it/usenet/

 Elementi-Parametri appartenenti alla stessa classe.
Aggiungi un campo alla PK, ad esempio un numerico progressivo, per cui
ciascuna coppia Elemento-Parametro avrà un suo progressivo e potrai
gestire quanti parametri aggiuntivi vuoi...
-- 
David Martin


Non ho capito.... :-(
Stai parlando della PK della Tab 4?
Ad oggi *TUTTE* le tabella hanno la PK sul classico ID con autoincremento.
Tab 2 e Tab 3 hanno un campo Classe ID che ha una relazione 1-a-molti verso
la PK della Tab 1.
Tab 4 ha due campi ElementoID e ParametroID che hanno una relazione
1-a-molti rispettivamente verso la PK delle Tab 2 e 3 (in sostanza non
memorizzo direttamente i nomi degli elementi e dei parametri ma i loro ID).
A questo punto io voglio che nei record della Tab 4 sia possibile solo
specificare coppie ElementoID-ParametroID tali che nei record relazionati si
abbia che [Tab 2].Classe ID = [Tab 3].Classe ID.
Non so se la cosa ti era già chiara o se ho contribuito solo a fare casino,
ma spero di essere stato un po' più chiaro su quello che ho fatto.
Grazie.


 ma spero di essere stato un po' più chiaro su quello che ho fatto.
Sinceramente, pensavo di aver capito ma evidentemente non è così.
Per capire di cosa stiamo parlando, dovresti postare qui gli script di
creazione delle tabelle e di inserimento di alcuni dati di test.
Poi scrivi qual'è il problema e magari riusciamo a parlare della stessa
cosa :-)
-- 
David Martin

 cosa :-)
OK, domeni vedo di tirare fuori qualcosa: da buon utente Access ho creato
tutto da interfaccia, ma se non erro SQL server genera da se gli script in
ogni caso.


 ogni caso.
Da Enterprise Manager (se hai SQL 2000) oppure da Management Studio (se
hai SQL 2005), puoi tranquillamente generare gli script ddl.
Pr gli script di insert dei dati, invece, da un'occhiata qua:
http://vyaskn.tripod.com/code.htm#inserts
-- 
David Martin

 >> creazione delle tabelle e di inserimento di alcuni dati di test.
Eccoci qua.
Gli script di generazione delle tabelle sono questi. Ovviamente nei post
precedenti avevo usato dei nomi di tabelle e campi semplificati per capirci
meglio.
Non sono riuscito a generare uno script relativo alle relazioni imposte alle
tabelle, ma dovrebbe essere abbastanza chiaro anche se te lo spiego a voce.
------------------------------------------
Tab 1 - definisce le classi disponibili.
------------------------------------------
ID è la sua PK.
CREATE TABLE [dbo].[TechID] (
	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Tech] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Sys] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
----------------------------------------------
Tab 2 - definisce gli elementi disponibili.
----------------------------------------------
ID è la sua PK. Per ogniuno il campo TechID (relazionato 1-a-molti con
[dbo].[TechID].[ID]) ne definisce la classe di appartenenza.
CREATE TABLE [dbo].[ElementID] (
	[ID] [bigint] NOT NULL ,
	[Element] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Type] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PER] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TechID] [bigint] NOT NULL
) ON [PRIMARY]
GO
----------------------------------------------
Tab 3 - definisce i parametri disponibili.
----------------------------------------------
 ID è la sua PK. Per ogniuno il campo TechID (relazionato 1-a-molti con
[dbo].[TechID].[ID]) ne definisce la classe di appartenenza.
CREATE TABLE [dbo].[ParamDictionary] (
	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Param] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TechID] [bigint] NOT NULL ,
	[Type] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
----------------------------------------------
Tab 4 - definisce la lista dei cambiamenti.
----------------------------------------------
 ID è la sua PK.
Per ogni Record viene inserito:
* [ElemID] che rappresenta l'ID dell'elemento su cui fare la variazione.
* [ParamID] che rappresenta l'ID del parametro di cui cambia il valore.
* [Value] il nuovo valore da inserire.
[ElemID] è relazionato 1-a-molti con [dbo].[ElementID].[ID]
[ParamID] è relazionato 1-a-molti con [dbo].[ParamDictionary].[ID]
 
CREATE TABLE [dbo].[ParamChange] (
	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Date] [datetime] NULL ,
	[ElemID] [bigint] NOT NULL ,
	[ParamID] [bigint] NOT NULL ,
	[Value] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SVI] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A1] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Note] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Descritta la struttura adesso il problema è fare sì che siano accettati
sulla tabella [dbo].[ParamChange] solo coppie [ElemID] - [ParamID] tali che
i record relazionati su [dbo].[ElementID] e [dbo].[ParamDictionary]
riportino lo stesso valore di [TechID].
Immagino che ci sia la possibilità di definire questa come regola di
convalida dei dati per cui un record che NON rispetta la regola non è
accettato, come ad esempio non viene accettato un doppio su una PK, tanto
per capirsi.
I dati verranno immessi manualmente dagli utenti, debbo ancora definire con
che tipo di interfaccia. é ovvio che potrei effettuare una verifica con del
codice a livello di interfaccia, ma volevo capire (megari imparando una cosa
nuova) se fosse possibile porre vincoli di questo tipo sul DB.
Spero di essere stato abbastanza chiaro, non vorrei fartio perdere ore
perchè non sono in grado di spiegarmi....


 riportino lo stesso valore di [TechID].
Ok, stavolta ho capito ;-)
Per avere la convalida ho usato una funzione, che confronta i [TechID]
della coppia [ElemID] - [ParamID] che stai per inserire, e autorizza
l'inserimento.
USE [tempdb]
GO
/****** Oggetto:  Table [dbo].[TechID]    Data script: 11/30/2007
11:20:03 ******/
CREATE TABLE [dbo].[TechID](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Tech] [char](3) NOT NULL,
	[Sys] [char](4) NOT NULL,
 CONSTRAINT [PK_TechID] PRIMARY KEY CLUSTERED
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Oggetto:  Table [dbo].[ElementID]    Data script: 11/30/2007
11:20:24 ******/
CREATE TABLE [dbo].[ElementID](
	[ID] [bigint] NOT NULL,
	[Element] [char](6) NOT NULL,
	[Type] [char](6) NOT NULL,
	[PER] [char](2) NOT NULL,
	[TechID] [bigint] NOT NULL,
 CONSTRAINT [PK_ElementID] PRIMARY KEY CLUSTERED
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ElementID]  WITH CHECK ADD  CONSTRAINT
[FK_ElementID_TechID] FOREIGN KEY([TechID])
REFERENCES [dbo].[TechID] ([ID])
GO
ALTER TABLE [dbo].[ElementID] CHECK CONSTRAINT [FK_ElementID_TechID]
/****** Oggetto:  Table [dbo].[ParamDictionary]    Data script:
11/30/2007 11:20:39 ******/
CREATE TABLE [dbo].[ParamDictionary](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Param] [char](20) NOT NULL,
	[TechID] [bigint] NOT NULL,
	[Type] [char](6) NOT NULL,
 CONSTRAINT [PK_ParamDictionary] PRIMARY KEY CLUSTERED
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ParamDictionary]  WITH CHECK ADD  CONSTRAINT
[FK_ParamDictionary_TechID] FOREIGN KEY([TechID])
REFERENCES [dbo].[TechID] ([ID])
GO
ALTER TABLE [dbo].[ParamDictionary] CHECK CONSTRAINT
[FK_ParamDictionary_TechID]
/****** Oggetto:  Table [dbo].[ParamChange]    Data script: 11/30/2007
11:20:57 ******/
CREATE TABLE [dbo].[ParamChange](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NULL,
	[ElemID] [bigint] NOT NULL,
	[ParamID] [bigint] NOT NULL,
	[Value] [char](10) NOT NULL,
	[SVI] [char](15) NULL,
	[A1] [char](15) NULL,
	[Note] [ntext] NULL,
 CONSTRAINT [PK_ParamChange] PRIMARY KEY CLUSTERED
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ParamChange]  WITH CHECK ADD  CONSTRAINT
[FK_ParamChange_ElementID] FOREIGN KEY([ElemID])
REFERENCES [dbo].[ElementID] ([ID])
GO
ALTER TABLE [dbo].[ParamChange] CHECK CONSTRAINT [FK_ParamChange_ElementID]
GO
ALTER TABLE [dbo].[ParamChange]  WITH CHECK ADD  CONSTRAINT
[FK_ParamChange_ParamDictionary] FOREIGN KEY([ParamID])
REFERENCES [dbo].[ParamDictionary] ([ID])
GO
ALTER TABLE [dbo].[ParamChange] CHECK CONSTRAINT
[FK_ParamChange_ParamDictionary]
GO
/****** Oggetto:  UserDefinedFunction [dbo].[ufn_VerificaParamChange]
 Data script: 11/30/2007 11:21:28 ******/
CREATE FUNCTION [dbo].[ufn_VerificaParamChange]
(
	@ElemID [bigint],
	@ParamID [bigint]
)
RETURNS INT
AS
BEGIN
	DECLARE @TechElemento AS BIGINT
	DECLARE @TechParametro AS BIGINT
	DECLARE @Output AS TINYINT
	SET @TechElemento = (
							SELECT [TechID]
							  FROM [tempdb].[dbo].[ElementID]
							 WHERE [ID] = @ElemID
						)
	SET @TechParametro = (
							SELECT [TechID]
							  FROM [tempdb].[dbo].[ParamDictionary]
							 WHERE [ID] = @ParamID
						)
	IF @TechElemento <> @TechParametro
		SET @Output = 1
	ELSE
		SET @Output = 0
	RETURN @Output
END
GO
/* Modifica della tabella ParamChange in modo che i valori inseriti
siano validati in base alla funzione */
ALTER TABLE [dbo].[ParamChange]  WITH CHECK ADD  CONSTRAINT
[CK_ParamChange] CHECK  (([dbo].[ufn_VerificaParamChange]([ElemID],
[ParamID]) = 0))
GO
ALTER TABLE [dbo].[ParamChange] CHECK CONSTRAINT [CK_ParamChange]
GO
/* Inserimento dati */
INSERT INTO [TechID] ([Tech],[Sys])VALUES('T01','A')
INSERT INTO [TechID] ([Tech],[Sys])VALUES('T02','B')
INSERT INTO [TechID] ([Tech],[Sys])VALUES('T03','C')
GO
INSERT INTO [ElementID]
([ID],[Element],[Type],[PER],[TechID])VALUES(100,'Elem1','X','X',1)
INSERT INTO [ElementID]
([ID],[Element],[Type],[PER],[TechID])VALUES(200,'Elem2','Y','Y',2)
INSERT INTO [ElementID]
([ID],[Element],[Type],[PER],[TechID])VALUES(300,'Elem3','X','X',1)
GO
INSERT INTO [ParamDictionary] ([Param],[TechID],[Type])VALUES('Par01',1,'X')
INSERT INTO [ParamDictionary] ([Param],[TechID],[Type])VALUES('Par02',1,'X')
INSERT INTO [ParamDictionary] ([Param],[TechID],[Type])VALUES('Par03',3,'Y')
GO
/* Questo inserimento va a buon fine */
INSERT INTO [ParamChange]
([Date],[ElemID],[ParamID],[Value],[SVI],[A1],[Note])VALUES('nov 30 2007
12:00:00:000AM',100,1,'aaa','aaa','aaa',NULL)
GO
/* Questo inserimento genera una violazione del vincolo check:
L'istruzione INSERT ha generato un conflitto con il vincolo TABLE CHECK
'CK_ParamChange' nel database 'Tempdb', tabella 'ParamChange'.
*/
INSERT INTO [ParamChange]
([Date],[ElemID],[ParamID],[Value],[SVI],[A1],[Note])VALUES('nov 30 2007
12:00:00:000AM',100,3,'aaa','aaa','aaa',NULL)
GO
/* Cancellazione oggetti */
DROP TABLE [dbo].[ParamChange]
GO
DROP FUNCTION [dbo].[ufn_VerificaParamChange]
GO
DROP TABLE [dbo].[ParamDictionary]
GO
DROP TABLE [dbo].[ElementID]
GO
DROP TABLE [dbo].[TechID]
GO
-- 
David Martin

 l'inserimento.
Mitico! Mi hai davero insegnato una cosa nuova, ti ringrazio. Immaginavo si
potesse fare ma non avevo la piů pallida idea di come. Ero arrivato al
concetto di CONSTRAINT, ma non sapevo come implementare una cosa complessa
che non fosse una costante... :-(
Un'ultima cosa: come fai a far generare lo script di tutta la descrizione
del DB? Con le tabelle sono riuscito, ma con le relazioni no. E averlo
sarebbe comodissimo per fare una sorta di back-up della struttura del DB.
Ah, ovviamente, GRAZIE INFINITE!!!!


 Ah, ovviamente, GRAZIE INFINITE!!!!
E' stato un piacere ;-)
-- 
David Martin

 ieri sera.
 
Questo non è un problema, mi interessa la possibilità di ricostruire la
struttura del DB. Ad ogni modo terrò da buon conto, non si sa mai!
Grzie ancora.
