How to apply complex constraints

[Jon]

Hi all!
I want to create a constraint that uses data from other tables,
specifically i want to make sure that a varchar has exactly the length
specified in an integer-column in a table that I pointed out with a
foreign key.

I would like this to be solved something like this:

create table string_size_lim its
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_lim it_row_id INTEGER
REFERENCES string_size_lim its(row_id)
string varcher(50)
CONSTRAINT check_string_si ze CHECK ???
)

Is it possible to solve this problem without SP using above model?
Is it possible to solve this problem with SP using above model?
Must the above problem be solved using triggers?

Any help appreciated

[Simon Hayes]

Re: How to apply complex constraints


"Jon" <jonsjostedt@ho tmail.com> wrote in message
news:9f379edc.0 503150952.59caa 802@posting.goo gle.com...[color=blue]
> Hi all!
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
>
> I would like this to be solved something like this:
>
> create table string_size_lim its
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
>
> create table strings
> (
> string_size_lim it_row_id INTEGER
> REFERENCES string_size_lim its(row_id)
> string varcher(50)
> CONSTRAINT check_string_si ze CHECK ???
> )
>
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?
>
> Any help appreciated[/color]

A CHECK constraint can only access data in the table it's created on, but in
your example, what is the purpose of Row_ID as a primary key? In other
words, what is the difference between these two limits:

insert into string_size_lim its select 1, 5
insert into string_size_lim its select 2, 5

Is the second string size limit somehow different because its row_id is
different? If the primary key of your limits table was just the string size
itself, then the foreign key could be used in the CHECK constraint:

create table dbo.StringSizes (
StringSize int not null,
constraint PK_StringSizes primary key (StringSize)
)

create table dbo.Strings
(
StringSize int not null,
String varchar(50) not null,
constraint PK_Strings primary key (String),
constraint FK_Strings_Stri ngSizes foreign key (StringSize)
references StringSizes (StringSize),
constraint CHK_StringLengt h check (len(String) = StringSize)
)

insert into dbo.StringSizes select 3
insert into dbo.StringSizes select 5

insert into dbo.Strings select 3, 'Jon'
insert into dbo.Strings select 3, 'John' -- Fails
insert into dbo.Strings select 5, 'Check'
insert into dbo.Strings select 5, 'Cheque' -- Fails

If that doesn't help, I suggest you give some more details on Row_Id, and
also working CREATE TABLE and INSERT statements. But if you can't use the
string size limit itself in the foreign key, a trigger is the most likely
alternative.

Simon

[Erland Sommarskog]

Re: How to apply complex constraints

Jon (jonsjostedt@ho tmail.com) writes:[color=blue]
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
>
> I would like this to be solved something like this:
>
> create table string_size_lim its
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
>
> create table strings
> (
> string_size_lim it_row_id INTEGER
> REFERENCES string_size_lim its(row_id)
> string varcher(50)
> CONSTRAINT check_string_si ze CHECK ???
> )
>
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?[/color]

The problem does not need be solved with triggers, but that's the best
solution.

The alternative is to write a UDF which access the string_size_lim its
table, and then you CHECK constraint would read:

CHECK (len(string) = dbo.maxlen(row_ id))

The reason you should not do this, is because the performance penalty
can be severe. I remember that I played with this once, and added a
constraint with a UDF to the copy of an existing table. I then inserted
all 24000 rows into that table. Instead of two seconds it took 30!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: How to apply complex constraints

In full SQL-92, you can write such a CHECK() constraint, but not in SQL
Server yet. You would have to use a trigger and get away from
declarative code.

However, why are you putting metadata into the database in violation of
basic design principles? This is sooo wrong.

[Jason Hawryluk]

Re: How to apply complex constraints


"--CELKO--" <jcelko212@eart hlink.net> a écrit dans le message de
news:1110989814 .440585.47880@o 13g2000cwo.goog legroups.com...[color=blue]
> In full SQL-92, you can write such a CHECK() constraint, but not in SQL
> Server yet. You would have to use a trigger and get away from
> declarative code.
>
> However, why are you putting metadata into the database in violation of
> basic design principles? This is sooo wrong.
>[/color]

Why is this so wrong? How about a link to thoes basic design principles?
Where do you think metadata should be stored?