How to enforce a unique list of names associated with a name in another table

user_3519718 0
Viewed 33

Consider two related tables, T1 and T2, where the T2.T1_ID values refer to T1.ID values. I'm looking for how to use a constraint, unique index, clever insert/select, or some other approach to prevent ending up with a duplicate T1.NAME associated with the same list of T2.NAMES.

Table T1

ID   NAME
 1  'fred'

Table T2

T1_ID  NAME
  1    'joe'
  1    'jane'

So, 'fred' is associated with 'joe' and 'jane'. I want to prevent inserting into T1 and T2 such that I end up more than one T1 NAME with the same associated T2 NAMEs.

That is, I want the overall attempt to do the following inserts to fail:

INSERT INTO T1 (ID, NAME) VALUES (3, 'fred');
INSERT INTO T2 (T1_ID, NAME) VALUES (3, 'joe');
INSERT INTO T2 (T1_ID, NAME) VALUES (3, 'jane');

Because it would result in the following (invalid) table contents:

Table T1

ID   NAME
 1  'fred'
 3  'fred'

Table T2

T1_ID  NAME
  1    'joe'
  1    'jane'
  3    'joe'
  3    'jane'

That is, 'fred' would be associated with the list 'joe' and 'jane' twice.

Note that it would be ok for there to be a different list of T2 NAMEs associated with the same T1 NAME. That is, the following should be allowed:

INSERT INTO T1 (ID, NAME) VALUES (3, 'fred');
INSERT INTO T2 (T1_ID, NAME) VALUES (3, 'joe');
INSERT INTO T2 (T1_ID, NAME) VALUES (3, 'jane');
INSERT INTO T2 (T1_ID, NAME) VALUES (3, 'kimberly');

Because it would result in the following (valid) table contents:

Table T1

ID   NAME
 1  'fred'
 3  'fred'

Table T2

T1_ID  NAME
  1    'joe'
  1    'jane'
  3    'joe'
  3    'jane'
  3    'kimberly'

The following would also be ok:

INSERT INTO T1 (ID, NAME) VALUES (3, 'fred');
INSERT INTO T2 (T1_ID, NAME) VALUES (3, 'joe');

Because it would result in the following (valid) table contents:

Table T1

ID   NAME
 1  'fred'
 3  'fred'

Table T2

T1_ID  NAME
  1    'joe'
  1    'jane'
  3    'joe'
sql
1 Answers

You should restruchtire your design

First have one name table

user_table

ID   NAME
 1  'fred'
 2  'jane'
 3  'joe'
 4  'kimberly'

Her eon ID is promary KEY and NAE gets ban Unique constraint

And a second table relationships

FK_ID1 FK_ID2
1      2
1      3
2      4

her you have a Priomary key (FK_ID1,FK_ID2)

this will enforce that fred only can befriend jane once, because adding another row would violate the primary key constraint

From my comment:

if you need to know if Fred has the same friends as Jane. the count would be 0

SELECt Count(*) 
FROM (SELECT FK_ID2 FROM relationships WHERE FK_ID1  = 1) f1 
LEFT JOIN (SELECT FK_ID2 FROM relationships WHERE FK_ID1  = 2) f2 ON  f1.FK_ID2 = f2. FK_ID2 
WHERE f2. FK_ID2 is NULL

such bridge table, that define relationships between two entities can contain additional columns, but you should also keep normalisation in mind.

user_51935360
Related