0

I have been trying implementing inheritance in MySql. Even though I am able to execute the following code, MySql workbench fails creating an ER diagram and just stops working and closes without giving an error. code was based from the article : https://www.sqlteam.com/articles/implementing-table-inheritance-in-sql-server.

Students and Teachers have a lot of in common(name, surname...) but they also differ by some attributes. A person cannot be both a student or a teacher at the same time.

UPDATE: eer diagram executes if I remove from the code this line. Is there any different way to write this line of code?

PersonTypeID int not null default X check (PersonTypeID = X)

code:

create table PersonType (PersonTypeID int primary key, PersonType varchar(10));
insert into PersonType
select 1,'Student' union all
select 2,'Teacher' union all
select 3,'Parent';

create table People
(
  PersonID int primary key, 
  PersonTypeID int,
  foreign key (PersonTypeID) references PersonType(PersonTypeID), 
  Name varchar(10),
constraint People_AltPK unique (PersonID,PersonTypeID)
);

create table Students
(
  PersonID int primary key, 
  PersonTypeID int not null default 1 check (PersonTypeID = 1), -- student
  EnrollmentDate datetime, 
foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID)
);

create table Teachers 
(
  PersonID int primary key, 
  PersonTypeID  int not null default 2 check (PersonTypeID = 2), -- teacher
  HireDate datetime, 
 foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID)
);
14
  • 1
    The workbench may have trouble executing a PNG. Jan 4 at 19:33
  • @GerardH.Pille What's a png? Do you mean a png image format? Jan 4 at 19:58
  • 1
    Yes, as the one you added to your question. Jan 4 at 20:13
  • What do you mean by a workbench executing a PNG? What png is the workbench executing? Jan 4 at 20:15
  • 1
    What @GerardH.Pille is telling you in a humoristic way is that we cannot reproduce the issue as we cannot copy and paste the code to test. Sadly it's a picture and our Workbenches cannot execute a picture. And yours likely cannot too. So you likely have the plain text of the code and that's what you should have posted, not a picture. You can edit the question to correct that.
    – sticky bit
    Jan 4 at 21:02
0

Instead of using

PersonTypeID int not null default X check (PersonTypeID = X)

use

   PersonTypeID  int not null default 1,
    foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID),
   constraint  check (PersonTypeID = 1),

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.