Home » SQL & PL/SQL » SQL & PL/SQL » Unique Constraint on a Table
Unique Constraint on a Table [message #287863] Thu, 13 December 2007 19:06 Go to next message
Prasad01
Messages: 22
Registered: August 2007
Junior Member
I want to have a unique constraint on deptno and emp_name in emp table.
What I want is:
Any Dept can have 100 Steves OR 100 Johns but only 1 Robert
Dept No Emp Name
10 Steve
10 Steve
10 Robert
20 Steve
20 Robert
20 John
20 John

How can we achieve this with unique constraint??

Cheers
Prasad.
Re: Unique Constraint on a Table [message #287933 is a reply to message #287863] Fri, 14 December 2007 01:26 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I stole my solution from what Mr. Kyte published in Oracle Magazine. The table below will accept only one occurrence per department (deptno) with 'MHE' in the name.

CREATE TABLE mhe_foo( id         NUMBER
                    , deptno     NUMBER
                    , lastname   VARCHAR2(20)
                    , CONSTRAINT mhe_pk PRIMARY KEY (id)
                    )
/

CREATE UNIQUE INDEX mhe_name ON mhe_foo (deptno, CASE WHEN lastname  = 'MHE' THEN 'MHE' ELSE TO_CHAR(id) END)
/

INSERT INTO mhe_foo(id, deptno, lastname) VALUES (1, 1, 'MHE');
INSERT INTO mhe_foo(id, deptno, lastname) VALUES (2, 1, 'James');
INSERT INTO mhe_foo(id, deptno, lastname) VALUES (3, 1, 'James');
INSERT INTO mhe_foo(id, deptno, lastname) VALUES (4, 1, 'James');
INSERT INTO mhe_foo(id, deptno, lastname) VALUES (5, 2, 'MHE');
INSERT INTO mhe_foo(id, deptno, lastname) VALUES (6, 2, 'MHE');

SELECT * FROM mhe_foo
/
DROP TABLE mhe_foo PURGE
/


MHE

[Updated on: Fri, 14 December 2007 01:40]

Report message to a moderator

Previous Topic: records
Next Topic: Transer sql query into oracle function
Goto Forum:
  


Current Time: Sat Dec 03 22:38:37 CST 2016

Total time taken to generate the page: 0.04947 seconds