CODEWITHSUNDEEP
mysql
AiSirachcha21
AiSirachcha21

Reputation: 119

I need an ID to be inserted in a sub-entity when I insert it into the parent table

So I have 3 tables that are still in the logical ERD state. Devices, HearingDevices and Vision Devices. Hearing and Vision devices are derived from the Device entity.

I'm just pondering, is there any way I can create a entry where when I enter a device with ID 001 in the device entity, it will replicate in my two sub entities i.e. hearing and vision device entities.

Even if thats not possible. How would I display the corresponding hearing or vision device when I print out the device information ( I know you can use a simple embedded select statement but I'm looking for alternative options )

My code is down below,

CREATE TABLE Device (
   deviceCatalogID varchar(5),
   deviceCatalogName varchar(20),
   deviceDescription varchar(300),
   availabilityStatus boolean,

   CONSTRAINT pk_device_catalogID PRIMARY KEY (deviceCatalogID)
 );

 CREATE TABLE Visual_Device (
   deviceCatalogID varchar(5),
   frBrand varchar(20),
   frModel varchar(20),
   lensSerialN varchar(20),
   lensVisionType varchar(20),
   lensTint set('1','2','3'),
   lensThinnessLevel set('1','2','3'),

   CONSTRAINT pk_visionDevice_catalogID PRIMARY KEY (deviceCatalogID),
   CONSTRAINT fk_visualDevice_catalogID FOREIGN KEY (deviceCatalogID) references device(deviceCatalogID)
 );

 CREATE TABLE Hearing_Device (
   deviceCatalogID varchar(5),
   hdMake varchar(20),
   hdModel varchar(20),

   CONSTRAINT pk_hearingDevice_catalogID PRIMARY KEY (deviceCatalogID),
   CONSTRAINT fk_hearingDevice_catalogID FOREIGN KEY (deviceCatalogID) references device(deviceCatalogID)
 );

Upvotes: 0

Views: 53

Answers (1)

Roger Kreft
Roger Kreft

Reputation: 200

Read about database triggers: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

You can replicate entries in devive to the other two tables this way: https://www.db-fiddle.com/f/VAfRc5GA4sjsV3ZYpiVwq/0

Upvotes: 1

Related Questions