| Author |
Topic |
|
ryguy1410
Starting Member
5 Posts |
 Posted - 2006-02-07 : 15:31:44
|
| Hello,I am fairly new to SQL, so I have a few questions that may sound odd. First of all, I am trying to restrict users to putting in a three character code as an id item, or their team abbreviation. Here is the table definition:CREATE TABLE TEAMS{ city varchar(20) NOT NULL, name varchar(20) NOT NULL, id varchar(3) NOT NULL};Here is my code for adding the constraintALTER TABLE TEAMSADD CONSTRAINT Chk_id CHECK (id = `[A-Z][A-Z][A-Z]`);Here is the error that I get when trying to execute this statement:Msg 547, Level 16, State 0, Line 1ALTER TABLE statement conflicted with COLUMN CHECK constraint 'Chk_id'. The conflict occurred in database 'statbookdb', table 'cabateams', column 'id'.I am using SQL 2005 EM if this makes any difference.My other question is in regards to once the constraints have been put in. Is there a way to make SQL throw a message out when a user violates a constraint? Right now, I have a numerical constraint in and whenever I violate it, all that happens is a "This page cannot be displayed" error. It doesn't make sense if you can only do this on the front end, as I don't see the point in enforcing it on the backend if there is no way to notify the user.Thank you all in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-07 : 15:59:16
|
| Yes SQL Server will throw an error. Your application should intercept it and display a custom message.So is your table named cabateams like in the error message or is it TEAMS? Do you currently have any data that violates this check constraint in your table?Tara Kizeraka tduggan |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-02-07 : 16:05:34
|
| You want LIKE, not =ADD CONSTRAINT Chk_id CHECK (id LIKE '[A-Z][A-Z][A-Z]'); |
|
|
|
ryguy1410
Starting Member
5 Posts |
Posted - 2006-02-07 : 16:13:53
|
| It is cabateams, sorry for the confusion. No currently there is no data that violates the constraint, however whenever I go to change the data I don't get a custom message. Is there anything I need to do on my end to make sure it appears, or should it be automatic?Thanks again! |
|
|
|
ryguy1410
Starting Member
5 Posts |
Posted - 2006-02-07 : 16:19:51
|
| Ok, I adjusted the constraint to match what jsmith said, and it worked. Thanks jsmith! I still do not get any error messages to pop up though. Now I have two constraints in the system, and whenever I go to the website it just gives me the same "Page cannot be found" error. I am using ASP/HTML if that makes any difference and viewing in IE. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-07 : 16:30:16
|
| In Query Analyzer, add a value to the column that should violate the constraint. Do you get an error? If so, then your application is also getting it.Tara Kizeraka tduggan |
|
|
|
ryguy1410
Starting Member
5 Posts |
Posted - 2006-02-07 : 16:46:48
|
| I inserted data that violated the constraint and it returned an error message. So on the front end I will have to create a custom message box detailing this error? Do I simply set a variable equal to the SQL statement and check for the error value? Also, where can I find the exact value, because in query analyzer it returns a msg, level, and state error code? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-07 : 17:57:08
|
| Here's how I do it in VB.NET:Try ...SomeCodeGoesHereCatch ex As Exception MsgBox(ex.ToString)End TryTara Kizeraka tduggan |
|
|
|
ryguy1410
Starting Member
5 Posts |
Posted - 2006-02-07 : 18:04:51
|
| Thanks alot for the help! |
|
|
|
|
Data integrity using constraints