query about check constraint

Question

want to write a check constraint(while creating a table) which accepts value between 2 dates like ('25-oct-94' to '10-may-16')

Oh dear...which _vendor_ is your database, e.g. SQL Server, Oracle, etc.? — Tim Biegeleisen, Jul 23 '17 at 15:07
@TimBiegeleisen AFAIK it's even worst... it does not complain about them, but does not enforce them either. Making developers think they are OK since the create or alter statement have been completed successfully, but then just ignores the check constraint that might, of course, lead to corrupt data... — Zohar Peled, Jul 23 '17 at 15:11
@PrashantTendulkar sql is a language, not a product. people are asking you what is the product you are working with? Is it ms-access, oracle, MySql, Sql Server, PostgreSql, or something else I didn't mention? — Zohar Peled, Jul 23 '17 at 15:13
@TimBiegeleisen, yes as Zohar said CHECk constraint is present in MySQL but Db engine won't enforce it at all and will ignore simply. So it's almost useless — Rahul, Jul 23 '17 at 15:15
it gives error :- "date or system variable wrongly specified in CHECK constraint " create table dob5 ( birthdate date, CONSTRAINT check_dates CHECK (birthdate BETWEEN '25-oct-94' AND '10-may-16') ) this is how i tried – — Prashant Tendulkar, Jul 23 '17 at 15:25
@Rahul for what purpose, other then evolution by natural selection, are check constraints good for in MySql? I would say not only are they useless, they are even harmful in MySql... — Zohar Peled, Jul 23 '17 at 15:26
want to write a check constraint(while creating a table) which accepts value before the year of 2000.. @AnkitBajpai — Prashant Tendulkar, Jul 23 '17 at 17:57
You can simply use the check constraint as `CHECK YEAR(date_col) <= 2000` — Ankit Bajpai, Jul 23 '17 at 18:15
this line gives error ORA-00906: missing left parenthesis @AnkitBajpai — Prashant Tendulkar, Jul 23 '17 at 18:33
Now you have to post your whole Create table statement. Without that it is not possible for us to help you. — Ankit Bajpai, Jul 24 '17 at 10:46

Rahul · Answer 1 · 2017-07-23T16:03:13.417

1

Since you are using Oracle you can use CHECK constraint saying

CONSTRAINT check_dates
  CHECK (my_date_column BETWEEN date '1994-10-25' AND date '2016-05-10')

Your query (as in comment) should be like below

 create table dob5 ( birthdate date not null, 
                    CONSTRAINT check_dates 
                    CHECK (birthdate BETWEEN date '1994-10-25' AND date '2016-05-10') );

See this demo fiddle http://sqlfiddle.com/#!4/779f9

edited Jul 23 '17 at 16:03

answered Jul 23 '17 at 15:17

Rahul

76,197
13
71
125

it gives error @Rahul :- "date or system variable wrongly specified in CHECK constraint " create table dob5 ( birthdate date, CONSTRAINT check_dates CHECK (birthdate BETWEEN '25-oct-94' AND '10-may-16') ) this is how i tried – Prashant Tendulkar Jul 23 '17 at 15:30
? are you there ? – Prashant Tendulkar Jul 23 '17 at 15:52
@PrashantTendulkar, see edit in answer if helps – Rahul Jul 23 '17 at 16:03
@PrashantTendulkar - you got that error because you did not create the constraint exactly as Rahul showed. You missed the `date` keyword, which is crucial when declaring date literals. – APC Jul 23 '17 at 16:40

score 0 · Answer 2 · answered Jul 23 '17 at 16:02

0

please check this.

create table t

( your_columnnm date

check( your_columnnm between date '1994-10-25' and date '2016-05-10' ));

answered Jul 23 '17 at 16:02

Ganesh

486
3
8
18

hey can u tell me query for sql accept the date which is less than year 2000 using check constraint ? – Prashant Tendulkar Jul 23 '17 at 17:50

query about check constraint

2 Answers2