Ошибка 515 sql

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Column Name', 
table 'Table Name'; column does not allow nulls.  
INSERT fails. The statement has been terminated.

To illustrate, let’s say you have the following table definitions:

CREATE TABLE [dbo].[Users] (
    [UserName]     VARCHAR(10)  NOT NULL,
    [FullName]     VARCHAR(100) NOT NULL,
    [Email]        VARCHAR(100) NOT NULL,
    [Password]     VARCHAR(20)  NOT NULL,
    [CreationDate] DATETIME     NOT NULL DEFAULT(GETDATE())
)

There are three ways that the error can be encountered.  The first way is when a column is not specified as one of the columns in the INSERT clause and that column does not accept NULL values.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com' )

Given this INSERT statement, the [Password] column is not specified in the column list of the INSERT INTO clause.  Since it is not specified, it is assigned a value of NULL. But since the column does not allow NULL values, the following error is encountered:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Password', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

The second way that the error can be encountered is when a NULL value is explicitly assigned to the column that does not allow NULLs.

INSERT INTO [dbo].[Users]([UserName], [FullName], [Email], [Password])
VALUES ( 'MICKEY', 'Mickey Mouse', NULL, 'Minnie' )

As can be seen from this INSERT command, the [Email] column is being assigned a NULL value during the insert but since the column does not allow NULL values, the following error is generated:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Email', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

The third possible way that the error is encountered is similar to the second one, which is by explicitly assigning a NULL value to a column, as shown below:

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', NULL )

The only difference is that the column being assigned to has a default value (in this case, the default value of the [CreationDate] column is current system date and time as generated by the GETDATE() function).  Since the column has a default value, you would think that if a NULL value is assigned to it, it will assign the default value instead. However, the following error is encountered:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CreationDate', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Solution / Work Around:

Regardless of the way on how the error is encountered, if a column does not accept NULL values, always assign a value to it when inserting new records to the table.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', GETDATE() )

If the column does not accept NULL values but has a default value assigned to it and you want that the default value be used for the newly inserted record, just do not include that column in the INSERT statement and the default will automatically be assigned to the column.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie' )

In this example, since the [CreationDate] column has a default value of GETDATE(), since it is not included in the column list in the INSERT INTO clause, the default value gets assigned to the column.

Another way of explicitly assigning the default value is by using the reserved word DEFAULT in the VALUES list, as can be seen in the following:

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', DEFAULT )

I have a table SINVOICE and another table called SINVOICE_LINE.

I need to put all columns of SINVOICE into SINVOICE_LINE.

I have created the corresponding columns and was trying to copy the values. The primary key of SINVOICE is SINVOICE_CODE, while the primary key for SINVOICE_LINE is a composite key (SINVOICE_CODE, SINVOICE_LINE_NUMBER).

I wrote the following query:

INSERT INTO SINVOICE_LINE (sinvoice.ITINERARY_CODE) 
   SELECT sinvoice_line.ITINERARY_CODE 
   FROM SINVOICE 
   INNER JOIN sinvoice_line ON sinvoice.sinvoice_code = sinvoice_line.sinvoice_code;

I get this error:

Cannot insert the value NULL into column SINVOICE_CODE, table SINVOICE_LINE; column does not allow nulls. INSERT fails.

I do not understand why I’m getting this error as I am not trying to insert any value in SINVOICE_CODE column.

Thanks!!!

I created some tables, this is one of them:

CREATE TABLE JewelryOrders
(
    OrderID INT, 
    JewelID INT,
    Quantity INT NOT NULL,
    PRIMARY KEY (OrderID, JewelID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (JewelID) REFERENCES Jewels(JewelID)
)

and then I tried to insert some data:

INSERT INTO JewelryOrders(Quantity)
VALUES ('1'), ('2');

However, I got an error message:

Msg 515, Level 16, State 2, Line 101
Cannot insert the value NULL into column ‘OrderID’, table ‘JewelryStore.dbo.JewelryOrders’; column does not allow nulls. INSERT fails.

The OrderID is defined this way in its original table:

CREATE TABLE Orders
(
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate DATE NOT NULL,
    SumTotal INT NOT NULL,
)

So it is not NOT NULL column. Any suggestions?

Error 515

Severity Level 16

Message Text

Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

Explanation

This error occurs at run time when an attempt is made to use a null value while inserting or updating a column that does not allow null values.

This message differs from the following message, which indicates that the attempt has been detected at compile time.

error 233: The column '%.*ls' in table '%.*ls' cannot be null.

This error can also occur if the table that is the target of an INSERT or UPDATE statement in a stored procedure or trigger is being dropped and re-created, and one or more of the table column definition(s) have changed from NULL to NOT NULL.

Action

If this error occurs when you are running an UPDATE or INSERT statement, verify that the data inserted or updated matches the column definition for the affected table.

Inserting or updating a column does not allow null values.

Note  In a direct UPDATE or INSERT by value, you will get compile error 233 instead of error 515. Usually 515 errors occur in an INSERT/SELECT or an UPDATE statement that uses data in another table.

If this error occurs when a stored procedure or trigger references a table that has been dropped and re-created with different nullability, drop and re-create the affected stored procedure or trigger.

If you are unable to resolve the problem, contact your primary support provider for assistance.

See Also

ALTER PROCEDURE

ALTER TABLE

ALTER TRIGGER

CREATE PROCEDURE

CREATE TABLE

CREATE TRIGGER

DROP PROCEDURE

DROP TRIGGER

Errors 1 — 999

INSERT

Reporting Errors to Your Primary Support Provider

SELECT

UPDATE