By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Limiting integers

P: 44
Hi,

I am trying to prevent one integer (minrate) exceeding the value of (maxrate) in sql server 2000.

The table is to store details relating to the cost of accommodation. When adding new rows I want to make sure the min rate cannot exceed the max rate.

I have searched the net but have not turned up any positive results.

I have tried a check constraint and trigger but cannot find anything that works.

I am fairly new to sql server and am using 2000 as this is part of a project which limits me to using 2000.

Many thanks
May 7 '08 #1
Share this Question
Share on Google+
7 Replies


P: 46
Hi,

I am trying to prevent one integer (minrate) exceeding the value of (maxrate) in sql server 2000.

The table is to store details relating to the cost of accommodation. When adding new rows I want to make sure the min rate cannot exceed the max rate.

I have searched the net but have not turned up any positive results.

I have tried a check constraint and trigger but cannot find anything that works.

I am fairly new to sql server and am using 2000 as this is part of a project which limits me to using 2000.

Many thanks
Are you using a store procedure to add new rows to the table? If yes than you can use a simple if statement to INSERT row to a table only if values are within the range.

Hope this helps,
Sree
May 7 '08 #2

P: 44
[font=Arial]Cheers for the reply[/font]

The data is being inserted from a web page. Under normal circumstances I would just validate the info from the web side of things but I am doing a project that requires database skills to be evaluated.

I am currently looking at an after trigger and not having a great deal of success. I wanted to use a check_constraint as this sounded the easiest way but again not much success.

Cheers
May 7 '08 #3

P: 46
[font=Arial]Cheers for the reply[/font]

The data is being inserted from a web page. Under normal circumstances I would just validate the info from the web side of things but I am doing a project that requires database skills to be evaluated.

I am currently looking at an after trigger and not having a great deal of success. I wanted to use a check_constraint as this sounded the easiest way but again not much success.

Cheers
Hi,

I do not think Trigger is the right thing to use for this but if you can post you SQL script for CHECK Constraint may be some one can help by seeing where you have gone wrong.

Cheers,
Sree
May 7 '08 #4

ck9663
Expert 2.5K+
P: 2,878
Hi,

I am trying to prevent one integer (minrate) exceeding the value of (maxrate) in sql server 2000.

The table is to store details relating to the cost of accommodation. When adding new rows I want to make sure the min rate cannot exceed the max rate.

I have searched the net but have not turned up any positive results.

I have tried a check constraint and trigger but cannot find anything that works.

I am fairly new to sql server and am using 2000 as this is part of a project which limits me to using 2000.

Many thanks

If the two columns are in the same table and you're checking within the same row, a CONSTRAINT should be fine.

For more complicated check, use TRIGGER.

What have you done so far?

-- CK
May 7 '08 #5

P: 44
[font=Arial]The data in question is in the same table and same row just different columns.[/font]

[font=Arial]I don't have a great deal as I'm struggling with the syntax.[/font]

To be honest I haven't used any constraints other than P and F Keys before.

I would have thought that a simple if x is > y then do not allow the insert else insert it.

I have sql server 2000 with enterprise manager and query analyzer.

Are there any tutorials that could set me on the right path. I have done some searching but there doesn't seem to be much out there for 2000 and the books I have aren't much use either.

Cheers
May 8 '08 #6

P: 46
Hi,

I am sure you will find MSDN online useful. I am adding a link where it explains with example. I hope this link helps:
http://www.mssqlcity.com/Articles/General/using_constraints.htm

We will need to know what syntax you have created to be able to see where you are going wrong. Hence, your best bet is to add you syntax here.

Hope you figure it out soon.

Cheers
Sree
May 8 '08 #7

P: 44
Well, this is what I have.

ALTER TABLE accommodation ADD CONSTRAINT minmaxrate_check CHECK (minrate < maxrate)

This causes and error.

ALTER TABLE statement conficted with TABLE CHECK constraint 'minmaxrate_check'. The conflict occured in databese....


Update.

As I was writing this I noticed that the mminrate and maxrate were set to varchar!!! I have altered them to money and it works. A nooby error I guess

Thanks for the help though.
May 8 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.