• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

How to Set a Check Constraint on a Column? - MS SQL Server

CREATE TABLE EmpHiringInfo
(
      employeeID int NOT NULL CONSTRAINT employeeid_fk REFERENCES Employee(employeeID) ON DELETE CASCADE,
      gender         char(1) CONSTRAINT gender_ck CHECK ('M', 'F'),
      CONSTRAINT empid_fk_pk PRIMARY KEY(employeeID)
);

When i run the code, i get this error that points to gender's check constraint:

An expression of non-boolean type specified in a context where a condition is expected, near ','.
0
F-J-K
Asked:
F-J-K
  • 3
  • 2
2 Solutions
 
krunal_shahCommented:
try like this,
CREATE TABLE EmpHiringInfo
(
      employeeID int NOT NULL CONSTRAINT employeeid_fk REFERENCES Employee(employeeID) ON DELETE CASCADE,
      gender         char(1),
      CONSTRAINT empid_fk_pk PRIMARY KEY(employeeID)
);



ALTER TABLE EmpHiringInfo ADD CONSTRAINT gender_ck CHECK (gender == 'M' OR gender == 'F' )
0
 
Om PrakashCommented:
CREATE TABLE EmpHiringInfo
(
      employeeID int NOT NULL CONSTRAINT employeeid_fk REFERENCES Employee(employeeID) ON DELETE CASCADE,
      gender         char(1) CONSTRAINT gender_ck CHECK (gender in ('M', 'F')),
      CONSTRAINT empid_fk_pk PRIMARY KEY(employeeID)
)
0
 
F-J-KAuthor Commented:
Ok, i run this:

ALTER TABLE EmpHiringInfo
(
      employeeID int NOT NULL CONSTRAINT employeeid_fk REFERENCES Employee(employeeID) ON DELETE CASCADE,
      gender         char(1) CONSTRAINT gender_ck CHECK (gender in ('M', 'F')),
      CONSTRAINT empid_fk_pk PRIMARY KEY(employeeID)
);

Now, i get another error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

I don't see where i went wrong
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
krunal_shahCommented:
have you tried this,
ALTER TABLE EmpHiringInfo ADD CONSTRAINT gender_ck CHECK (gender in ('M', 'F'))
0
 
F-J-KAuthor Commented:
@krunal_shah:

Error presists. For some reason, i had to drop the table and then create it again. The check constraint worked! Thanks
0
 
F-J-KAuthor Commented:
Solved! Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now