Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    Unanswered: rant: Referential Integrity not possible in SQL Server

    Take the folder example:

    CREATE TABLE members
    (
    member_username VARCHAR(25) NOT NULL PRIMARY KEY
    );

    CREATE TABLE pm_folders
    (
    folder_name VARCHAR(25) NOT NULL,
    member_username VARCHAR(25) NOT NULL REFERENCES members(member_username),
    PRIMARY KEY (folder_name, member_username)
    );

    The members table has various other references so SQL SERVER and because of that, it prevents you from doing a "cascade" to pm_folders due to cyclic foreign keys. No other database vendor has this limitation.

    Therefore, if you try to change the username, the update fails because you must update the pm_folder's username first. Ah, but you can't......because pm_folder's username depends on the members username.

    In Oracle, this would be no problem. In Postgre, this would be no problem. In MySql, this would be no problem. In Access, this would be no problem. In DB2, this would be no problem.

    SQL Server is the only one with the problem. Is there even a point to using referential integrity in SQL Server???

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I agree. Support for cascading updates is seriously defficient. I'm not sure why Microsoft hasn't been able to put this right. Customers have been asking for it for a long time.

    http://connect.microsoft.com/SQLServ...dbackID=125309
    http://connect.microsoft.com/SQLServ...dbackID=126159
    http://connect.microsoft.com/SQLServ...dbackID=307723

  3. #3
    Join Date
    Jun 2009
    Posts
    66
    They've been considering it for the next release since SQL Server 2000!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just playing devil's advocate here; but why have you picked a member_username as a key if it's value changes? That makes it appear to be a poor candidate imo.

    A surrogate key would solve this issue.

    Hey - this forum works just like that!
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    It seems obvious to me that member_username is a sensible choice for a candidate key. How else would you propose to stop two users getting the same user name?

    All keys are equally important in the relational model so whether or not you use a surrogate doesn't change the problem because there can still be good reasons for referencing some other key. You might also want to update the surrogate key (a common requirement if you merge previously separate data sets for example).

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If anyone can tell me the value of this thread, I won't lock it.
    There is no question.
    It is restating a well documented deficiency in SQL Server RI.

    We get enough rants without actually bypassing the question in the first place.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, not explicit enough again - "...member_name as the primary key"

    member_name can happily be a unique_key, but I would use a surrogate in your foreign key references.
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Since I have absolutely no problem implementing cascading updates on the DDL you posted, I have no idea what your problem is.
    SQL Server is not going to allow cascading if they result may not be consistent, you can use triggers to perform you own cascading logic, and most problems can be resolved by using surrogate keys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2009
    Posts
    66
    Bad example above, try implementing cascades on the example below using surrogates:

    CREATE TABLE topics
    (
    topic_id INT IDENTITY(1,1) NOT NULL,
    topic_name VARCHAR(25),
    PRIMARY KEY (topic_id)
    );

    CREATE TABLE topic_posts
    (
    post_id INT IDENTITY(1,1) NOT NULL,
    topic_id INT,
    post_reply_id INT,
    post_subject_name VARCHAR(25),
    PRIMARY KEY(post_id),
    FOREIGN KEY (topic_id) REFERENCES topics (topic_id) ON CASCADE UPDATE,
    FOREIGN KEY (post_reply_id) REFERENCES posts (post_id) ON CASCADE UPDATE
    );


    The worlds only database that cannot implement foreign constraints properly, SQL Server, should complain that you have a cyclic foreign key issue. Postgre, Oracle, Firebird, Mysql, Access, SQLite can all do cyclic foreign key cascades.

    Why would you use triggers instead of having the DRI do it automatically?
    Last edited by sqlguru; 07-07-09 at 12:13.

  10. #10
    Join Date
    Mar 2009
    Posts
    349
    Quote Originally Posted by blindman
    I have no idea what your problem is.
    I have some ideas other than his 'puter is broke.

    This new set of DDL looks like it is missing a table.

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SQLguru, You still didn't test your code did you?

    Quote Originally Posted by sqlguru
    The worlds only database that cannot implement foreign constraints properly
    Not the only one and not even the worst in some cases. There are problems that are not unique to SQL Server. Try implementing a referential constraint that is mandatory on both sides of the foreign key. Or a foreign key that doesn't match the column list in a UNIQUE or PRIMARY KEY constraint. Or a foreign key referencing a view. Referential integrity is basically crippled with stupid limitations in SQL.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Since when did the action of cascading (which I have NEVER been a fan of) become Referential Integrity?

    Changing a primary Key loses valuable information

    In reality, you should add a new key and retain the old (hopefully with effective and termination dates) if the table is a Parent and has Children

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2009
    Posts
    66
    Why would you require referential constraint on both sides? This sounds like a design flaw.
    Example:
    Topic table has a LastPostId
    Post table has a TopicId

    They both refer to each other. In such cases, maybe an order/path definition is needed. But you can easily find the last post used the post_created in post table etc.


    Why would a foreign key column list NOT match the column list of a primary key constraint? example?


    Why would a foreign key need to reference a view? example?
    Last edited by sqlguru; 07-07-09 at 13:33.

  14. #14
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by Brett Kaiser
    Since when did the action of cascading (which I have NEVER been a fan of) become Referential Integrity?

    Changing a primary Key loses valuable information

    In reality, you should add a new key and retain the old (hopefully with effective and termination dates) if the table is a Parent and has Children

    MOO
    Since when are effective/termination dates candidates for a primary key??? Other than that, I don't see how it could result in loss of information if you design your model properly.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sqlguru
    Bad example above, try implementing cascades on the example below using surrogates:

    CREATE TABLE topics
    (
    topic_id INT IDENTITY(1,1) NOT NULL,
    topic_name VARCHAR(25),
    PRIMARY KEY (topic_id)
    );

    CREATE TABLE topic_posts
    (
    post_id INT IDENTITY(1,1) NOT NULL,
    topic_id INT,
    post_reply_id INT,
    post_subject_name VARCHAR(25),
    PRIMARY KEY(post_id),
    FOREIGN KEY (topic_id) REFERENCES topics (topic_id) ON CASCADE UPDATE,
    FOREIGN KEY (post_reply_id) REFERENCES posts (post_id) ON CASCADE UPDATE
    );


    The worlds only database that cannot implement foreign constraints properly, SQL Server, should complain that you have a cyclic foreign key issue. Postgre, Oracle, Firebird, Mysql, Access, SQLite can all do cyclic foreign key cascades.

    Why would you use triggers instead of having the DRI do it automatically?
    Still missing at least one table in that design.
    SQLGURU, I understand what "SQL" stands for. What is G.U.R.U. an acronym for?
    Just curious....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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