Can someone help? Im trying to create then modify a table. INSERT statement conflicted with the CHECK constraint "chk_Sex"

Question

I am attempting to create a table then add and modify it. Below is how I created the table. The other part is the first record I attempted to add to the the table that has given me the check constrain Error

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "chk_Sex". The conflict occurred in database "MHaynes_F22", table "dbo.DogLicense", column 'Sex'. The statement has been terminated.

CREATE TABLE DogLicense

(

License int identity (1,1) primary key Not Null, Expires date, Sex nvarchar(15), PetName nvarchar(20), Breed nvarchar(20), OwnerLastName nvarchar(30), OwnerFirstName nvarchar(30), Address nvarchar(50), Zip nvarchar(5), Phone nvarchar(10),

CONSTRAINT chk_Sex CHECK (Sex IN ('M(Male)', 'F(Female)','NM(Neutered Male)','SF(Spayed Female)')), CONSTRAINT chk_Expires CHECK(Expires > '01/01/1990'))

this is the first records I attempted to insert

insert DogLicense values('06/21/2023','NM','Rosco','St.Bernard','Freeman','Mark','123 Meadow Ln.','99207','(509) 555-1212')

you have to enter 'NM(Neutered Male)' but you are entering 'NM' — tinazmu, Nov 22 '22 at 03:40
Thank you that worked !! I was thinking the instructions were to include both. — nightowl898, Nov 22 '22 at 04:15
'Instructions'? do you mean 'allow both variations'? if so, it will make it very difficult to search/report on it. — tinazmu, Nov 23 '22 at 01:02

score 0 · Answer 1 · answered Nov 22 '22 at 19:40

Check constraints are strict. Because you asked it to accept only 'NM(Neutered Male)' (and others but not 'NM') you can only use that value:

insert DogLicense 
values('06/21/2023','NM(Neutered Male)','Rosco','St.Bernard','Freeman','Mark','123 Meadow Ln.','99207','(509) 555-1212')

Can someone help? Im trying to create then modify a table. INSERT statement conflicted with the CHECK constraint "chk_Sex"

1 Answers1