CODEWITHSUNDEEP
mysql
Dario Ongsono
Dario Ongsono

Reputation: 79

Foreign key showing null value

I am trying to show the foreign key value that is extracted from another table. The codes that i've used:

Code for first table:

CREATE TABLE trip (
    tripID  INT(10) PRIMARY KEY,
    startTime   TIME,
    endTime     TIME,
    tripDate    DATE,
    price       VARCHAR(5),
    databaseID  INT(10),
    FOREIGN KEY (databaseID)
       REFERENCES databasestorage(databaseID)
);

code for second table:

CREATE TABLE databasestorage (
    databaseID  INT(10) PRIMARY KEY,                    
    location        VARCHAR(40)
);

After inserting the values into both tables, in the trip table, databaseID still shows as null while all other columns are correct. How do i make it such that databaseID in my trip table shows the value of databaseID from the databasestorage table?

Upvotes: 3

Views: 1799

Answers (2)

itzmukeshy7
itzmukeshy7

Reputation: 2677

Try this ;)

Add auto_increment to table definition:

For first:

CREATE TABLE databasestorage (
    databaseID  INT(10) PRIMARY KEY auto_increment,
    location        VARCHAR(40)
);

For second:

CREATE TABLE trip (
    tripID  INT(10) PRIMARY KEY auto_increment,
    startTime   TIME,
    endTime     TIME,
    tripDate    DATE,
    price       VARCHAR(5),
    databaseID  INT(10),
    FOREIGN KEY (databaseID)
        REFERENCES databasestorage(databaseID)
);

ADDITION (Insert query)

INSERT INTO trip(tripID, startTime, endTime, tripDate, price) VALUES('101', '20:20:00', '20:40:00', '2016-10-22', '$5.25');

In this query no need to pass value for tripID it's an auto_increment field I think you are passing databaseID value to tripID. So the value you are getting after insert for databasestorage.databaseID should be passed in trip.databaseID

So final query would be:

INSERT INTO trip(startTime, endTime, tripDate, price, databaseID) VALUES('20:20:00', '20:40:00', '2016-10-22', '$5.25', 101);

And as the trip.databaseID is INT field no need to add quotes('');

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You need to pass primary key of databasestorage table while inserting data into trip table right?

INSERT INTO databasestorage(databaseID, location) VALUES('1021', 'Caulfield')

For trip table you need to explicit pass the value for databaseID column in trip table, foreign key will not automatically inserted 

                                                        ---------v
INSERT INTO trip(tripID, startTime, endTime, tripDate, price,databaseID)
VALUES('101', '20:20:00', '20:40:00', '2016-10-22', '$5.25','1021')
                                                         -----^

Upvotes: 1

Related Questions