Home » SQL & PL/SQL » SQL & PL/SQL » on check constraints... (oracle 10g 2003 server)
on check constraints... [message #421475] Tue, 08 September 2009 10:36 Go to next message
kang
Messages: 89
Registered: November 2007
Member
the composition of column1 and column2 is not unique itself.

I want some uniqueness to them with maybe check constraints(do you know better ways?)

is it possible to
alter table mytable 
add CONSTRAINT myconst CHECK ((select count(*) from mytable where not exists(select * from mytable where column1=this.column1)));

things like that.

I need some complex check constraints.
any ideas welcomed.

TIA.
Re: on check constraints... [message #421476 is a reply to message #421475] Tue, 08 September 2009 10:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
*If* that would work, how would that work different from an unique index on column1?
Re: on check constraints... [message #421477 is a reply to message #421476] Tue, 08 September 2009 10:49 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
(select count(*) from mytable where not exists(select * from mytable where column1=this.column1))

should be modified for some rules.
like
(select count(*) from mytable where not exists(select * from mytable where column1||substr(column2,1,4)=this.column1||substr(this.column2,1,4)))

I need complex check constraints.
Re: on check constraints... [message #421478 is a reply to message #421477] Tue, 08 September 2009 11:02 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
why not try with UNIQUE INDEX on COL1 & COL2?
Re: on check constraints... [message #421479 is a reply to message #421475] Tue, 08 September 2009 11:06 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
CREATE UNIQUE INDEX unq_idx ON mytable(column1, substr(column2,1,4));


Though I'd suggest that column2 should probably be split into two columns.
Re: on check constraints... [message #421484 is a reply to message #421479] Tue, 08 September 2009 12:17 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Anyway, basically you can only use deterministic functions in check constraints.

In your example, there might be a case where the check would be passed, but after you change some data it wouldn't pass again.

That's the reason non-deterministic functions (like sysdate, for example) are not allowed in check-constraints.
Previous Topic: Calling a funcion in SQL Select stmt
Next Topic: Data upload
Goto Forum:
  


Current Time: Fri Dec 09 09:35:38 CST 2016

Total time taken to generate the page: 0.08189 seconds