Ralf
Ralf

Reputation: 548

Can a CLR UDT be a PK on a table?

Can you have your own clr udt as a primary key on a table? It seems you cannot - I added interfaces for binary serialization and comparable, but when using the type in a PK Constraint I receive an error: The type cannot be used in a primary key.

So - is this possible at all, and if so, which attributes do I need to decorate my type with to allow it?

*edit: Found a clue to my problem: from https://learn.microsoft.com/de-de/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017 section "columns": You can create indexes on CLR user-defined type columns if the type supports binary ordering.

So binary ordering needs to be "true" (mine was false). Follow up question is now: how to serialize your type (collection of strings) so that it becomes binary orderable?

-- Background Info

The reason I want to do that is maybe a worse followup to a bad initial idea: our current db implementation uses composite business keys. The components of the composite key are not in "many" columns, but concatenated with separators in one column. For example, "jeep-red-ny" could be the primary key for a row describing a Jeep, colored in red, that is based in New York. The column that contains this concatenation is simply varchar(50).

I was wondering if having a "dedicated" type to host these three aspects (vehicle type, color, city) makes it a bit better.

I suppose, best option would be to drop these concatenated keys and use "normal composite" keys in distinct columns instead. Having the concatenation is nice in a way that the whole key is one column, there is no doubt what belongs to it and what not. The UDT would give both: contained in one column, but access to all components.

Upvotes: 0

Views: 47

Answers (0)

Related Questions