Limiting integers

[badvoc]

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

[sreemati]

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

[badvoc]

[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_constrain t as this sounded the easiest way but again not much success.

Cheers

[sreemati]

[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_constrain t 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

[ck9663]

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

[badvoc]

[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

[sreemati]

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.c om/Articles/General/using_constrain ts.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

[badvoc]

Well, this is what I have.

ALTER TABLE accommodation ADD CONSTRAINT minmaxrate_chec k CHECK (minrate < maxrate)

This causes and error.

ALTER TABLE statement conficted with TABLE CHECK constraint 'minmaxrate_che ck'. 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.