Conditional Unique Constraints

Well here was the chalenge, I wanted to be able to mark a record as deleted, what is so tricky with that?

Well the record has a unique constraint on everything apart from the id column and the delete column, so if I want to insert an identical row again it will throw up a uniqueness exception. If I've marked a row as deleted it still wont let me insert the same row again, so you would not be able to insert row 2 in the following example:

ID	data	delete
1	ud1	1
2	ud1	0

So what you need is a Function Based Index, FBIs have been around since Oracle 8i so they are not a new thing. A normal unique index will allow you to create a unique constraint on multiple columns however a unique contraint using FBIs will only allow one input so you have to concatonate all of the columns together into one string.

Anyway this is some code that should get you up and running, I only used one condition to test the delete column but you could use as many a you want to really:

CREATE UNIQUE INDEX U01
ON [ TABLENAME ] (
CASE WHEN [ CONDITION 1 ]
THEN [ COLUMN 1 ]||[ COLUMN 2 ]||[ COLUMN N ]
CASE WHEN [ CONDITION 2 ]
THEN [ COLUMN 1 ]||[ COLUMN 2 ]||[ COLUMN N ]
ELSE
NULL;
END);

Server Side Image Map

I found an interesting PL/SQL feature today, when you use the

htp.formImage

procedure in a PL/SQL form it will return the value of the form image and also the coordinates of the click made on that image, so you get 3 variables returned.

• name - the value
• name.x - x coordinate
• name.y - y coordinate

So you could then use this information to create server side image maps, these could be created dynamically from a database table and updated easily by users.

User Centered Design

A colegue put me on to this really great infographic User Centered Design today.


This was researched and designed by Pascal Raabe right here in Bristol and can be applied to design in general but is definitely applicable to software design.

Sharepoint Lists

I had observed a bit of inconsistency between SharePoint 2007 List Views, some were showing

(More Items...)

At the bottom of the list and some were not, why? Well it turned out to be stupidly simple, all you had to do is initially switch your list view to

< summary >

This will initialy remove all the columns that you had in your view, click the Edit the current view link and add those columns back in again and hit apply.

Personaly I think it's a better option to set up a view that allows you to click through to the next set of records in batches of the specified size, but, if you NEED a (More Items...) link that is how you would do it.