I really had a hard time knowing what words to put into the title of my question, as I am not especially sure if there is a database pattern related to my problem. I will try to simplify matters as much as possible to get directly to the heart of the issue.

Suppose I have some tables. The first one is a list of widget types:

create table widget_types (
    widget_type_id number(7,0) primary key,
    description varchar2(50)
);

The next one contains icons:

create table icons (
    icon_id number(7,0) primary key,
    picture blob
);

Even though the users get to select their preferred widget, there is a predefined subset of widgets that they can choose from for each widget type.

create table icon_associations (
    widget_type_id number(7,0) references widget_types,
    icon_id number(7,0) references icons,
    primary key (widget_type_id, icon_id)
);

create table icon_prefs (
    user_id number(7,0) references users,
    widget_type_id number(7,0),
    icon_id number(7,0),
    primary key (user_id, widget_type_id),
    foreign key (widget_type_id, icon_id) references icon_associations
);

Pretty simple so far.

Let us now assume that if we are displaying an icon to a user who has not set up his preferences, we choose one of the appropriate images associated with the current widget. I'd like to specify the preferred icon to display in such a case, and here's where I run into my problem:

alter table icon_associations 
    add ( is_preferred char(1) check( is_preferred in ('y','n') ) )
;

I do not see how I can enforce that for each widget_type there is one, and only one, row having is_preferred set to 'y'.

I know that in MySQL, I am able to write a subquery in my check constraint to quickly resolve this issue. This is not possible with Oracle.

Is my mistake that this column has no business being in the icon_associations table? If not where should it go? Is this a case where, in Oracle, the constraint can only be handled with a trigger?

I ask only because I'd like to go the constraint route if at all possible.

Thanks so much for your help, Paul