CODEWITHSUNDEEP
sqlsql-serverdatabase
user1725619
user1725619

Reputation:

I am stuck on creating constraint on SQL Server

I am totally new to SQL Server management, I tried to add a constraint in a table.

The situation is that I created a column with only 'Y'or 'N' allowed to be valued.

So I tried to create a constraint in Management Studio by right clicking "Constraints" in the table.

However, I really have no idea about the syntax of creating a constraint. Finally, I tried to input the code into "Check Constraint Expression" window by referring the template from the Internet. The SQL Server always tell me "Error Validating constaint".

Can you guys just help me to write the first constraint for me? Because I really dont know how to start.

My requirement is:

I have a table called "Customer"
I created a column called "AllowRefund"
The column "AllowRefund" is only allowed to 'Y' or 'N'

Thanks.

Upvotes: 0

Views: 3270

Answers (3)

user330315
user330315

Reputation:

I partly agree with JohnFix, but as knowing the correct syntax to define a check constraint might be useful for you in the future (as you apparently don't read manuals), here is the SQL to create such a constraint:

alter table customer 
  add constraint check_yes_no check (AllowRefund in ('Y', 'N'));

You probably want to also define the column as NOT NULL in order to make sure you always have a value for that.

(As I don't use "Management Studio" I cannot tell you where and how you have to enter that SQL).

Upvotes: 1

JohnLBevan
JohnLBevan

Reputation: 24430

You can do this as follows:

ALTER TABLE Customer 
ADD CONSTRAINT CK_Customer_AllowRefund
CHECK (AllowRefund in ('Y','N'))

However @JohnFx is correct - you'd be better off making this column a bit field.

Upvotes: 1

JohnFx
JohnFx

Reputation: 34909

I'd advise against what you are trying to do. There is a datatype (Bit) that is designed to represent values with two states. If you use this type you won't even need the constraint at all. SQL Server will enforce the value to be either one or zero with no additonal work required. You just have to design your app to treat 1 as yes, and 0 as No.

The approach you are attempting is just not a good idea and no good will come of it.

Upvotes: 1

Related Questions