How can I make a column valid or unique sequential

Question

I have seen several web searches and answers to this, but it seems that they are all related to submissions.

How to configure a column to allow NULL or a unique value?

In my case, there is a table with stock items in which only server items have a serial number. The remaining values ​​are zero. I would like to apply some kind of control against entering the same series.

In addition, I cannot redesign the architecture, as I am writing a new interface for a still live site.

Answer 1

? , , NULL.

Answer 2

MySQL UNIQUE, nullable, ( ). , MySQL, UNIQUE . SQL, PostgreSQL SQLite (, , Oracle UNIQUE, ).

SQL , SQL Server DB2; , , .

Answer 3

SQL Server UNIQUE, NULL, .

, NULL UNIQUE INDEX :

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

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

, INSERT UPDATE .

INSTEAD OF :

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

Answer 4

MS SQL Server 2008, . .

Answer 5

...

, nulls , INSERT UPDATE , , , NULL,

Answer 6

, , Postgres " ", . , .

Answer 7

?

SQL Server . , .

Answer 8

For Sql Server 2008, here is a copy of the filtered index associated with MattB. I am writing it here so that people do not miss it:

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