Set default value SQL Server column

Asked

Viewed 1,333 times

2

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 receive only values of’S' or 'N'.

Thank you

sql sql-server sql-insert

2 answers

4

Luiz, you can use the restriction CHECK.

-- 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 defined with grouping (leotard) case sensitive, the use of the upper function should then be added to the comparison.

  • It would not be the same that was informed by Ricardo Punctual?

  • @Luizmartinez: Yes, Ricardo also suggested the use of the CHECK restriction. The difference is only the form: he suggested it through the ALTER TABLE command and I suggested it directly in the table definition. The final result is the same. // When the table already exists, the method of making changes to the tables is via the ALTER TABLE command.

  • 1

    I understood, it was what I suspected, as here the fields already exist, I will have to use the Alter Table, but thank you for the suggestion, I will keep it in the sleeve for another need.

4


You can create a constraint to validate the field domain:

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, it’s not best to use a field of the kind bit, which will only accept 1 and 0 (S and N)?

  • is a case of custom, do not see as good or bad. Is there any good practice for these cases?

  • @Luizmartinez, in this specific case, it is good practice to use the type bit. Some reasons to be better: field bit takes up less space. In addition, the char can bring you difficulties when using the where, if the COLLATE your bank is case sensitive.

  • Got it, here we are using the collate default SQL (Latin1_general_ci_as) I believe there are no problems, in any case the fields already existed, it was just a matter of preventing the insertion of different values. Thank you very much

Browser other questions tagged sql sql-server sql-insert

You are not signed in. Login or sign up in order to post.