9

Is there a built-in way to limit a column in the sql server to a list of explici...

 3 years ago
source link: https://www.codesd.com/item/is-there-a-built-in-way-to-limit-a-column-in-the-sql-server-to-a-list-of-explicit-values-in-the-table-design-view.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Is there a built-in way to limit a column in the sql server to a list of explicit values ​​in the table design view?

advertisements

I have a table named myTable on SQL Server 2008 R2 Express.

I would like to have a (marital) Status column with the only explicit values: 'Single', 'Married', 'Divorced', 'Widower'. The default should be 'Married'.

Is there a way to limit the field to the above values ON THE SQL Server without additional tables?

You can do that with a CHECK() constraint.

create table whatever (
...
  Status varchar(12) not null default 'Married'
    check (Status in ('Single', 'Married', 'Divorced', 'Widower')),
...
);

The problem with using a CHECK constraint like this is that it's harder for the user interface to present a list of valid choices to the user for selection. If these four were stored in a table of marital statuses, you could just select status from marital_statuses order by status;. If they were stored in a table, you could still use 'Married' as the default.

If I were going to store these in a table, it might look like this.

create table marital_statuses (
  status_code char(1) primary key,
  status_text varchar(12) not null unique
);

insert into marital_statuses values
('s', 'Single'),
('m', 'Married'),
('d', 'Divorced'),
('w', 'Widower');

Using human-readable codes means you usually won't need an additional join. (If you use ID numbers, you always need an additional join.) If I did that, I'd use a foreign key in the "whatever" table, and change the default to 'm'.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK