Constraints [message #288439] |
Mon, 17 December 2007 09:56 |
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 #288469 is a reply to message #288439] |
Mon, 17 December 2007 12:36 |
|
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 |
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 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Hmm, (as so often) Barbara's solution is way more elegant
[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
|
|
|
|
|