How can I make a column allow null or unique serial

Question

I've seen a few web searches and responses to this but it seems they all involve views.

How can I adjust a column so that it only allows NULL or a unique value?

In my case there's a table with stock items in which only the server items have a serial number. The rest are null. I would like to enforce some kind of control against entering the same serials.

Also, I cannot redesign the architecture as I'm writing a new front end to a still live site.

Answer 1

Can you validate from the frontend? Before you insert or commit your data make sure it is unique or NULL.

Answer 2

In MySQL, a UNIQUE column that's also nullable allows any number of nulls (in all engines). So, if you're using MySQL, just add a UNIQUE constraint to the column of interest. This behavior is the SQL standard and is also supported by PostgreSQL and SQLite (and apparently Oracle for single-column UNIQUE constraint only, though I can't confirm this).

However, this SQL standard behavior won't necessarily work for all other RDBMS engines, such as SQL Server or DB2; if you specify what engines you need to support, we may be able to offer more specific suggestions.

Answer 3

SQL Server allows creating UNIQUE indexes that accept NULL values, though it takes a little trick.

Create a view that selects only non-NULL columns and create the UNIQUE INDEX on the view:

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Note that you'll need to perform INSERT's and UPDATE's on the view instead of table.

You may do it with an INSTEAD OF trigger:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END

Answer 4

You could do it with a Trigger instead...

Index the column but without a unique constraint Allow nulls Stick a trigger on the table that on INSERT and UPDATE you check if the column value your attempting to insert is either NULL or doesn't already exist in the table

Answer 5

I don't know what database you're using, but in Postgres, you can define a "before insert or update" trigger that can return a special value to reject the insert. You could use it to enforce a constraint like this on your table if there isn't a native way to do it.

Answer 6

What platform is this for?

For SQL Server see this blog post which shows a technique using indexed views.

Answer 7

If you're using MS SQL Server 2008, then you can use a filtered unique index to achieve this. Have a look at this forum thread for details.