CODEWITHSUNDEEP
sqlsql-serverconstraints
SaraD
SaraD

Reputation: 53

SQL - Only accept capital letters by constraint

ALTER TABLE TblPersoon
    ADD Geslacht char(1) NULL
    CONSTRAINT CKGeslacht CHECK (Geslacht in ('M', 'V'))

When I execute this query, afterwards it is still possible to insert 'm' or 'v' in the column called 'Geslacht' in TblPersoon. How can I make sure that only capital letters are accepted?

I know I should be able to do this in SQL Server Management Studio, but where?

enter image description here

Upvotes: 3

Views: 1224

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can define explicit collation like that :

ALTER TABLE TblPersoon
ADD Geslacht char(1) NULL
CONSTRAINT CKGeslacht CHECK (Geslacht collate SQL_Latin1_General_CP1_CS_AS in ('M', 'V'))

However, i have used default collation case-sensitive type SQL_Latin1_General_CP1_CS_AS you can change it accordingly.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You need a case-sensitive collation. You can define the column that way, or do it in the check constraint:

ALTER TABLE TblPersoon
    ADD Geslacht char(1) NULL
    CONSTRAINT CKGeslacht CHECK (Geslacht collate Latin1_General_CS_AS in ('M', 'V'));

or:

ALTER TABLE TblPersoon
    ADD Geslacht char(1) collate Latin1_General_CS_AS
    CONSTRAINT CKGeslacht CHECK (Geslacht in ('M', 'V'));

Collation in general is a bit of a tricky subject. It involves the relationship between different characters. In SQL Server it also seems to be conflated with the character set ("code pages"). I do which that basic things like case-sensitive vs. case-insensitive comparisons did not require using them. You can learn more about it in the documentation.

Upvotes: 4

Related Questions