SQL Foreign keys not auto incrementing

Question

When I comment out every call of the foreign keys the code runs, creates the tables correctly and fills them with the incrementing id, but when I call the foreign keys in it just breaks and the id stays as null

 CREATE TABLE CustomerDetails
(
    CustomerID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
    CONSTRAINT CustomerDetails_PK PRIMARY KEY,
    CustomerFirstName VARCHAR(15) NOT NULL,
    CustomerSurname VARCHAR(15) NOT NULL,
    ContactNumber VARCHAR(13) NOT NULL
);
INSERT INTO CustomerDetails(CustomerFirstName, CustomerSurname, ContactNumber) VALUES ('First', 'Last', '09998877665');
CREATE TABLE CinemaList
(
    CinemaID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
    CONSTRAINT CinemaList_PK PRIMARY KEY,
    CinemaName VARCHAR(10)
);
INSERT INTO CinemaList(CinemaName) VALUES ('England');
INSERT INTO CinemaList(CinemaName) VALUES ('Wales');
INSERT INTO CinemaList(CinemaName) VALUES ('Ireland');
CREATE TABLE FilmDetails
(
    FilmID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
    CONSTRAINT FilmDetails_PK PRIMARY KEY,

    FilmName VARCHAR(25),
    FilmInfo VARCHAR(250),
    CinemaList_CinemaID INT NOT NULL
);
ALTER TABLE FilmDetails ADD FOREIGN KEY (CinemaList_CinemaID) REFERENCES CinemaList(CinemaID);
INSERT INTO FilmDetails(FilmName, FilmInfo) VALUES ('Film1', 'Film1 Bio');
INSERT INTO FilmDetails(FilmName, FilmInfo) VALUES ('Film2', 'Film2 Bio');
CREATE TABLE FilmTimes
(
    TimeID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
    CONSTRAINT FilmTimes_PK PRIMARY KEY,
    FilmTime VARCHAR(5),
    FilmDetails_FilmID INT NOT NULL
);
ALTER TABLE FilmTimes ADD FOREIGN KEY (FilmDetails_FilmID) REFERENCES FilmDetails(FilmID);
INSERT INTO FilmTimes(FilmTime) VALUES ('11:00');
INSERT INTO FilmTimes(FilmTime) VALUES ('17:00');
INSERT INTO FilmTimes(FilmTime) VALUES ('21:00');
CREATE TABLE BookingDetails
(
BookingID INT NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
CONSTRAINT BookingDetails_PK PRIMARY KEY,
FilmTimes_TimeID INT NOT NULL,
CustomerDetails_CustomerID INT NOT NULL,
BookingDate DATE NOT NULL

);
ALTER TABLE BookingDetails ADD FOREIGN KEY (CustomerDetails_CustomerID) REFERENCES CustomerDetails(CustomerID);
ALTER TABLE BookingDetails ADD FOREIGN KEY (FilmTimes_TimeID) REFERENCES FilmTimes(TimeID);
INSERT INTO BookingDetails(BookingDate) VALUES ('2016-01-17');

Looks like you need to re-think the relationship between FilmTimes and FilmDetails in general. I see you inserting values into FilmDetails without providing values for the foreign key, which has a `NOT NULL` constraint (so you must provide non-null values for it). — aro_tech, Jan 17 '16 at 07:21
I'm trying to call FilmTimeID from FilmTimes as a foreign key in FilmDetails. We need the time there, and not null. @aro_tech — Matt Richardson, Jan 17 '16 at 07:33
Foreign key constraints do not cause any values to be generated or inserted. — aro_tech, Jan 17 '16 at 08:57
Are the values not generated on the initial declaration? E.g - `BookID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) CONSTRAINT BookingDetails_PK PRIMARY KEY` — Matt Richardson, Jan 17 '16 at 09:00

score 0 · Answer 1 · answered Jan 18 '16 at 07:21

The database doesn't know what values you want to insert for the foreign key. You have to insert them explicitly. For example, for film times you'll need to do something like this:

INSERT INTO FilmTimes(FilmTime, FilmDetails_FilmID) SELECT '11:00', FilmID FROM FilmDetails

This should insert two lines into FilmTimes: a line containing a generated TimeID, "11:00", and the generated ID of "Film1" and a line with a different generated TimeID, "11:00", and the generated ID of "Film2". For example {1,"11:00",1} and {2,"11:00",2}.

Your primary keys are generated on insertion, but the foreign keys cannot be generated - a foreign key refers to something that exists already in another table.

SQL Foreign keys not auto incrementing

1 Answers1