On Wed, Jun 28, 2006 at 08:56:31 -0700, [EMAIL PROTECTED] wrote: > * tbl_data_type : contains the data type of the profile, their id > and their names. E.g.: id=1, data type name="last name"; id=2, > data type name="address", and so on > * tbl_data : the data of all the profiles of the system; it has > three columns: the id of the profile the data belongs to (linked > to the tbl_user), the data type id (linked to tbl_data_type) and > the value of the data. E.g.: profile=1, data_type_1=1, > value="Smith", and so on > Suppose we have a data type named "unique_id", which value should be > stored in tbl_data. The value must be unique in the whole system, so > the > profiles store only one "unique_id", and I have to able to identify a > profile by this value(that's why must be unique!). > Generating such a unique id it's not a problem, using e.g. a sequence. > The problem is the user can change this value accessing to the proper > stored procedure, and the system should check that the value chosen do > not violate the requirement of uniqueness. > > I have only two solutions, I'd be glad to hear from you if they are > correct, or if you have already encountered similar problems and you > can > point me to some useful document.
You could add a flag to tbl_data that indicates whether or not the data should be unique. Then you can create a partial index where this flag is true over the combination of type id and value. For referential integrity of the flag, you can add the same flag to the tbl_data_type table and make a unique index over the id type and the flag and then make the reference from tbl_data to tbl_data_type use the type id and flag as a foreign key instead of just type id. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings