为什么不在关系表中自动创建？ （在mysql中）

Question

create table Board (
boardID     char(30) not null,
readLevel   tinyint not null,
writeLevel  tinyint not null,
PRIMARY KEY (boardID) ) engine=InnoDB character set=utf8;

create table Post (
postID          int not null AUTO_INCREMENT,
title           char(50) not null,
content         TEXT not null,
writeDate       date not null,
readCount       int not null,
PRIMARY KEY (postID)) engine=InnoDB character set=utf8;

create table Save_Board_Post(
boardID   char(30) not null,
postID    int not null,
FOREIGN KEY (boardID) REFERENCES Board(boardID) ON UPDATE CASCADE,
FOREIGN KEY (postID) REFERENCES Post(postID) ON UPDATE CASCADE ) engine=InnoDB character set=utf8;

insert into Board (boardID, readLevel, writeLevel) values ('testBoard', 0, 0);
insert into Post  (title, content, writeDate, readCount) values ('testPost1', 'test', CURRENT_TIMESTAMP() ,0);

select * from Board where boardID='testBoard';
select * from Post where tile='testPost1';
select * from Save_Board_Post where boardID='testBoard';

I'm rookie in sql. and I'm not native about English.

So, Please forgive my English skills.

Here's my mysql code.

Last five lines are for test. And select from Board and Post is working fine.

But

select * from Save_Board_Post where boardID= 'testBoard';

It doesn't work. This code has no error. but there is no output result.

I guess it means no data in Save_Board_Post table.

I thought REFERENCES command is automatically creation data when insert parent table.

If it does not, please let me know how to automatically creation in relation data.

Answer 1

No, that's not what REFERENCES does. All that your REFERENCES constraints mean is that every row that is inserted (manually) into the Save_Board_Post table must have a boardID and a postID that exist in the Board and Post tables. Nothing is inserted into that table automatically.

If you are trying to represent what board a post is in, the appropriate way to do this would be to make the board ID be a property of the post, e.g.

CREATE TABLE Post (
    postID INTEGER NOT NULL AUTO_INCREMENT,
    boardID CHAR(30) NOT NULL,
    ...
    FOREIGN KEY (boardID) REFERENCES Board(boardID)
);

rather than having an entirely separate table just for that data.