I create a foreign key, build a diagram with the help of the Reverse Engineer in mySQL workbench, in the diagram the connection is designated as optional. How to create a binding relationship?

Ancestor table:

CREATE TABLE `discipline` ( `short_disc_name` varchar(50) CHARACTER SET utf8 NOT NULL, `full_name` varchar(150) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`short_disc_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table with foreign key:

 CREATE TABLE `teacher` ( `full_name` varchar(100) CHARACTER SET utf8 NOT NULL, `post` varchar(100) CHARACTER SET utf8 DEFAULT NULL, `qualification` varchar(100) CHARACTER SET utf8 DEFAULT NULL, `autohrity` tinyint(1) DEFAULT NULL, `discipline` varchar(50) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`full_name`), KEY `discipline` (`discipline`), CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`discipline`) REFERENCES `discipline` (`short_disc_name`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here is the connection: This is how the connection works:

    1 answer 1

    You can run for a long time for the consequences of the wrong assumption, hehe.

    You have everything right: there is a foreign key and a limit on non- NULL . No communication excluded.

    Oh yeah, dotted. Dotted line is not about optional. He points out that the link is not identifiable . And here it is: the foreign key of this link is not part of the primary key of the table.

    In this case, the link would be identifiable if the primary key teachers contained discipline . I'm not sure that this is a good idea for your case, because for a formally accurate identification of the teacher in the database, you need not only his name, but also discipline. A bit strange. Do you have a teacher who can teach several disciplines? Hmm ... Decide.

    You can check this out by drawing such a link in the editor and looking at the changes.

    • Understood you, thank you :) Such a question: can a foreign key be included in the primary key? This is not a question for this case. - xd720p
    • @ xd720p m ... If a "foreign key" means a column (and not constraint on it), then yes, of course. - D-side
    • for example, there is a table Attendance ( account number, date , presence). The attribute number of the record is obtained from the connection with the Student table ( number of the record , full name, etc.), where it is the primary key. Is it possible in the Attending table to make such a composite key [Ref. Number, date]? After all, only this key can uniquely identify a visit. - xd720p
    • @ xd720p if the student's primary key is his record number, then yes, this is a good example of an identifying relationship. - D-side