I would like to know how to set an insert pattern in a column in SQL.
Example:
CREATE TABLE XPTO(
ID INT IDENTITY(1,1),
NOME VARCHAR(100) NOT NULL,
ATIVO CHAR(1) NOT NULL)
In this case, I want to limit the ACTIVE field to only receive values of 'S' or 'N'.
Thank you
You can create a constraint to validate the domain of the field:
ALTER TABLE [dbo].[XPTO] WITH CHECK ADD CONSTRAINT [CK_ATIVO] CHECK (( [ATIVO]='S' OR [ATIVO]='N')) GO
ALTER TABLE [dbo].[XPTO] CHECK CONSTRAINT [CK_ATIVO] GO
Now, for your specific case, is it not better to use a field of type bit , which will only accept 1 and 0 (S and N)?
Luiz, you can use the CHECK restriction.
-- código #1
CREATE TABLE XPTO(
ID INT IDENTITY(1,1),
NOME VARCHAR(100) NOT NULL,
ATIVO CHAR(1) NOT NULL check (ATIVO in ('S','N'))
);
-- código #2
CREATE TABLE XPTO(
ID INT IDENTITY(1,1),
NOME VARCHAR(100) NOT NULL,
ATIVO CHAR(1) NOT NULL,
constraint Ativo_SN check (ATIVO in ('S','N'))
);
If the database is set to collation ( collate ) that is case-sensitive, you should add the use of the upper function in the comparison.