Is there a way of enforcing one-to-one foreign key relationships?

Databases
1

Hi,

I have a table with a foreign key, that is showing as MUL which i think is a many-to-many relationship with the referenced parent table. Is there a way of enforcing a one-to-one relationship between the parent and child tables? I have tried making the child_id unique with no luck.

Thank you.

2

yes it does, except it’s actually a one-to-zero-or-one relationship

no, one-to-one relationships do not always need to be collapsed into a single table

:slight_smile:

3

Yes, that’s it IBazz :slight_smile:

4

I think I get it now.

by making a child table - even for those who have no children - the parent table can be smaller and not stiuffed with null cols.

The child table only stores a row if there is data.

trade-offs

savings
fewer null cols and easier db management

costs
a table join.

that about it?

bazz

5

okay, here it is: drop the id column from the child, and make parent_id the primary key

:slight_smile:

6

Thanks so much r937. That really helped :slight_smile:

7

Curiosity here…(since the OP has got his /her answer now).

if a child table can have only one record per parent, does that not make it a 1:1 relationship which should therefore mean that all data in that relationship should be in one table?

bazz

8

Hmmm, thanks

I see a db streamlining coming soon.

My project is turning into a labour of love :cool:

9

yup

nope – nulls don’t actually “stuff” a table

but only if you need the extra columns – no join required if you don’t (so that’s a benefit, not a cost)

thus, a common reason to split columns off into a 1-1 table is Bfrequency of access[/B]

make sense?

10

Yes, you are right r937 - i’m wanting to restrict each parent to only one child, and vice versa.

11

awesome, that you went to that trouble, man, that’s awesome, thanks

what you have looks completely good except for one minor detail… if you have PRIMARY KEY (id) then you don’t need UNIQUE KEY id (id)

am i right that you are asking how to ensure that each parent can have only one child?

12

Here it is:


parent | CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1



child | CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` 
FOREIGN KEY (`parent_id`) 
   REFERENCES `parent` (`id`) 
   ON DELETE CASCADE
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
13

could you do a SHOW CREATE TABLE please, for both tables

14

Yes, it is a one-to-zero-or-one relationship. Not all parents have children :smiley: