Home » SQL & PL/SQL » SQL & PL/SQL » Advance Integration Implementation (Oracle10g, Win2003)
Advance Integration Implementation [message #331017] Wed, 02 July 2008 02:54 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

HI

I have to enforce integrity as follows

create table part
( parts varchar2(30),
part_type varchar2(30),
an_fk_col number,
part_use char(1)
)

insert into part(parts,part_type,an_fk_col) values('P1','abc',1);
insert into part(parts,part_type,an_fk_col) values('P1','abc',2);
insert into part(parts,part_type,an_fk_col) values('P1','pqr',2);

insert into part(parts,part_type,an_fk_col) values('P2','abc',4);
insert into part(parts,part_type,an_fk_col) values('P2','abc',1);
insert into part(parts,part_type,an_fk_col) values('P2','xyz',1);

insert into part(parts,part_type,an_fk_col) values('P3','abc',3);
insert into part(parts,part_type,an_fk_col) values('P3','abc',1);
insert into part(parts,part_type,an_fk_col) values('P3','klm',1);

commit;

PARTS	PART_TYPE	AN_FK_COL	PART_USE
P1	abc		1	
P1	abc		2	
P1	pqr		2	
P2	abc		4	
P2	abc		1	
P2	xyz		1	
P3	abc		3	
P3	abc		1	
P3	klm		1	



Now i have to enforce following 3 integrity on "PART_USE"

1. "PART_USE" must be in (T,F)
2. "PART_USE" is null if "PART_TYPE" != 'abc' -- only T,F both are only for 'abc'.
3. there is only 0 or 1 "T" against against a distinct value of "PARTS" -- i.e. in all the rows with PARTS =P1 there is only 0 or 1 rows with "PART_USE" = T

Please advise how can i enforce this complex integrity.

Wishes
J a w a d
Re: Advance Integration Implementation [message #331030 is a reply to message #331017] Wed, 02 July 2008 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can enforce 1) with a standard check constraint.
For 2) you need a trigger that will fire on Insert or update of Parts.

For 3), I think your best bet is a unique function based index on CASE WHEN part_use = 'T' then parts ELSE null END
Re: Advance Integration Implementation [message #331172 is a reply to message #331030] Wed, 02 July 2008 08:20 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Completed the 1st two still looking for 3rd
Re: Advance Integration Implementation [message #331175 is a reply to message #331172] Wed, 02 July 2008 08:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What's the problem with 3)?
Re: Advance Integration Implementation [message #331176 is a reply to message #331172] Wed, 02 July 2008 08:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's wrong with JRowbottom's suggestion?
Re: Advance Integration Implementation [message #331184 is a reply to message #331176] Wed, 02 July 2008 08:49 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Nothing wrong with JRowbottom suggestion but i fail to build a function base index as its conflicting with 2nd.
Re: Advance Integration Implementation [message #331186 is a reply to message #331184] Wed, 02 July 2008 09:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Will
CREATE UNIQUE INDEX part_unq_idx_1 ON PART (case when part_use = 'T' then parts else null end);
not do the job?

[Updated on: Wed, 02 July 2008 09:13]

Report message to a moderator

Re: Advance Integration Implementation [message #331188 is a reply to message #331186] Wed, 02 July 2008 09:30 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why do we need an FBI why not a unique index on (parts, part_use) ?

Regards

Raj
Re: Advance Integration Implementation [message #331189 is a reply to message #331186] Wed, 02 July 2008 09:30 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

JRowbottom

Thanks for your quick help and Thanks for your easy fish now its working Smile

Wishes
Re: Advance Integration Implementation [message #331192 is a reply to message #331188] Wed, 02 July 2008 09:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The restriction only seems to apply if the Part_Type is 'T' - you can have a many part_type 'F' records as you like.
Re: Advance Integration Implementation [message #331197 is a reply to message #331188] Wed, 02 July 2008 09:51 Go to previous message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

--Why do we need an FBI why not a unique index on (parts, part_use) ?

Just as JRowbottom say ,as we can have many F so unique index is not the solution and i must have to use FBI.
Previous Topic: query for count
Next Topic: Select private procedures from data dictionary
Goto Forum:
  


Current Time: Sun Dec 04 08:36:34 CST 2016

Total time taken to generate the page: 0.21130 seconds