Professional Web Applications Themes

Foreign key constraint against primary keys in two different table. - Microsoft SQL / MS SQL Server

Say I have 3 tables A, B, C. Table A has primary key identity column A_primary_key. Table B has primary key identity column B_primary_key. C table has two columns: a foreign key column primary_key_from_A_or_B, and a bool column is_A. How can I write a constraint that in table C: 1. if is_A is true, then primary_key_from_A_or_B must reference to A_primary_key in A, and 2. if is_A is false, then primary_key_from_A_or_B must reference to B_primary_key in B Thank you very much for the help! Bill...

  1. #1

    Default Foreign key constraint against primary keys in two different table.

    Say I have 3 tables A, B, C. Table A has primary key identity column
    A_primary_key. Table B has primary key identity column B_primary_key.
    C table has two columns: a foreign key column primary_key_from_A_or_B,
    and a bool column is_A. How can I write a constraint that in table C:

    1. if is_A is true, then primary_key_from_A_or_B must reference to
    A_primary_key in A, and
    2. if is_A is false, then primary_key_from_A_or_B must reference to
    B_primary_key in B

    Thank you very much for the help!
    Bill
    Bill Guest

  2. #2

    Default Re: Foreign key constraint against primary keys in two different table.

    You can't. The easiest way is to create the third table with a column for
    each FK. If you don't want to do this, then triggers are your only option
    AFAIK.

    Another alternative is to create a fourth table that has the same general
    structure. The 3rd is devoted to rows associated with table A, the fourth
    is associated with rows from table B. You could then define a view that
    unions the two tables if you need to see the rows together.

    "Bill" <com> wrote in message
    news:google.com... 


    Scott Guest

  3. #3

    Default Re: Foreign key constraint against primary keys in two different table.

    First of all, the concept of "primary key identity column" is absurd. A
    key is a set of attributes that unique identify an entity in the reality
    from which you derived your data model. The IDENTITY property is
    derived from the physical state of a particular machine that is holding
    the data. There is no BOOLEAN datatype in SQL.

    You are trying to fake an OO design in SQL, aren't you? This might be
    what you are after --

    CREATE TABLE Foobar
    (foo_key INTEGER NOT NULL UNIQUE
    foo_flag CHAR(1) DEFAULT 'A' NOT NULL
    CHECK(foo_flag IN ('A', 'B')),
    ...
    PRIMARY KEY (foo_key, foo_flag));

    CREATE TABLE Foo_A
    (foo_key INTEGER NOT NULL UNIQUE
    foo_flag CHAR(1) DEFAULT 'A' NOT NULL
    CHECK(foo_flag = 'A'),
    ...
    FOREIGN KEY (foo_key, foo_flag)
    REFERENCES Foobar (foo_key, foo_flag),
    PRIMARY KEY (foo_key, foo_flag));

    CREATE TABLE Foo_B
    (foo_key INTEGER NOT NULL UNIQUE
    foo_flag CHAR(1) DEFAULT 'B' NOT NULL
    CHECK(foo_flag = 'B'),
    ...
    FOREIGN KEY (foo_key, foo_flag)
    REFERENCES Foobar (foo_key, foo_flag),
    PRIMARY KEY (foo_key, foo_flag));

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Foreign key constraint against primary keys in two different table.

    >>>>
    The IDENTITY property is
    derived from the physical state of a particular machine that is holding
    the data
    <<<<<

    No it isn't, the IDENTITY property is simply a tool for generating an
    auto-number. This is valid in the most recent ANSI standard.

    An example of its use is (and I use your own schema)....

    CREATE TABLE Foobar
    (foo_key INTEGER NOT NULL UNIQUE IDENTITY
    foo_flag CHAR(1) DEFAULT 'A' NOT NULL
    CHECK(foo_flag IN ('A', 'B')),


    How else would you create foo_key? Perhaps you would reinvent the wheel and
    write your own kludgy sequence generatation code? I tend to like to use
    functionality that is proven and tested and is already available within the
    product I'm writing for in our case Microsoft SQL Server.

    Portability is not a concern - play the stats game - a very small fraction
    of a percent of people actually require portability and even then,
    portability is just a pipe dream and isn't really achievable in todays
    client (and nor does it need to be speaking from a business perspective).

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  5. #5

    Default Re: Foreign key constraint against primary keys in two different table.

    >> None of the leading database vendors - Microsoft, IBM nor Oracle
    support true portability - you cannot simply take a database schema
    un-modified from one product to the next; nor can you do that with the
    application either. <<

    You might want to tell Janet Perna at IBM that she is doing the
    impossible with the DB2 product line. Tell the DoD that the FIPS
    Flagger does not help. If you begin with portability as a design goal
    you can minimize almost all of the pain in the code transfers between
    products. Setting up the physical configuration is more trouble.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  6. #6

    Default Re: Foreign key constraint against primary keys in two different table.

    >> Here in the UK car number plates are formed from an area code and
    then a sequence number slapped on the end, no check digit - i remember
    when I bought a new motor bike and was asked to choose my registration
    number from a sheet of about 100. <<

    The UK postal codes are also pretty bad. Each state over here has its
    own auto tag system -- some with check digits and some without, but most
    follow a 6-8 alphanumeric. You have to get new tags when you move to a
    new state -- it is more about taxes than identifying property.

    That is why we have a VIN number. I am sure that you did not have to
    burn a VIN number on the engine block of the motorbike from a list of
    about 100 choices.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  7. #7

    Default Re: Foreign key constraint against primary keys in two different table.

    >>>>You might want to tell Janet Perna at IBM that she is doing the
    impossible with the DB2 product line. Tell the DoD that the FIPS
    Flagger does not help. If you begin with portability as a design goal
    you can minimize almost all of the pain in the code transfers between
    products. Setting up the physical configuration is more trouble.
    <<<<

    If you start with portability as a design goal then you are ignoring the
    more important business goal - what does the business need in order to
    function?

    All too often IT a specific group of IT 'professionals' adopt the approach
    that IT is more important than the business - that is not true; taking that
    approach is a major reason projects fail or come in greatly above cost.

    Portability will add a considerable cost to a project in terms of design;
    development; testing; implementation and maintainability - that needs to be
    weighed up against the business justification and valued accordingly.

    As a businessman and an IT professional myself I want a solution that meets
    two requirements a) allows my business to function and b) can be implemented
    and ongoing costs are cheaper than my competitors.

    Portability should stay in educational establishments and taught as a 'this
    is how the real world should operate' rather than 'this is how the real
    world of business operates'.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  8. #8

    Default Re: Foreign key constraint against primary keys in two different table.

    Thank you Joe for the reply. I eventually wrote a trigger as follows.
    Would you take a look to see if it is the proper way to achieve the
    goal? -Bill-

    CREATE TRIGGER My_Test_RI ON C
    FOR INSERT, UPDATE AS
    IF NOT EXISTS
    (SELECT 'True'
    FROM inserted, A
    WHERE inserted.primary_key_from_A_or_B = A.A_primary_key
    AND inserted.is_A = 1)
    BEGIN
    IF NOT EXISTS
    (SELECT 'True'
    FROM inserted, B
    WHERE inserted.primary_key_from_A_or_B = B.B_primary_key
    AND inserted.is_A = 0)
    BEGIN
    RAISERROR ('Error, invalid primary_key_from_A_or_B', 16, 1)
    ROLLBACK TRAN
    END
    END


    Joe Celko <edu> wrote in message news:<#phx.gbl>... 
    Bill Guest

Similar Threads

  1. Foreign key constraint fails
    By Nuno in forum MySQL
    Replies: 3
    Last Post: February 13th, 03:06 PM
  2. Replies: 11
    Last Post: June 2nd, 04:01 AM
  3. Reading Foreign Keys From an Access Table with ADO
    By Bernhard in forum ASP Database
    Replies: 0
    Last Post: September 15th, 08:41 AM
  4. Can Not Access Foreign Key Constraint
    By Rajesh Tiwari in forum ASP.NET General
    Replies: 0
    Last Post: June 30th, 02:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139