Reputation: 548
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