Sql – Cannot insert the value NULL into column ‘id’, even though Column has IDENTITY property

sqlsql-server-2008

CREATE TABLE Type1
(
   TypeID       TINYINT         NOT NULL            IDENTITY(1,1),
   TypeName     VARCHAR(20)     NOT NULL,
   Speed        VARCHAR(10)     NOT NULL

   CONSTRAINT   TypeID_pk       PRIMARY KEY (TypeID)
);


   CREATE TABLE Splan
(
   PlanID       TINYINT         NOT NULL            IDENTITY(1,1),
   TypeID       TINYINT         NOT NULL,
   PlanName     VARCHAR(20)     NOT NULL,
   Quota        SMALLINT        NOT NULL

   CONSTRAINT   PlanID_pk       PRIMARY KEY (PlanID)
   CONSTRAINT   TypeID_fk       FOREIGN KEY (TypeID) REFERENCES Type1(TypeID)                                    
);  



  INSERT INTO Type1(TypeName, Speed)
  VALUES ('Sample type', '10Mbps'),
         ('Other type', '50Mbps');

^Up until there its fine

and then when I enter the following it returns "Msg 515, Level 16, State 2, Line 8
Cannot insert the value NULL into column 'TypeID' ….. column does not allows. INSERT fails." Statement terminates

   INSERT INTO Splan(PlanName, Quota)
   VALUES ('Some sample name', '500GB'),
          ('sample2, '250GB');

I've tried creating the constraints at both column and table level but the second INSERT statement still refused to enter. Double checked via the GUI and 'TypeID' definitely has an IDENTITY property.

I've looked about everywhere and this errors seems to stem from the lack of an IDENTITY property, yet its present in my creation statements and the error still comes up. Tried removing the seed and increment from IDENTITY, still nothing. Also tried inserting the data one row at a time, nothing there either.

P.S If you haven't noticed the actual names have been substituted and other columns rows have been omitted.

Best Solution

Since you created typID as NOT NULL, Sql is complaining that the default value (NULL) is not acceptable.

Try

INSERT INTO Splan(TypeID, PlanName, Quota)
VALUES (1, 'Some sample name', '500GB'),
       (2, 'sample2, '250GB');

Where corresponding records with TypeID = 1 and TypeID = 2 are in your Type1 table.


You are creating 2 tables: Type1 which has a primary key TypeId that is auto generated and SPlan which has a primary key PlanId that is also auto generated and a foreign key TypeId that must be supplied and cannot be null.

As written you must enter 1 or more records into Type1 first, obtain their TypeIds, then enter those TypeIds into new records in SPlan.

Incidentally, using TINYINT for your primary key data types is perfectly legal but probably a really bad idea if this is anything other than homework.