Aitor Imaz wrote:
> Hello,
>
> I have a design question regarding domains, although I don't know if
> it is really SAPDB specific (I've never used them before). I want the
> value of a certain field in a row to be checked against a predefined
> set of possible values. I understand domains can be used in a
> situation like this (defining a set of possible values). The problem
> is that although I know the number of elements the domain should
> contain as of today (two), I can't really tell if this number is going
> to grow in the future (five or six maximum, I'd say). AFAIK, this
> would require me to drop the domain and recreate it with the new set
> of values, which I want to avoid if the database is in production.
> Would an ordinary table + referential integrity be a better solution
> than a domain in this case? Can domains be altered without needing to
> drop them?
There is no ALTER DOMAIN available.
Domains are used during creation/altering of a column.
If a domain is dropped, nothing happens to the column, it has a copy of the
domain definition.
If one want to 'change' the domain definition:
- select OWNER,TABLENAME,COLUMNNAME from COLUMNS
where domainowner = '...' and domainname = '...'
hold the result somewhere
- drop domain ...
- create domain ...
- alter table modify using the new domain for the columns given in the above select
(the existing column-values are checked against the new domain during this command)
If you want to avoid this, one can use referential constraint as mentioned
(slower during every insert/update than the domain-usage, but changing the 'domain'
is much easier)
or one can use a trigger (insert/update-trigger), in which something like
IF NEW.col NOT IN ( < the domain-values > )
THEN error
(in the correct trigger-syntax) is written.
This is slower than domain-usage (perhaps faster then ref constraint, I do not know),
but easier for changing.
It's your turn to decide what is more important for you.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
