Home » SQL & PL/SQL » SQL & PL/SQL » one-to-one with a join table
one-to-one with a join table [message #439105] Thu, 14 January 2010 04:53 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have a join table for one-to-one relations ship,
the only way I can achieve this is having unique constraint on both the columns , is that right?,
I was assuming any join table first should have compound primary key , but in my case only compound PK will not help , so should I just have unique contraints on both the column , or both compound PK and unique constraint?

attached is the image for one-to-one join table table./fa/7299/0/
Re: one-to-one with a join table [message #439106 is a reply to message #439105] Thu, 14 January 2010 04:58 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
The compund key will not help, you need a seperate unique index on each column.
That said, I'd just merge the three tables into a single one.
Re: one-to-one with a join table [message #439107 is a reply to message #439105] Thu, 14 January 2010 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the only way I can achieve this is having unique constraint on both the columns , is that right?,

If you mean one unique constraint on each of the columns (that is 2 unique constraints), it is correct for the part no more than one but wrong in the part at least one.
If you mean one unique constraints on the 2 oclumns it is wrong.

The way to do this one to one constraint is to get rid of the intermediate table, put the referenced column in each other tables with a not null constraint.

Regards
Michel
Re: one-to-one with a join table [message #439109 is a reply to message #439105] Thu, 14 January 2010 05:18 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One way I can think off that you could get a 1:1 constraint with that intermediate table:

1) Unique Index (or PK) on Employee.emp_id
2) Unique Index (or PK) on Address.address_id
3) Unique Index (or PK) on Emp_add.emp_id
4) Unique Index on Emp_add.addr_id
5) Foreign Key Constraint on Employee.emp_id to Emp_add.emp_id
6) Foreign Key Constraint on Address.address_id to Emp_add.addr_id

If you insert the employee / address before you insert into emp_addr then the foreign keys need to be deferred of course.
Re: one-to-one with a join table [message #439111 is a reply to message #439109] Thu, 14 January 2010 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Foreign constraint column should have NOT NULL constraints otherwise you 0-1:0-1 relation.

But in this case the intermediate table is useless, just add complexity for nothing.

Regards
Michel
Re: one-to-one with a join table [message #439284 is a reply to message #439105] Fri, 15 January 2010 09:21 Go to previous message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
You do not need the middle table. Simply store the emp_id in the address table. This allows you to have a one to many relationship to the addresses.
Previous Topic: Converting Number Inputs to Their corresponding Word-Values
Next Topic: Ledger summery
Goto Forum:
  


Current Time: Sat Oct 01 07:36:12 CDT 2016

Total time taken to generate the page: 0.06581 seconds