Home » SQL & PL/SQL » SQL & PL/SQL » Constraints
Constraints [message #288439] Mon, 17 December 2007 09:56 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good Day,

Can you please help me regarding this.

I need to create the constraint like these combination.

Table : EMP.
SQL> desc emp;
 Name                          Null?       	Type
 ------------------------- ---------------   	----
 EMPNO                    NOT NULL    NUMBER(4)
 ENAME                                     	VARCHAR2(10)
 JOB                                       	VARCHAR2(9)
 HIREDATE                                  	DATE
 DEPTNO                                    	NUMBER(2)
 SAL                                       	NUMBER

I need to create the constraint when I am getting the new values, if the JOB is like A the DEPTNO should not be NULL.

Thanx & Reds
Thangam.

[Updated on: Mon, 17 December 2007 10:10] by Moderator

Report message to a moderator

Re: Constraints [message #288443 is a reply to message #288439] Mon, 17 December 2007 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to create a trigger for this.
A check constraint can only be on one column.

Regards
Michel
Re: Constraints [message #288451 is a reply to message #288443] Mon, 17 December 2007 10:32 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Yes Michel you are right.

Even i suggested same thing but the requirment is constraint.

Can you please help me.


Reds
Thangam.
Re: Constraints [message #288452 is a reply to message #288451] Mon, 17 December 2007 10:34 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

As micheal said.

Quote:

You have to create a trigger for this.


Try to create trigger and post it here whatever message you getting.
Re: Constraints [message #288469 is a reply to message #288439] Mon, 17 December 2007 12:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE emp_test
  2    (job	VARCHAR2 (9),
  3  	deptno	NUMBER (2))
  4  /

Table created.

SCOTT@orcl_11g> ALTER TABLE emp_test
  2  ADD CONSTRAINT deptno_not_null_if_job_a
  3  CHECK (DECODE (job, 'A', deptno, 0) IS NOT NULL)
  4  /

Table altered.

SCOTT@orcl_11g> INSERT INTO emp_test (job, deptno) values ('A', NULL)
  2  /
INSERT INTO emp_test (job, deptno) values ('A', NULL)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.DEPTNO_NOT_NULL_IF_JOB_A) violated


SCOTT@orcl_11g> INSERT INTO emp_test (job, deptno) values ('A', 1)
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO emp_test (job, deptno) values ('B', NULL)
  2  /

1 row created.

Re: Constraints [message #288470 is a reply to message #288443] Mon, 17 December 2007 12:36 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 17 December 2007 17:13

You have to create a trigger for this.
A check constraint can only be on one column.

Regards
Michel


Are you sure?
SQL> alter table emp add constraint faq_ck
  2  check
  3  ((instr(upper(job), 'A') > 0 and deptno is not null)
  4  or
  5  instr(upper(nvl(job, 'B')), 'A') = 0
  6  )
  7  /

Table altered.

SQL> insert into emp (empno, job, deptno) values (1, 'JAK', null);
insert into emp (empno, job, deptno) values (1, 'JAK', null)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.FAQ_CK) violated
Re: Constraints [message #288471 is a reply to message #288470] Mon, 17 December 2007 12:38 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Hmm, (as so often) Barbara's solution is way more elegant Smile

[Edit: wait... I interpreted the "if the JOB is like A" as a LIKE in the SQL meaning, not in the Californian-youth-slang meaning..

[Updated on: Mon, 17 December 2007 12:39]

Report message to a moderator

Re: Constraints [message #288474 is a reply to message #288470] Mon, 17 December 2007 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It seems my eyes (reading the SQL Reference) and my mind (don't remembering what I already did) were in vacation at this moment.
Sorry for this error.

Regards
Michel
Re: Constraints [message #288629 is a reply to message #288439] Tue, 18 December 2007 03:48 Go to previous message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Thanks to all My dear friends.

Once again Thanks a lot.

Thanx & Reds
Thangam
Previous Topic: how can we know the execution performance
Next Topic: Table Joins
Goto Forum:
  


Current Time: Sat Nov 09 19:57:20 CST 2024