Foreign key doesn't get auto_incremented

Question

My Code:

    create table Products
(
    ProductID int not null auto_increment primary key,
    ProductName varchar(20),
    Recommendedprice decimal(10,2),
    Category varchar(10)

)



create table customers
(
    CustomerID int not null auto_increment primary key,
    FirstName varchar(50),
    LastName varchar(50),
    city varchar(50),
    State char(2),
    zip varchar(10)

)


    create table sales
    (
        SalesID int not null auto_increment primary key,
        ProductID int, 
        CustomerID int,
        CONSTRAINT fk_PerProducts FOREIGN KEY (ProductId)
        REFERENCES Products(ProductId),  
        CONSTRAINT fk_PerCustomers FOREIGN KEY (CustomerId)
        REFERENCES customers(customerId),  
        SalesPrice decimal(10,2),
        SalesDate date 
    )

I get null values in the table list.

create database practice create table Products ( ProductID int not null auto_increment primary key, ProductName varchar(20), Recommendedprice decimal(10,2), Category varchar(10) ) create table customers ( CustomerID int not null auto_increment primary key, FirstName varchar(50), LastName varchar(50), city varchar(50), State char(2), zip varchar(10) ) — Mehul Solanki, Feb 05 '16 at 08:25
no....i used two foreign keys in create table statements only....there should be integer values but what i get is Null values....don't know why ? some modification required? i am at beginner level ! — Mehul Solanki, Feb 05 '16 at 08:46
while inserting data you have to insert the values, foreign key does not get automatically populated — undefined_variable, Feb 05 '16 at 09:28

score 0 · Accepted Answer · edited May 23 '17 at 12:07

0

You need to send the id of the foreigner key when you make an INSERT.

like this (paper_Author work like your sales table) :

CREATE TABLE Paper (`paperId` int, `title` varchar(7));
INSERT INTO Paper (`paperId`, `title`)
VALUES (1, 'hello'),(2, 'hola'),(3, 'bonjour');

CREATE TABLE Author (`authorId` int, `authorName` varchar(3));
INSERT INTO Author (`authorId`, `authorName`)
VALUES (1, 'me'),(2, 'moi');

CREATE TABLE Paper_Author (`paperId` int, `authorId` int);
INSERT INTO Paper_Author (`paperId`, `authorId`)
VALUES (1, 1),(1, 2),(2, 2);

and you use it like this : http://sqlfiddle.com/#!2/eb61f/2

other think you should read :

Basics of Foreign Keys in MySQL?

Why use Foreign Key constraints in MySQL?

edited May 23 '17 at 12:07

Community

1
1

answered Feb 05 '16 at 09:28

Blag

5,818
2
22
45

so the conclusion is : i must insert the value of foreign key by myself. It will not come from primary table ? – Mehul Solanki Feb 05 '16 at 13:22
yes if i insert manually It works. Thanks. I thought it will come from primary but that's not true.I guess ! – Mehul Solanki Feb 05 '16 at 13:41
your code can't guess what you want to link. the `id` primary key is here to be a unique identifier you can use in other table to reference as the whole row (like a flash code on a product). The constraint if used on `innoDB` mySql DB will lock the row if you still have a children that belong to it. This allow you to keep you data integrity (no orphan). – Blag Feb 05 '16 at 13:50
i understood my mistake. i didn't see the sales table clearly. so i was stucked in a wrong way. At last i got rid of it. Thanks to stackoverflow ! and all of you guys for your responses ! – Mehul Solanki Feb 05 '16 at 14:01

Foreign key doesn't get auto_incremented

1 Answers1