0

Take docker image mcr.microsoft.com/mssql/server:2022-latest for example.

CREATE TABLE unique_struct (
   name nvarchar(30), 
   CONSTRAINT uni_unique_struct_name UNIQUE (name)
);

step 1:

ALTER TABLE unique_struct ALTER COLUMN name nvarchar(20);

According to https://stackoverflow.com/a/19461205/14562993, it should fail, because The object 'uni_unique_struct_name' is dependent on column 'name'

But try step 2:

ALTER TABLE unique_struct ALTER COLUMN name nvarchar(40);

It succeeded.

After my tests

  • new size >= current size: alter column succeeded.
  • new size < current size: alter column failed.

Is this a bug in SQL Server?

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
Mr.black
  • 21
  • 3
  • Does this answer your question? [How to alter column of a table with indexes?](https://stackoverflow.com/questions/43319018/how-to-alter-column-of-a-table-with-indexes) – P.Salmon Jun 19 '23 at 10:38
  • 3
    The example here describes the exact scenario: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#alter_column, it simply says that "The size of col_a can still be increased". – Salman A Jun 19 '23 at 10:45
  • Wow, thanks for your answer. – Mr.black Jun 19 '23 at 11:48

2 Answers2

0

When you reduce the length it implies that the data in the column needs to be truncated. It will only work when the column does not contain any data or when the data length does not exceed the new length you want, so it does not have to truncate anything.

If you want to reduce the length on a column containing data exceeding the new value you can first run a query to update the data and truncate them yourself then you can update the column length.

You can do it by running a query like this:

UPDATE unique_struct 
SET name =LEFT(name ,20)

But be aware that you will lose data. So be careful.

Max
  • 794
  • 3
  • 7
0

This behavior is not a bug, but a feature of SQL Server.

When you define a unique constraint on a column, SQL Server needs to ensure that all data in that column complies with the constraint. When you try to alter the column to have a smaller size, SQL Server cannot guarantee that the data will still be unique after the change. For example, if you have two entries, '12345678901234567890' and '123456789012345678901', they are unique when the size is 30. But if you change the column size to 20, they will both be truncated to '12345678901234567890', and the uniqueness constraint will be violated.

On the other hand, if you try to alter the column to have a larger size, SQL Server can guarantee that the data will still be unique after the change, since the existing data will not be altered in any way. Therefore, it allows the change.

This behavior is in line with the SQL Server's data integrity enforcement. It prevents changes that could possibly result in data loss or violation of data integrity rules, such as unique constraints.

Tom
  • 11
  • 1