Home » RDBMS Server » Server Administration » Regarding Check constraint in Date field
Regarding Check constraint in Date field [message #372684] Sat, 03 March 2001 01:15 Go to next message
ramu
Messages: 82
Registered: February 2001
Member
Hi Friends,

I want to create a table DOB with a column dd(Date).
And I have to create a check constraint for that column. The costraint should check the input value and only it should allow the user to input values in that column having age>18.

I tried that using check (to_char(sysdate,'yyyy')-to_char(dd,'yyyy')>18) but I got

"Date or system variable wrongly specified in the CHECK constraint"

can any one help me..
Plz..

Thanks..

Ramu..

Report message to a moderator

Re: Regarding Check constraint in Date field [message #372696 is a reply to message #372684] Sat, 03 March 2001 11:02 Go to previous messageGo to next message
Sudhir M. Pongurlekar
Messages: 8
Registered: January 2001
Junior Member
select months_between(sysdate,hiredate) from emp
where months_between(sysdate,hiredate)>204

Report message to a moderator

Re: Regarding Check constraint in Date field [message #372697 is a reply to message #372696] Sat, 03 March 2001 11:30 Go to previous messageGo to next message
ramu
Messages: 82
Registered: February 2001
Member
Hello Friend,

I realy want to create a check constraint while creating the table. I do not want to select as your query. I have again tried to create a check constraint like..

CREATE TABLE my_table(my_column DATE CHECK(MONTHS_BETWEEN(SYSDATE,my_column))204);

But I got the error as

ORA-02436: date or system variable wrongly specified in CHECK constraint..

So Could you help me in my effort..

Thanks..

Ramu..

Report message to a moderator

Re: Regarding Check constraint in Date field [message #372701 is a reply to message #372697] Sat, 03 March 2001 13:22 Go to previous messageGo to next message
Sudhir M. Pongurlekar
Messages: 8
Registered: January 2001
Junior Member
TRY THIS
(ENAME varchar2(20),
DOB DATE,
TODAY DATE,
constraint dob CHECK(MONTHS_BETWEEN(today,dob) > 204))
[[TODAY = SYSDATE]]

Report message to a moderator

Re: Regarding Check constraint in Date field [message #372705 is a reply to message #372697] Sun, 04 March 2001 04:54 Go to previous messageGo to next message
ramu
Messages: 82
Registered: February 2001
Member
Hello Sudhir,

I tried as your advice, the statement is as follows:

CREATE TABLE DOB(TODAY DATE,DD DATE CONSTRAINT CKDOB CHECK(MONTHS_BETWEEN(TODAY,DD)>204));

But I got the error message as follows:

ORA-02436: Column check constraint can not reference other columns.

So what can I do?

Thanks..

Ramu..

Report message to a moderator

Re: Regarding Check constraint in Date field [message #372715 is a reply to message #372697] Mon, 05 March 2001 01:00 Go to previous messageGo to next message
Sudhir M. Pongurlekar
Messages: 8
Registered: January 2001
Junior Member
Ramu

CREATE TABLE DOB(TODAY DATE,DD DATE CONSTRAINT CKDOB
CHECK(MONTHS_BETWEEN(TODAY,DD)>204));

Ur satement is correct only there is a comma missing after DD DATE , modify ,it will work.

CREATE TABLE DOB(TODAY DATE,DD DATE, CONSTRAINT CKDOB
CHECK(MONTHS_BETWEEN(TODAY,DD)>204));

Sudhir.

Report message to a moderator

you cannot with constraint [message #372719 is a reply to message #372697] Mon, 05 March 2001 01:59 Go to previous message
deva
Messages: 5
Registered: March 2001
Junior Member
u cannot check the user id ,system date by constraint.this is the one reason that we go for trigger.

Report message to a moderator

Previous Topic: Re: who to search patterns ([0-9] or [a-z]) in sql
Next Topic: import gets unknown command error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 29 04:31:02 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.