Geek asks and answers

Primary key on 2 tables

I have two tables, one "master" and one "children's" table. Each table has a "ProductNo" field, 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 the 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`

You need to check to insert / update the table "child" if "ProductNo" already exists in the table "master".

How can I identify it? Or do I need to create a trigger for this?

TIA Matt

+3
source share
5 answers

No, there are no composite PKs among the tables.

, , FK .

, :

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

EDIT:

, , masterID . , , , , .

+4

( ) , . , . , master . , .

( ). , , , , / , , , . , /, ProductNo .

+2

, @DavidM , , , , . -, ProductNo, ID? , , - ( 1 1).

+1

Are you sure you need two tables? Keep only one with productID plus parentID. Then productID can be a primary key and auto-increment, while everyone having a non-zero parent ID (f.keyed to the same table) will be a child.

+1
source

You can add the ProductNo column to the child table and add the foreign key reference to the parent table.

0
source

More articles:


All Articles