create table with dynamic constraint

I am trying to create a table with the type of constraint I don't see in any
of the help resources.
Say the basic table structure for table t1 is (colType int, colDesc
varchar(10), colMiscellaneous varchar(100))
I want to limit the combination colType-colDesc thusly:
If the combination is new, it's okay.
If the combination is exactly the same as one previously used, it's okay.
If the colDesc is the same as one previously entered, but the colType is
different, the constraint is violated and the insert or update operation
aborts.
Is this even doable? I've used multi-column constraints before, but not in
this way.
Thanks in advance,
DaveIn t-SQL, you cannot have a query expression in a CHECK constraint, so
multi-row checks are not easy to implement declaratively. You can have a
scalar UDF in certain cases, but it might fail for UPDATE operations. So one
option is to use a trigger like:
CREATE TRIGGER trg ON t1 FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT <> 0 RETURN
IF EXISTS ( SELECT * FROM inserted i
WHERE EXISTS ( SELECT * FROM t1
WHERE t1.type = i.type
AND t1.descr <> i.descr )
) ROLLBACK
... -- add any error messages if needed.
Anith|||>> IF @.@.ROWCOUNT <> 0 RETURN
should be = 0 to see if there are any rows affected
Anith|||Dave,
I think an easier solution here would be to maintain two tables:
create table cols (
colType int not null primary key,
colDesc varchar(10)
)
create table colMisc (
colType int not null references cols(colType),
colMiscellaneous varchar(100)
)
This enforces the data integrity you want:
A single colType cannot have more than one description
A colMiscellaneous value must be associated with a colType and colDesc
You could preserve an interface like you have by creating a view to match
your current table, on which there is an INSTEAD OF trigger to perform
the one or two insert statements needed for each addition of a
colMiscellaneous
value. It may not seem like less work to do this, but it avoids what you're
awkwardly doing now, which is storing facts like "the description of
column #N
is blahblah" once for every colMiscellaneous value there happens to be
for that
column.
In the long run, what you're doing will likely get you into trouble that
you have to solve with more awkwardness, like by adding DISTINCT
to queries that shouldn't need it.
Steve Kass
Drew University
Dave wrote:

>I am trying to create a table with the type of constraint I don't see in an
y
>of the help resources.
>Say the basic table structure for table t1 is (colType int, colDesc
>varchar(10), colMiscellaneous varchar(100))
>I want to limit the combination colType-colDesc thusly:
>If the combination is new, it's okay.
>If the combination is exactly the same as one previously used, it's okay.
>If the colDesc is the same as one previously entered, but the colType is
>different, the constraint is violated and the insert or update operation
>aborts.
>Is this even doable? I've used multi-column constraints before, but not in
>this way.
>Thanks in advance,
>Dave
>
>|||That is an excellent point, and one that I had considered. However, there
really are only three columns, this is just an ancillary table of about 50
rows that will not get many hits, and there will be only one routine for
each of the operations (SELECT, INSERT, UPDATE, & DELETE). I was also just
curious how I would accomplish such a task.
I do know enough about normalization to recognize your solution is
theoretically better; in this case I think the fewer tables factor will
outweigh the drawbacks you point out.
Thanks,
Dave
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23S36LBWbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> Dave,
> I think an easier solution here would be to maintain two tables:
> create table cols (
> colType int not null primary key,
> colDesc varchar(10)
> )
> create table colMisc (
> colType int not null references cols(colType),
> colMiscellaneous varchar(100)
> )
> This enforces the data integrity you want:
> A single colType cannot have more than one description
> A colMiscellaneous value must be associated with a colType and colDesc
> You could preserve an interface like you have by creating a view to match
> your current table, on which there is an INSTEAD OF trigger to perform
> the one or two insert statements needed for each addition of a
> colMiscellaneous
> value. It may not seem like less work to do this, but it avoids what
you're
> awkwardly doing now, which is storing facts like "the description of
> column #N
> is blahblah" once for every colMiscellaneous value there happens to be
> for that
> column.
> In the long run, what you're doing will likely get you into trouble that
> you have to solve with more awkwardness, like by adding DISTINCT
> to queries that shouldn't need it.
> Steve Kass
> Drew University
> Dave wrote:
>
any