Inserting data into sql server table

Question

Inserting data into sql server table

Navigation

#1 by (2 votes)

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, the warning appears:

It is not possible to insert the NULL value in the column 'id_observacao', table 'RDO.dbo.observacaoProtocolos'; the column does not allow nulls. INSERT failed.

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

asked by anonymous 22.01.2015 / 14:48

1 answer

How to retrieve the ID of the selected element in asp: Repeater via javascript? Result of the strange serialize

score 2 · Answer 1

The error is in your INSERT .

You have named the fields being inserted, but did not include the id_observacao field, which 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 do not know which Id would be correct to insert, so I put 0.

In addition, there are several problems in modeling your table. There are no foreign keys in the statement. The correct one would look 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:

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