search

microsoft / mssql-django

The Microsoft Django backend for SQL Server provides a connectivity layer for Django on SQL Server or Azure SQL DB.
Other
347 stars 112 forks source link

Add support for `unique=True` on `BinaryField`, `TextField`, and others #34

Closed NZSmartie closed 3 years ago

NZSmartie commented 3 years ago

Microsoft SQL server has a set of maximum capacity specifications as documented Maximum capacity specifications for SQL Server

The DatabaseWrapper.data_types dictionary contains a max parameter for some fields in the database that may or may not have a max_length size set in the applications model: https://github.com/microsoft/mssql-django/blob/ff5984800c45bc5c9bd95e783425bf77963948e5/mssql/base.py#L74-L102

Specifically, BinaryField, JSONField, TextField have a corresponding max size set instead of respecting the max_length property that can be set in an application's model.

For example:

class ClientDownload(models.Model):
    access_code = models.BinaryField(max_length=15, default=random_access_code, unique=True)
    # ...

A developer should expect the access_code field to be created in the database with the type varbinary(15) (or binary(15), but that may be out of scope of this issue). However, since the field's type will end up with varbinary(max), the unique=True constraint will throw an exception from SQL server as the field is too large to be indexed.

[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'access_code' in table 'mypp_clientupload' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not create constraint or index. See previous errors. (1750)

This is due to the maximum bytes allowed for an indexable field

SQL Server Database Engine object Maximum sizes/numbers SQL Server (64-bit) Additional Information
Bytes per index key 900 bytes for a clustered index. 1,700 for a nonclustered index. The maximum number of bytes in a clustered index key cannot exceed 900 in SQL Server. For a nonclustered index key, the maximum is 1700 bytes. You can define a key using variable-length columns whose maximum sizes add up to more than the limit. However, the combined sizes of the data in those columns can never exceed the limit. In a nonclustered index, you can include extra non-key columns, and they do not count against the size limit of the key. The non-key columns might help some queries perform better.
absci commented 3 years ago

Thanks for your suggestion, the support will likely come in the future.

absci commented 3 years ago

Hi, max_length for BinaryField is supported with this commit (https://github.com/microsoft/mssql-django/commit/848c847cec23f18501010d97889b2cbcb7631fbf)

For TextField and JSONField, there are intended to store a large amount of text, so max_length will not be supported.