Getting Null error even tho there should be a value via the foreign key

Question

I keep getting an error message when trying to enter my data into the books table

error(Msg 515, Level 16, State 2, Line 32 Cannot insert the value NULL into column 'booksPubID', table 'Librarys.dbo.books'; column does not allow nulls. INSERT fails. The statement has been terminated.)

I don't understand why, I have done this same procedure countless times with other tables and it works fine.

create database Librarys

use Librarys

create table publishers (
    pubID int primary key not null identity (200,1),
    pubName varchar(100) not null,
    pubAddress varchar(200) not null,
    pubPhone varchar(20) not null,
);
insert into publishers
    (pubName, pubAddress, pubPhone)
    values
    ('World Class Books','9152 Longfellow Court Grand Island, NE 68801','(202)-555-0199'),
    ('Andy And Ally','19 Indian Summer Street Hempstead, NY 11550','(426)-535-4861'),
    ('Penguin Random House','7506 Fifth Court Holyoke, MA 01040','(202)-555-0148'),
    ('Hachette Livre ','58 Corona Ave. Kaukauna, WI 54130','(339)237-1422'),
    ('HarperCollins','9292 E. Cardinal Street West Lafayette, IN 47906','(361)474-5931'),
    ('Pan Macmillan','7002 Hillcrest Street Vernon Rockville, CT 06066','(713)515-6501'),
    ('Bloomsbury','9332 Ohio Ave. Copperas Cove, TX 76522','(219)738-8246'),
    ('Simon & Schuste','8464 Jockey Hollow Street Goldsboro, NC 27530','(610)921-2491'),
    ('Scholastic','47 West Heritage St. Henderson, KY 42420','(609)354-2678')
;

create table books (
    bookID int primary key not null identity (1,1),
    booksPubID int not null constraint fkPubID foreign key references publishers(pubID) on update cascade on delete cascade,
    bookTitle varchar(50) not null
);

insert into books 
    (bookTitle)
    values 
    ('The Lost Tribe'),
    ('Misery'),
    ('The Silmarillion'),
    ('The Hobbit'),
    ('The Fellowship of the Ring'),
    ('Two Towers'),
    ('Return of the King'),
    ('One Fish Two Fish Red Fish Blue Fish'),
    ('The Cat in the Hat'),
    ('Eragon'),
    ('The Megicians Nephew'),
    ('The Name Of The WInd'),
    ('A Game of Thrones'),
    ('Mistborn'),
    ('2001 A Space Odyssey'),
    ('The FOrever War'),
    ('Hyperion'),
    ('Beyond the High Mist'),
    ('To Kill a Mockingbird'),
    ('The Giver'),
    ('Gathering Blue'),
    ('Messenger'),
    ('Son'),
    ('The Great Gatsby'),
    ('Pride and Prejudice')
;

Answer 1

The error is pretty clear. On this table below, booksPubID is NOT NULL, specifically because of the FK constraint of the publishers table. You are stating that a book can not exist without a publisher by making it not null. Remove the not null part to to allow a book to exist without a publisher in the publisher table.

create table books (
    bookID int primary key not null identity (1,1),
    booksPubID int not null constraint fkPubID foreign key references publishers(pubID) on update cascade on delete cascade,
    bookTitle varchar(50) not null
);

Here is how you fix it... ONLINE DEMO

drop table books;
drop table publishers;
create table publishers (
    pubID int primary key not null identity (200,1),
    pubName varchar(100) not null,
    pubAddress varchar(200) not null,
    pubPhone varchar(20) not null,
);

create table books (
    bookID int primary key not null identity (1,1),
    booksPubID int null constraint fkPubID foreign key references publishers(pubID) on update cascade on delete cascade,
    bookTitle varchar(50) not null
);


insert into publishers
    (pubName, pubAddress, pubPhone)
    values
    ('World Class Books','9152 Longfellow Court Grand Island, NE 68801','(202)-555-0199'),
    ('Andy And Ally','19 Indian Summer Street Hempstead, NY 11550','(426)-535-4861'),
    ('Penguin Random House','7506 Fifth Court Holyoke, MA 01040','(202)-555-0148'),
    ('Hachette Livre ','58 Corona Ave. Kaukauna, WI 54130','(339)237-1422'),
    ('HarperCollins','9292 E. Cardinal Street West Lafayette, IN 47906','(361)474-5931'),
    ('Pan Macmillan','7002 Hillcrest Street Vernon Rockville, CT 06066','(713)515-6501'),
    ('Bloomsbury','9332 Ohio Ave. Copperas Cove, TX 76522','(219)738-8246'),
    ('Simon & Schuste','8464 Jockey Hollow Street Goldsboro, NC 27530','(610)921-2491'),
    ('Scholastic','47 West Heritage St. Henderson, KY 42420','(609)354-2678')
;

insert into books 
    (bookTitle)
    values 
    ('The Lost Tribe'),
    ('Misery'),
    ('The Silmarillion'),
    ('The Hobbit'),
    ('The Fellowship of the Ring'),
    ('Two Towers'),
    ('Return of the King'),
    ('One Fish Two Fish Red Fish Blue Fish'),
    ('The Cat in the Hat'),
    ('Eragon'),
    ('The Megicians Nephew'),
    ('The Name Of The WInd'),
    ('A Game of Thrones'),
    ('Mistborn'),
    ('2001 A Space Odyssey'),
    ('The FOrever War'),
    ('Hyperion'),
    ('Beyond the High Mist'),
    ('To Kill a Mockingbird'),
    ('The Giver'),
    ('Gathering Blue'),
    ('Messenger'),
    ('Son'),
    ('The Great Gatsby'),
    ('Pride and Prejudice')
;

select * from books
select * from publishers