0

Not exactly sure where to post this question. Is it necessary to make an unique indexed column not nullable, like this (using SQLAlchemy): 

name = db.Column(db.String(64), index=True, unique=True, nullable=False)  # <--

Or perhaps it's redundant and this column can't be NULL anyway? Can a unique column be NULL?

pennine
  • 49
  • 4
  • 1
    That depends on the database. For example, in MySQL, you would need to set `nullable=False` https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints?rq=1. In the case of SQLA itself, I'm not sure whether it has standardised behaviour or just relies on the underlying libraries – roganjosh Dec 28 '19 at 11:00
  • 1
    In many DBMS having both `index=True` and `unique=True` is redundant, since unique constraints are enforced using a unique index. – Ilja Everilä Dec 28 '19 at 11:54

1 Answers1

2

Yes, a unique column can be NULL. You will still need to use nullable=False if you want to control this.

If you omit nullable=False then the exact behaviour depends on the dialect. Some will allow multiple NULL values, others will only allow a single NULL when testing for uniqueness.

Firstly, we can see the following from the source code

:param nullable: When set to False, will cause the "NOT NULL" phrase to be added when generating DDL for the column. When True, will normally generate nothing (in SQL this defaults to "NULL"), except in some very specific backend-specific edge cases where "NULL" may render explicitly. Defaults to True unless :paramref:~.Column.primary_key is also True, in which case it defaults to False. This parameter is only used when issuing CREATE TABLE statements.

I can't cover all dialects but my searches seem to suggest that most will allow multiple NULL entries in a column with a unique constraint e.g. the SQLite FAQ states:

The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?

...

SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does.

roganjosh
  • 12,594
  • 4
  • 29
  • 46