constraint - at most one record has status 'active' in a table.
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
Thank in advance. I have a table like this:
CREATE TABLE FIFAPPS.FS_MST_SUPPLIER
(
SUPL_CODE VARCHAR2(12 BYTE) NOT NULL,
SUPL_STATUS VARCHAR2(12 BYTE) DEFAULT 'ACTIVE' NOT NULL
)
How to make constraint such that at most one supl_code has supl_status = 'ACTIVE' in the table.
For example:
-- BELOW IS NOT CORRECT AS THERE ARE TWO RECORDS WITH SUPL_STATUS='ACTIVE'
record 1 : (supl_code = '1',SUPL_STATUS='ACTIVE')
record 2 : (supl_code = '2',SUPL_STATUS='NOT ACTIVE')
record 3 : (supl_code = '3',SUPL_STATUS='ACTIVE')
-- -- BELOW IS CORRECT AS THERE IS ONLY ONE RECORD WITH SUPL_STATUS='ACTIVE'
record 1 : (supl_code = '1',SUPL_STATUS='ACTIVE')
record 2 : (supl_code = '2',SUPL_STATUS='NOT ACTIVE')
record 3 : (supl_code = '3',SUPL_STATUS='NOT ACTIVE')
-- -- BELOW IS CORRECT AS THERE IS NO RECORD WITH SUPL_STATUS='ACTIVE'
record 1 : (supl_code = '1',SUPL_STATUS='NOT ACTIVE')
record 2 : (supl_code = '2',SUPL_STATUS='NOT ACTIVE')
record 3 : (supl_code = '3',SUPL_STATUS='NOT ACTIVE')