CODEWITHSUNDEEP
sqlsql-server
Jan W.
Jan W.

Reputation: 1771

How can I make a column allow null or unique serial

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.

Upvotes: 3

Views: 2961

Answers (8)

Serge Wautier
Serge Wautier

Reputation: 21878

For Sql Server 2008, here's a copy of the filtered index solution linked by MattB. I write it here to make sure people dont miss it:

CREATE UNIQUE NONCLUSTERED INDEX [UniqueExceptNulls]
ON [Customers] (SSN)
WHERE [SSN] IS NOT NULL

Upvotes: 0

Matt H
Matt H

Reputation: 256

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.

Upvotes: 2

Alex Martelli
Alex Martelli

Reputation: 881665

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.

Upvotes: 6

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89661

What platform is this for?

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

Upvotes: 0

Brian L
Brian L

Reputation: 10955

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.

Upvotes: 0

Eoin Campbell
Eoin Campbell

Reputation: 44268

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

Upvotes: 0

northpole
northpole

Reputation: 10346

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

Upvotes: 0

Related Questions