471,092 Members | 1,478 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

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
Jul 23 '05 #1
4 3392

"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
Jul 23 '05 #2
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
Jul 23 '05 #3
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.

Jul 23 '05 #4

"--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?
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Christoph Bisping | last post: by
8 posts views Thread by J.Haan | last post: by
13 posts views Thread by Slower Than You | last post: by
6 posts views Thread by jacob navia | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.