Is it possible for a primary key to be determined by either one table or another?

Question

I'm in a scenario where two child tables (Visual and Hearing Devices) would take in data about the parent (Device) Table and I need what ever primary key (The ID) which is defined in the Visual Device or Hearing Device tables to be set as the primary key in the parent table (Also the ID). I can't figure out how to make this happen. I'm thinking whether you can use an IF condition?

CREATE TABLE w1742302_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);
);

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

  CONSTRAINT pk_hearingDevice_catalogID PRIMARY KEY (deviceCatalogID)
);

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

  CONSTRAINT pk_device_catalogID PRIMARY KEY (deviceCatalogID)
  CONSTRAINT fk_device_catalogID PRIMARY KEY (deviceCatalogID) references ????
);

Note that the references ??? is because idk what to put there

Are you using SQL Server or MySQL? But no (in SQL Server), a Primary key cannot reference another PK. YOu would have to create a foreign key. — Thom A, Nov 22 '19 at 22:15
@Larnu apologies. Its just MySQL. So Im using a Trigger to add the same primary key as hearing and visual devices to the device table. Im wondering if i need to define the device table as foreign key? — AiSirachcha21, Nov 22 '19 at 22:19
This is not clear. Make what happen? What is "set as the primary key in the parent table"? Explain how your DB is constrained & what you want to accomplish without using terms like PK or FK, because we can't trust that you know what they mean. Also we can expect this is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. [ask] — philipxy, Jul 15 '20 at 01:22
@philipxy the reason I asked this in the way I did was because I wanted to know if there was a way to cause a trigger like effect. The parent table "device" has children hearing and visual devices. When I created the objects from Java, I created them such that I would enter all the values for either a hearing or visual devices (just device was abstract). I was wondering if there was a way I could simultaneously create an entry in the device table as soon as I lodged an entry into either the hearing or visual table. But I realized that I could've just used generics to solve it in Java. — AiSirachcha21, Jul 15 '20 at 06:44
Please clarify via edits, not comments. Although that comment is not very clear. Your question isn't phrased in a way that would lead to an answer like the one you gave, although incorporating something like that comment would help. PS You might be interested in faqs about DB/SQL subtyping/inheritance/polymorphism. Eg [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) — philipxy, Jul 15 '20 at 08:10

score 1 · Answer 1 · answered Nov 22 '19 at 22:25

I think that you actually want things the other way around.

You should have the children table reference the primary key of the parent table instead. This seems to me like the correct way to represent the parent/children relationship in your data (ie a child always has a parent).

CREATE TABLE w1742302_device (
  deviceCatalogID varchar(5),
  deviceCatalogName varchar(20),
  deviceDescription varchar(300),
  availabilityStatus boolean,
  CONSTRAINT pk_device_catalogID PRIMARY KEY (deviceCatalogID)
);

CREATE TABLE w1742302_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 w1742302_device(deviceCatalogID)
);

CREATE TABLE w1742302_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_visionDevice_catalogID FOREIGN KEY (deviceCatalogID) 
    REFERENCES w1742302_device(deviceCatalogID)
);

Demo on DB Fiddlde

so I get what you're saying but wouldn't it make sense to store these as I've done above? Because think of it this way. I have a record called deviceCatalogName in my device table. I need to record whether or not it is a visual or hearing device. — AiSirachcha21, Nov 22 '19 at 22:31
If so, then my trigger would cause my element to be recorded to the device table once I enter it along with the name of the Catalog i.e. Hearing or Visual — AiSirachcha21, Nov 22 '19 at 22:32
@AiSirachcha21: *I have a record called deviceCatalogName in my device table. I need to record whether or not it is a visual or hearing device*: actually, you don't. You can get that information by looking up that information in the children table. A parent does not need to know about its child(ren); a child do know its parent. — GMB, Nov 22 '19 at 22:41

score -1 · Answer 2 · answered Nov 22 '19 at 22:22

Okay so I figured it out. I think

delimiter |
 CREATE TRIGGER replicateVisualDevice AFTER INSERT ON w1742302_visual_Device
 FOR EACH ROW
    BEGIN
        INSERT INTO w1742302_device(deviceCatalogID)
        VALUE (NEW.deviceCatalogID);
    END;
 |


 delimiter |
 CREATE TRIGGER replicateHearingDevice AFTER INSERT ON w1742302_hearing_Device
 FOR EACH ROW
    BEGIN
        INSERT INTO w1742302_device(deviceCatalogID)
        VALUE (NEW.deviceCatalogID);
    END;
 |

So this trigger seems to add it to the table regardless of whether or not it is a foreign key.

@Strawberry okay see its like this. I could do it the other way no doubt. But along under the "catalog" record i need to record whether or not it is a visual or hearing device. So im creating two for that purpose. I could do it the other way but i have no idea how to implement an IF condition for my case — AiSirachcha21, Nov 23 '19 at 08:08

Is it possible for a primary key to be determined by either one table or another?

2 Answers2