Home » SQL & PL/SQL » SQL & PL/SQL » Constraint problem for flag (Oracle 11)
Constraint problem for flag [message #642134] Thu, 03 September 2015 08:38 Go to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
I have a table with the following data:

Id Car. Value Date..... Is_active_row_flag
== ==== ===== ========= ==================
01 Ford 95000 05-Mar-15 Y
02 Ford 10000 05-Mar-15 NULL
03 Ford 11000 05-Jan-15 NULL
04 Audi 15000 01-Jan-15 Y
05 Audi 18000 03-Aug-14 NULL
06 Audi 20000 05-Sep-14 NULL
07 Audi 22000 01-Sep-14 NULL

Id is the Primary Key (surrogate column from a sequence), no other indexes or constraints. Date is not unique within the Car make.

I want to create a constraint/index to enforce that only one row with Ford has a 'Y' value for Is_active_row_flag and the same for Audi etc. Please does anyone know how to do this?

(Please don't ask me for exact ddl Sad as it's not relevant and I've simplified this problem down). thanks
Re: Constraint problem for flag [message #642135 is a reply to message #642134] Thu, 03 September 2015 08:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could provide the DDL and INSERTs for your demo table, you know. It's a bit difficult to test a soution without that.
Re: Constraint problem for flag [message #642136 is a reply to message #642134] Thu, 03 September 2015 08:51 Go to previous messageGo to next message
bugfox
Messages: 18
Registered: October 2010
Junior Member
create unique index cars_uk on cars(car, nvl(is_active_row_flag, id))
Re: Constraint problem for flag [message #642137 is a reply to message #642135] Thu, 03 September 2015 09:01 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
Sorry!

drop table cars;

CREATE TABLE cars
(
ID NUMBER(28,0) NOT NULL ENABLE,
car VARCHAR2(10) NOT NULL,
value NUMBER(28,0) NOT NULL ENABLE,
cr_date DATE NOT NULL ENABLE,
is_curr_flag VARCHAR2(1),
CONSTRAINT PK_CARS PRIMARY KEY (ID)
) ;
INSERT INTO CARS VALUES( 01, 'Ford', 95000, '05-Mar-15', 'Y' );
INSERT INTO CARS VALUES( 02, 'Ford', 10000, '05-Mar-15', NULL );
INSERT INTO CARS VALUES( 03, 'Ford', 11000, '05-Jan-15', NULL );
INSERT INTO CARS VALUES( 04, 'Audi', 15000, '01-Jan-15', 'Y' );
INSERT INTO CARS VALUES( 05, 'Audi', 18000, '03-Aug-14', NULL );
INSERT INTO CARS VALUES( 06, 'Audi', 20000, '05-Sep-14', NULL );
INSERT INTO CARS VALUES( 07, 'Audi', 22000, '01-Sep-14', NULL );
Re: Constraint problem for flag [message #642138 is a reply to message #642137] Thu, 03 September 2015 09:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Bugfox's soltion works nicely with that test case. You owe him a beer Smile
Re: Constraint problem for flag [message #642139 is a reply to message #642138] Thu, 03 September 2015 09:10 Go to previous message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
Thanks Bugfox
Previous Topic: Top 2 Salary from Each Dept without using Analytic Function
Next Topic: to derive previous dates and ignore first date
Goto Forum:
  


Current Time: Fri Mar 29 02:59:35 CDT 2024