Need TABLE DDL to define finite list of column values

Question

Need TABLE DDL to define finite list of column values

Express12

SSCoach

Points: 18514
More actions
September 6, 2022 at 6:24 pm

#4084583

Looking for sample DDL syntax to CREATE a TABLE w/ a COLUMN that includes list of finite values.
For example, I only want 4 allowable SQL Versions stored in the column SQLServerVersion below. What is the correct DDL to accomplish this?
CREATE TABLE MyTable
(ID INT IDENTITY(1,1) NOT NULL,
MySQLServerName NVARCHAR(40) NOT NULL,
SQLServerVersion NVARCHAR(40) NOT NULL values ('SQL 2022', 'SQL 2019', 'SQL 2017', 'SQL 2014')
)
GO
(Yes, we know we should create a 2nd table w/ the list of valid SQL Versions but for this case, we want to use a contrained list of column valuesin the existing table. thx in advance)
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Recommend storing atomic values rather than denormalized lists. STRING_AGG() function makes it easy to return it to the client as a list if that's really required.

e.g., if you're trying to limit to a max of 4 versions, then add an ordinal column (tinyint is sufficient) with a check constraint that limits the ordinal to 1 through 4, and a unique constraint on MySqlServerName & Ordinal. You might also want a unique constraint on MySqlServerName & SqlServerVersion to prevent storing the same server/version more than once.

Out of curiosity, what do these allowable SQL Server versions mean/represent? Does MySqlServerName represent a host/VM rather than a SQL Server instance?

Ed B Hall of Fame Points: 3666 More actions · Answer 2

Do you just need a check constraint on the column?

CREATE TABLE MyTable
(ID INT IDENTITY(1,1) NOT NULL,
MySQLServerName NVARCHAR(40) NOT NULL,
SQLServerVersion NVARCHAR(40) NOT NULL ,
CONSTRAINT CKMyTableSqlVersion CHECK (SQLServerVersion IN  ('SQL 2022', 'SQL 2019', 'SQL 2017', 'SQL 2014'))
)

Or does the column contain a list of values that need to be constrained?

Express12 SSCoach Points: 18514 More actions · Answer 3

thx all -- for your valuable feedback. Ed B, exactly what I was looking for! thank you.

BT

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Express12 wrote:

thx all -- for your valuable feedback. Ed B, exactly what I was looking for! thank you.

While there's likely no better way to do this, the trouble with something like this is that you're going to have to remember to change that check whenever Microsoft comes out with a new version of SQL Server. I hope you have a documented process somewhere that will help your memory. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)