CODEWITHSUNDEEP
sql-server
G S M
G S M

Reputation: 29

how to add constraint to check values in table column

I have a table etblshopaccount to that i want to add constraint so that while exporting the data from excel to sql server native client the column shopaccountcity should be added with cityname 'texas' if there is no value in the excel data.i.e.,

shopaccountcity is not null column and i want to add texas by default to the column if the field has no value in the excel.

i came up with this query is successful but there is no result 

alter table EtblShopAccount add default (isnull(null,'Texas')) for [ShopAccountCity]

Upvotes: 2

Views: 237

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Try this:

ALTER TABLE EtblShopAccount 
 add constraint df_ShopAccountCity 
 default 'Texas' for [ShopAccountCity]

i.e., instead of using the function isnull() you can better add it as a constraint. So it means whenever you are not going to provide the value for your ShopAccountCity column it will take the default value as Texas

Also you can use the UPDATE script if you want to update the NULL values to 'Texas' before doing running the wizard

update EtblShopAccount 
set ShopAccountCity = 'Texas'
where ShopAccountCity is NULL

Upvotes: 0

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

Modify to this:

ALTER TABLE EtblShopAccount 
ADD CONSTRAINT ConstraintName DEFAULT N'Texas' FOR [ShopAccountCity];

Upvotes: 1

Related Questions