Check Constraints in SQL

Question

I want not to allow my DB user to enter bigger dates than 2017-03-18. How can add this constraint to my table? Is this Correct?

(Year([ContractEnd])<2017) and (Month([ContractEnd])<03) and (Day([ContractEnd])<18)

No that is not right. The values in your code don't match the date that you stated. And there is no need to break apart a date(time) like that do check. — Sean Lange
– Sean Lange, Commented Jun 13, 2017 at 15:35

SqlZim · Accepted Answer · 2017-06-13 15:42:34Z

4

You can add a constraint like that to an existing table like so:

alter table t add constraint chk_ContractEnd_lt_20170319 
  check (ContractEnd<'20170319');

rextester demo: http://rextester.com/FQWFMI88817

create table t (
    id int not null identity(1,1)
  , ContractEnd date
  /* at table creation */
  , constraint chk_ContractEnd_lt_20170319 check (ContractEnd<'20170319')
)
alter table t drop constraint chk_ContractEnd_lt_20170319;
/* to existing table */
alter table t add constraint chk_ContractEnd_lt_20170319 
  check (ContractEnd<='20170318');


insert into t values ('20161231')
insert into t values ('20170318')
/* all good */

insert into t values ('20170319')  
/* -- Error, constraint violation */

edited Jun 13, 2017 at 15:42

answered Jun 13, 2017 at 15:36

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bacon Bits Over a year ago

If the datatype is datetime or datetime2, then using ContractEnd < '20170102' might be necessary.

SqlZim Over a year ago

@BaconBits That's true.

Roman Marusyk · Accepted Answer · 2017-06-13 15:36:10Z

0

Try

[ContractEnd] DATE CHECK ([ContractEnd]  <= '20170318')

answered Jun 13, 2017 at 15:36

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

4 Comments

kwram Over a year ago

I tried what you said, but here is the error: The ALTER TABLE statement conflicted with the CHECK constraint "CK_Professor". The conflict occurred in database "University", table "dbo.Professor", column 'ContractEnd'.

Roman Marusyk Over a year ago

@kwram Of course, you should remove previous constraint with name CK_Professor. After that use alter table Professor add constraint . do you know how to remove it?

kwram Over a year ago

You are right, but I haven't already set any constraint on this column! I don't have a constraint on this column.

Roman Marusyk Over a year ago

@kwram no, you have according to the error message. Check

SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'CK_Professor') AND parent_object_id = OBJECT_ID(N'Professor')

Collectives™ on Stack Overflow

Check Constraints in SQL

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related