3

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

frgtv10
  • 5,300
  • 4
  • 30
  • 46
  • 3
    what you mean by primary + unique??? primary keys are by default unique. The difference between the two is only primary key does not accept null values – Code Prank Apr 27 '12 at 09:35
  • Do you mean that a row in the `child` table cannot have the same `ProductNo` as a row in the `master` regardless of whether other IDs match or not? Some example data and expected results (update allowed or rejected) would be helpful. – onedaywhen Apr 27 '12 at 09:44
  • Its not allow to have a ProductNo in table `child` which is already in table `master`. – frgtv10 Apr 27 '12 at 09:47
  • @frgtv10: Yes it is allowed, but it is redundant (because it could depend on the FK as well) To the OP: why the surrogate keys, *if* you have surrogate keys: then use them (the natural key becomes dependent on it). – wildplasser Apr 27 '12 at 09:55

5 Answers5

4

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.

Diego
  • 34,802
  • 21
  • 91
  • 134
  • i updated example data in main thread. think this still would be the best way. right? – frgtv10 Apr 27 '12 at 09:51
  • using the trigger works perfect. i cannot use 1 table, cause the `master` has a lot more fields then the table `child` – frgtv10 Apr 27 '12 at 10:00
2

(Original answer) You can declare a foreign key from master to child, even if that foreign key points to the primary key of child. This would be a one to zero-or-one relationship, and is not that uncommon. A row cannot exist in child without a matching row in master already being inserted, but a row can exist in master without a matching child row. Your inserts therefore need to happen in the order master then child.

(Edited in light of question edit) HOWEVER, in your case, the column you are referring to looks like it should not actually be the primary key of either table, but rather you have a separate primary/foreign key, and the column in question needs to be unique across the two tables, which has become clear now you've edited some sample data into your question. In this case, you'd be best to use a trigger on both tables, to check existence in the other table and prevent the insert/update if the ProductNo already exists.

David M
  • 71,481
  • 13
  • 158
  • 186
1

Just as @DavidM said, it can be done, but it seems you are with some modelling issues. First, if you have a natural primary key ProductNo, why do you define a surrogate ID? The other thing you might consider is to combine these two tables into a single one (as might make sense for most of 1-to-1 cases).

Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47
  • This could be true, yeah. But the table `master` has about 40 fields and in the table `child` i only got 5 fields. Thats why we're using 2 tables. – frgtv10 Apr 27 '12 at 10:05
  • @frgtv10, modelling choices are very specific to the solution, and of course you have your reasons; my point is just to consider the benefits and tradeoffs caused by this choice: this problem you presented is just not an issue if the 1-to-1 was modeled as a single table. – Gerardo Lima Apr 27 '12 at 10:11
1

Are you sure you need the two tables? Keep just one, having productID plus parentID. Then productID can be a primary key and auto increment, while everything having a parentID other than null (f.keyed to the same table) would be a child item.

Laszlo T
  • 1,165
  • 10
  • 22
0

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

Shamjith Haridasan
  • 146
  • 3