Creating column of type enum
-
Hello, I'm using VS2010 to create Data Base in SQL server. I want to create column that will be of type enum. For example the enum:
public enum Equipment
{
Tank,
Suit,
Fins,
Mask,
Snorkel
}And the column will be called DiveKit, the values in that column can be one of the Equipment enumeration members. Thanks
-
Hello, I'm using VS2010 to create Data Base in SQL server. I want to create column that will be of type enum. For example the enum:
public enum Equipment
{
Tank,
Suit,
Fins,
Mask,
Snorkel
}And the column will be called DiveKit, the values in that column can be one of the Equipment enumeration members. Thanks
Sql Server doesn't have an
Enumdatatype. You can store it's value-representation (int) or it's string-representation (varchar). It wouldn't do range-checking by default. Simple example below on having range-checking by creating a table for the enum and referencing it;CREATE TABLE enumEquipment([Label] VARCHAR(10) PRIMARY KEY)
INSERT INTO enumEquipment (Label) VALUES ('Tank'), ('Suit'), ('Fins'), ('Mask'), ('Snorkel');CREATE TABLE someTableUsingTheEnum
(
Id INT IDENTITY(1,1) PRIMARY KEY,
EnumValue VARCHAR(10) REFERENCES enumEquipment (Label)
)
INSERT INTO someTableUsingTheEnum (EnumValue) VALUES ('Tank') -- works
INSERT INTO someTableUsingTheEnum (EnumValue) VALUES ('Micky') -- failsSELECT * FROM someTableUsingTheEnum -- shows 1 record, with 'Tank'
DROP TABLE someTableUsingTheEnum
DROP TABLE enumEquipmentIt'd be better, performance-wise, to use the integer-value of the enum. Just cast it to an int, and store it in an int in Sql Server.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Hello, I'm using VS2010 to create Data Base in SQL server. I want to create column that will be of type enum. For example the enum:
public enum Equipment
{
Tank,
Suit,
Fins,
Mask,
Snorkel
}And the column will be called DiveKit, the values in that column can be one of the Equipment enumeration members. Thanks
-
Hello, I'm using VS2010 to create Data Base in SQL server. I want to create column that will be of type enum. For example the enum:
public enum Equipment
{
Tank,
Suit,
Fins,
Mask,
Snorkel
}And the column will be called DiveKit, the values in that column can be one of the Equipment enumeration members. Thanks
You can use a check constraint on the table if you just want the column limited to a list of values.
ALTER TABLE EQIPMENT
ADD CONSTRAINT EQUIPMENTTYPE_CHK CHECK (DIVEKIT IN ('Tank', 'Suit', 'Fins', 'Mask', 'Snorkel')"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
You can use a check constraint on the table if you just want the column limited to a list of values.
ALTER TABLE EQIPMENT
ADD CONSTRAINT EQUIPMENTTYPE_CHK CHECK (DIVEKIT IN ('Tank', 'Suit', 'Fins', 'Mask', 'Snorkel')"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
I'd be concerned about the need to easily add new members. And agnosticism. Plus, having a table allows the addition of more information related to the items.
-
I'd be concerned about the need to easily add new members. And agnosticism. Plus, having a table allows the addition of more information related to the items.
All true, and if one wanted to fully emulate an enum one would also need an extra column for the integer value. But you can't add new members to an enumeration without recompiling either. Anyway, I got the impression that the OP only wanted to limit the items in a column, and if your objections is of no concern for the OPs intended usage, this is a simple way to achieve a limited list of items.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
All true, and if one wanted to fully emulate an enum one would also need an extra column for the integer value. But you can't add new members to an enumeration without recompiling either. Anyway, I got the impression that the OP only wanted to limit the items in a column, and if your objections is of no concern for the OPs intended usage, this is a simple way to achieve a limited list of items.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Jörgen Andersson wrote:
the OP only wanted to limit the items in a column
Which is likely true, but I hope to open his eyes to a broader solution that he may not have considered yet. Reducing record size may not be as important with today's databases and storage availability, but it's still better to store a foreign key rather than a string, particularly if you may need to select records with given values. Having a table with the keys and values also allows for easier loading of a list from which the user can select items.
-
Jörgen Andersson wrote:
the OP only wanted to limit the items in a column
Which is likely true, but I hope to open his eyes to a broader solution that he may not have considered yet. Reducing record size may not be as important with today's databases and storage availability, but it's still better to store a foreign key rather than a string, particularly if you may need to select records with given values. Having a table with the keys and values also allows for easier loading of a list from which the user can select items.
Quite right. I'm usually pushing normalization myself.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull