CODEWITHSUNDEEP
sql-serverdatabaseunique
Paushchyk Julia
Paushchyk Julia

Reputation: 516

How can add UNIQUE column to a table in a SQL Server database?

I need UNIQUE column in my table.

My table:

CREATE TABLE my_table
(
      id int IDENTITY PRIMARY KEY,
      name text NOT NULL UNIQUE,
      blabla text NOT NULL
);

and I get an error:

Column 'name' in table 'my_table' is of a type that is invalid for use as a key column in an index. Could not create constraint or index. See previous errors.

So, How can add UNIQUE column in table?

Upvotes: 0

Views: 138

Answers (2)

Shubham Pandey
Shubham Pandey

Reputation: 1019

So the problem lies with the data type you have specified for column name. Essentially 'text' is a large object (LOB) data type. SQL does not allow the creation of index over this particular data type. Other examples of such a data type would be varchar(max), nvarchar(max), varbinary(max), xml, and image.

I believe it is largely because these are not allocated any specific data size from the beginning and hence any index created would have to be changed if it's maximum limit is surpassed.

https://msdn.microsoft.com/en-us/library/ms188783.aspx

Upvotes: 1

Horia
Horia

Reputation: 1612

You should be using VARCHAR / NVARCHAR instead of TEXT, because TEXT data type is deprecated - see https://msdn.microsoft.com/en-us/library/ms187993.aspx

CREATE TABLE my_table
(
      id int IDENTITY PRIMARY KEY,
      name VARCHAR(100) NOT NULL CONSTRAINT UQ_my_table_name UNIQUE,
      blabla VARCHAR(100) NOT NULL
);

Also, consider naming your constraints rather than let a default name for it.

Upvotes: 5

Related Questions