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_limits
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_limit_row_id INTEGER
REFERENCES string_size_limits(row_id)
string varcher(50)
CONSTRAINT check_string_size 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]

"Jon" <jo*********@hotmail.com> wrote in message
news:9f**************************@posting.google.c om...

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_limits
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_limit_row_id INTEGER
REFERENCES string_size_limits(row_id)
string varcher(50)
CONSTRAINT check_string_size 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

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_limits select 1, 5
insert into string_size_limits 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_StringSizes foreign key (StringSize)
references StringSizes (StringSize),
constraint CHK_StringLength 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]

Jon (jo*********@hotmail.com) writes:

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_limits
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_limit_row_id INTEGER
REFERENCES string_size_limits(row_id)
string varcher(50)
CONSTRAINT check_string_size 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?

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_limits
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

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]

"--CELKO--" <jc*******@earthlink.net> a écrit dans le message de
news:11*********************@o13g2000cwo.googlegro ups.com...

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.

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