Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    one to one relationship.

    Dear All,
    How to create 1 to 1 relationship by sql statements? i.e. if I have one employee table, and one spouse table. They should be 1 to 1, except you are living in a special country...
    My strategy is:

    1. in employee table
    empID (pk)
    spoID (fk)

    2. in spouse table
    spoID (pk)
    empID (fk)

    but... seems wrong... any suggestions?

    thx,

    neil

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    create table Employee
    (
    emp_id number(2),
    spouse_id number(2) UNIQUE,
    primary key (emp_id),
    Constraint Employee_FK_SPOUSE_ID foreign key (spouse_id) references Employee(emp_id)
    );
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    r123456, please show a couple of rows of sample data for your scheme

    if A points to B as a spouse, does B point to A?

    and if your answer is yes, then please explain how you add them to the table without violating the constraints!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    100 103 tom thumb
    101 null joe bachelor
    102 null mary spinster
    103 100 betty thumb

    assuming tom thumb got added before betty, how do you specify 103 as the fk if betty doesn't exist in the table yet?



    i just thought that it would be helpful to people reading this thread to fully understand what they have to do to get this relationship working properly

    and i think that it must be a reflexive relationship, i.e. if tom has betty as spouse, then betty must have tom as spouse

    otherwise the "who is a spouse of whom" queries get too complicated
    Last edited by r937; 02-21-04 at 09:18.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I do not consider the point you raised to be a problem. If employee '100' is inserted into the table, however the spouse of employee '100' is not present in the table, then null is inserted. Null in this context means "Not applicable", which is correct. The only problem that needs to be addressed, I believe, is ensuring that the following situation does not occur.

    EMP_ID SPOUSE_ID
    ---------- ----------
    1 3
    2 1
    3
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, the point i raised is not a problem

    i was merely suggesting that the relationship cannot be established with a simple insert
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r937
    no, the point i raised is not a problem

    i was merely suggesting that the relationship cannot be established with a simple insert
    ... unless your DBMS supports deferred constraints.

    Of course, even with monogamy people get divorced or widowed and remarry, so a separate marriage table is more realistic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •