Relation table does not update when I insert records

Question

Maybe this could be a stupid question. Ok, so I created the following table:

create table USER(
    ID int NOT NULL AUTO_INCREMENT primary key,
    firstname varchar(50) not null,
    lastname varchar(50) not null,
    password varchar(20) not null,
    emailU varchar(100) not null
);

create table POST(
    ID_User int,
    ID_Tweet int,
    primary key(ID_User, ID_Tweet),
    foreign key(ID_User) references USER(ID) on update cascade on delete cascade,
    foreign key(ID_Tweet) references TWEET(ID) on update cascade on delete cascade
);

create table TWEET(
    ID int NOT NULL AUTO_INCREMENT primary key,
    Text varchar(200)
);

When I insert something, for example:

insert into USER (firstname, lastname, password, emailU) values ('X', 'Y', 'XY', 'xy@gmail.com');

and

insert into TWEET (text) values ('Something');

It updates correctly the tables USER and TWEET, but the table POST remains empty. Why? It should be updated with the ID_User and ID_Tweet or I get wrong?

The post table doesn't get automatically populated, you have to populate it. How should the DB know that the TWEET(s) you just inserted are for which USER(s)? — MatBailie, Dec 18 '17 at 15:16
Please don't save passwords as plain text in your database. https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — Juan Carlos Oropeza, Dec 18 '17 at 15:27
Ok, but I'm not supposed to know the IDs of tweets and users cause they are int AutoIncrement, so how can I insert those informations in the table POST? @Juan Carlos Oropeza, yes this is just a first draft of the project ;) — palnic, Dec 18 '17 at 15:32
Different databases let you access that id created by IDENTITY or AUTOINCREMENT fields. SQL Server has `SCOPE_IDENTITY()`, MySQL has `LAST_INSERT_ID()`, SQLite has `sqlite3_set_last_insert_rowid()`, etc, etc, etc. So, when you create a user, you can find out what `id` it was given, when you insert a tweet you can find out what `id` it was given, then you can populated `post`. — MatBailie, Dec 18 '17 at 20:12
Possible duplicate of [How to setup relationship between tables In phpMyAdmin](https://stackoverflow.com/questions/24421055/how-to-setup-relationship-between-tables-in-phpmyadmin) — philipxy, Dec 18 '17 at 23:16

Juan Carlos Oropeza · Answer 1 · 2017-12-18T15:28:26.830

foreing key is just a constraint, to keep your table consistency, so you dont have the wrong data there (like orphan post without any user). So as MatBailie say the data doesn't get populated automatically.

You didnt specify your rdbms but after insert you need to get the last_insert id. for example in Sql Server

How to get last inserted id?

And then you use it to create a new record on the table post.

Also maybe your model is too simple but right now I dont see the need for you to have the table POST because not new data is add it to the relation.

So is better if you include the user_id field to the tweet

create table TWEET(
    ID int NOT NULL AUTO_INCREMENT primary key,
    Text varchar(200),
    ID_User int,
    foreign key(ID_User) references USER(ID) on update cascade on delete cascade,
);

Relation table does not update when I insert records

1 Answers1