Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Unanswered: Foreign Key Doubts mysql

    I have 2 questions regarding foreign Key with mysql.

    Question 1

    TABLE A -- ID (PK), col1, col2
    TABLE B -- ID (PK), col1, col2

    I want to create a table which would be like a column value either consists of TABLE A or TABLE ID (PK) is must.

    TABLE C -- ID (FOREIGN_KEY on either TABLE_A_ID/ TABLE_B_ID), col1, col2 I really dont know weather it is possible or not, is that a following RDBMS rules

    Question 2

    TABLE B contains a column which will be holding default value 0 or TABLE A ID (PK) value. it should not contain any other value

    Thanks in advance. Give your valuable suggestions

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no not possible
    think on it how would you 'know' which table had the 'correct' pk reference, short of storing something which identified which table was the 'parent'. granted you could create some rule and enforce it manually (eg a negative number is a FK to table A, a positive number is a FK to table B. but you'#d have to do the 'enforcing' bit yourself you couldn't expect the INDDOB tables to do it for you. There's nothing magical about PK's & FKs', essentially they are a way of enforcing data integrity. if you do that role yourself through code then you don't 'need' to use the SQL engines Pk/FK...

    ..but its a 'kin stupid idea in my books

    waht is to stop you setting the default value from table B as the value in Table A, and enforce the PK/FK as normal
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It may be possible, but without some sample data the explanation isn't good enough for me to be sure.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Quote Originally Posted by healdem View Post
    no not possible
    think on it how would you 'know' which table had the 'correct' pk reference, short of storing something which identified which table was the 'parent'. granted you could create some rule and enforce it manually (eg a negative number is a FK to table A, a positive number is a FK to table B. but you'#d have to do the 'enforcing' bit yourself you couldn't expect the INDDOB tables to do it for you. There's nothing magical about PK's & FKs', essentially they are a way of enforcing data integrity. if you do that role yourself through code then you don't 'need' to use the SQL engines Pk/FK...

    ..but its a 'kin stupid idea in my books

    waht is to stop you setting the default value from table B as the value in Table A, and enforce the PK/FK as normal
    Thanks for your reply

  5. #5
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Quote Originally Posted by Pat Phelan View Post
    It may be possible, but without some sample data the explanation isn't good enough for me to be sure.

    -PatP
    it's pretty simple

    Entity Table (A) and Sub_Entity Table (B). One to many relationship.
    configuration table (C)
    entity will have its configuration ( A is one to one with C).
    sub entity has configuration, its parent doesn't have it (B is one to one C)

    Table A
    --------
    ID | Col1
    1 | One
    2 | Two

    Table B
    --------------
    ID | A_ID | col1
    1 | 1 | One
    2 | 1 | oneTwo
    3 | 2 | TwoOne

    Table C

    A_ID | B_ID | col1
    null | 1 | conf1
    null | 2 | conf2
    2 | null | conf3

    the problem is if A_ID has value then B_ID has null vice versa. I thought can i maintain it has single column?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, MySQL doesn't support CHECK CONSTRAINTS which are needed for this kind of integrity.

    MariaDB does support them, as do DB2, Oracle, Microsoft SQL, PostgreSQL, etc.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Quote Originally Posted by Pat Phelan View Post
    No, MySQL doesn't support CHECK CONSTRAINTS which are needed for this kind of integrity.

    MariaDB does support them, as do DB2, Oracle, Microsoft SQL, PostgreSQL, etc.

    -PatP
    Thanks

Tags for this Thread

Posting Permissions

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