Unique constraints - Quick question, quick answer greatly appreciated

[Horace]

I have a column which is NULLABLE.

When a new varchar value is inserted into the column, it has to be unique.

How do I go about this?

I can't create the unique constraint as it complains about the NULLs in the column.

For example.........

I have 2 rows with column A set to NULL
I insert row 3 with the value 'red'
I insert row 4 with the value 'blue'
I insert row 5 with the value 'red' - I need the rdbms to prohibit this!!
I insert row 6 with a NULL value - this is okay

Can this be done? A quick answer would really be appreciated

Many thanks

[davey23uk]

a unique index allows nulls

SQL> create table dave (text varchar2(100));

Table created.

SQL> create unique index dave_id on dave(text);

Index created.

SQL> insert into dave values ('abcd');

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> select count(*) from dave;

  COUNT(*)
----------
         2

[Horace]

Thanks Dave,

I'm trying to sort out our developers over the phone. I have no access to a database to try this, hence having to rely on the community.

What happens if you try and add another NULL to your table?

[davey23uk]

SQL> create table dave (text varchar2(100));

Table created.

SQL> create unique index dave_id on dave(text);

Index created.

SQL> insert into dave values ('abcd');

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> insert into dave values (null);

1 row created.

SQL> commit;

Commit complete.

SQL>

If its actually a primary key and not a unique index then it wont allow nulls

[Horace]

Thanks as always Dave.