Primary key on 2 tables

Question

i have two tables, one 'master' and one 'child' table. Each table has a field named 'ProductNo', which is defined as PRIMARY KEY and UNIQUE. Is it possible to define the field 'ProductNo' in the table 'child' and the same field in table 'master' as PRIMARY + UNIQUE together? ``` master: ID | ProductNo child: ID | MasterID (FK on master.ID) | ProductNo Relation >> 1 (master) : n (child) example data: master: 1 | 1234 2 | 4567 child: 100 | 1 | 3333 101 | 1 | 4444 102 | 2 | 5555 103 | 1 | 1234 <----- NOT ALLOWED! PRODUCT NO ALREADY EXISTING IN TABLE `MASTER` 104 | 2 | 1234 <----- NOT ALLOWED! PRODUCT NO ALREADY EXISTING IN TABLE `MASTER` ``` It is needed to check on inserting/updating table 'child' if 'ProductNo' already exists in table 'master'. How can I define it? Or am I needed to create a trigger for this? TIA Matt

Accepted Answer

no, there is no such thing as composite PKs among tables.

Just for data consistency, if the Ids are the same, you should add a FK from child to the master.

To solve your problem, a trigger with a check like this:

```
if exists (select 1 from master where prodcutId=new_productId)
```

would be a good idea

EDIT:

actually the best idea is to have only one table called product with a ID and a masterID field with a relation to itself. The way you have today Im pretty sure that you have a lot of duplicate data and you are stuck with 2 levels on hierarchy.

Primary key on 2 tables

Solution

Problem