Inserting data into sql server table

Asked

Viewed 294 times

1

I’m trying to insert data into a table:

INSERT INTO [RDO].[dbo].[observacaoProtocolos] (id_protocolo, statusObs1, observacao1, statusObs2, observacao2, statusObs3, observacao3, statusObs4, observacao4, statusObs5, observacao5, statusObs6, observacao6, statusObs7, observacao7, statusObs8, observacao8, statusObs9, observacao9, statusObs10, observacao10) 
VALUES (153,'OK','','','','','','','','','','','','','','','','','','','')

However, a warning appears:

Unable to insert NULL value in column 'id_observacao', table 'RDO.dbo.observacaProtocolos'; column does not allow nulls. Failure in INSERT.

I created the table as follows:

CREATE TABLE observacaoProtocolos
(
    id_observacao int NOT NULL,
    id_protocolo int,
    statusObs1 varchar(4),
    observacao1 varchar(100),
    statusObs2 varchar(4),
    observacao2 varchar(100),
    statusObs3 varchar(4),
    observacao3 varchar(100),
    statusObs4 varchar(4),
    observacao4 varchar(100),
    statusObs5 varchar(4),
    observacao5 varchar(100),
    statusObs6 varchar(4),
    observacao6 varchar(100),
    statusObs7 varchar(4),
    observacao7 varchar(100),
    statusObs8 varchar(4),
    observacao8 varchar(100),
    statusObs9 varchar(4),
    observacao9 varchar(100),
    statusObs10 varchar(4),
    observacao10 varchar(100)
);
sql-server
  • 2

    I think this modeling is very wrong. Why you need to have 10 pairs of status and observation fields?

1 answer

2

The mistake is in yours INSERT.

You named the fields being entered but did not include the field id_observacao that by its modeling is NOT NULL.

See below the correct:

INSERT INTO [RDO].[dbo].[observacaoProtocolos] (
    id_observacao,
    id_protocolo, 
    statusObs1, 
    observacao1, 
    statusObs2, 
    observacao2, 
    statusObs3, 
    observacao3, 
    statusObs4, 
    observacao4, 
    statusObs5, 
    observacao5, 
    statusObs6, 
    observacao6, 
    statusObs7, 
    observacao7, 
    statusObs8, 
    observacao8, 
    statusObs9, 
    observacao9, 
    statusObs10, 
    observacao10) 
VALUES (0,
    153,
    'OK',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '')

I don’t know which id would be correct to insert, so I put 0.

Also, there are several problems in modeling your table. There are no foreign keys in the statement. The correct one would be something like:

CREATE TABLE observacaoProtocolos
(
    id_observacao_protocolo int primary key identity,
    id_observacao int NOT NULL,
    id_protocolo int NOT NULL,
    statusObs varchar(4),
    observacao varchar(100),
    constraint observacao_fk (id_observacao) references observacao (id_observacao),                 
    constraint protocolo_fk (id_protocolo) references protocolo (id_protocolo),
);

That is to say:

  • id_observacao_protocolo to identify only the association;
  • id_protocolo also becomes NOT NULL because it is foreign key. It would make no sense to be NULL;
  • statusObs and observacao become a single column. There is no reason to have this pair of fields 10 times;
  • Constraints of foreign keys to avoid entering values that do not exist.

Browser other questions tagged sql-server

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