Do I need to write a trigger for such a simple constraint?

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) che开发者_StackOverflow中文版ck( 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

One simple way that I believe solves your issue is to have another table called

icon_default_associations (widget_type_id, icon_id)

and simply make (widget_type_id, icon_id) obey a UNIQUE constraint (or make it the primary key).

I think you're overloading the purpose of icon_associations if you do it the way you're attempting.

If the default icons don't change, could you just make sure that the default icon goes into the icon_associations table first?

That way, you are always guaranteed ONE and ONLY one default icon, it will never go away as long as there ARE icons, and you don't need to futz with an is_preferred column, or its constraints and triggers.

It would also be easy to retrieve the default icon:

SELECT * from icon_associations WHERE ID=MIN(ID)

The downside is that changing the default icon would require a little bit of work, but if that never happens, this solves your is_preferred column issue.