SQL Server: What does this do?



  • I found this while looking through the tables in a SQL Server database my team administers. I thought I had a reasonably good grasp of SQL, but this confuses me. Does it actually do anything? ID is the primary key, too, by the way.

    ALTER TABLE [dbo].[ReferenceTable1]  WITH CHECK
    ADD CONSTRAINT [FK_ReferenceTable1_ReferenceTable1] FOREIGN KEY([ID])
    REFERENCES [dbo].[ReferenceTable1] ([ID])
    GO
    
    ALTER TABLE [dbo].[ReferenceTable1] CHECK CONSTRAINT [FK_ReferenceTable1_ReferenceTable1]
    GO

  • FoxDev

    It's making the ID column a foreign key… of itself :wtf:


  • BINNED

    Didn't we have someone's screenshot of this kind of thing recently?

    @Weng, perhaps?



  • Yeah this

    I was just about to post it.



  • Someone clicked "Add Constraint" in SQL Management Studio and saved the default constraint it made for them. It does nothing, but is valid syntactically.

    I like the UI concept, because it allows them to code an add button that doesn't put the UI in an invalid state and doesn't involve modal popups. But it does cause some people to add cruft to their databases.



  • So, it doesn't actually do anything. I spent, like, five minutes staring at that trying to figure out how that actually constrained the data at all.


  • FoxDev

    @Robert_Morson said:

    So, it doesn't actually do anything

    Correct; conceptually, it's a no-op.


  • Garbage Person

    Incorrect. I did not post a screenshot. I posted a photograph.


  • BINNED

    Indeed. The relevance of this fact has now been noted.


  • BINNED

    I've seen this a few times. I think it's the result of people using the GUI to add foreign keys instead of typing in SQL like $deity and Nature intended. In SSMS, if you do "Design" on a table, hit the "Relationships" button, and then "Add", that's what you get by default (thanks Microsoft!).



  • It surprises me that this can be considered valid. If it does what it reads, it basically says you can't add an [ID] to the table that isn't already in the table. Which is idiotic.



  • When @Weng first posted it, I suspected that it might prevent inserts. Testing showed it doesn't.



  • So, basically, a long-winded NO-OP.



  • In the other thread, someone theorized that it was probably a "safe" default so you wouldn't create an FK to nothing and blow up your database.


  • BINNED

    @hungrier said:

    a "safe" default so you wouldn't create an FK to nothing and blow up your database

    Anyone who needs that shouldn't be allowed anywhere near a database.

    EDIT: this is what happens when you try to satisfy the "anyone should be able to use a computer" brain-worm.



  • @hungrier said:

    In the other thread, someone theorized that it was probably a "safe" default so you wouldn't create an FK to nothing and blow up your database.

    That was me. What I said was that it would be a safe constraint that wouldn't put the UI in an invalid state. It's generally nicer if an application prevents you from entering invalid information rather than yelling at you when you push the OK button. There are so many things you can change in the "Design Table" screen in SSMS that you could easily put someone in a situation where they spend a lot of time looking for the problem.

    it was all about a friendly UI, not about data integrity.



  • @hungrier said:

    In the other thread, someone theorized that it was probably a "safe" default so you wouldn't create an FK to nothing and blow up your database.

    @Jaime said:

    it was all about a friendly UI, not about data integrity.

    What kind of people work with MSSQL that MS has to protect them from fucking things up?



  • It's just Microsoft going the extra mile to make user interfaces that aren't evil.



  • @CoyneTheDup said:

    It surprises me that this can be considered valid. If it does what it reads, it basically says you can't add an [ID] to the table that isn't already in the table. Which is idiotic.

    Actually all it says that you're not allowed to do anything that would create a dangling foreign key. And since any foreign key you could possibly create in this degenerate case always links to an existing key because it is the key it links to, there's no problem.



  • @antiquarian said:

    EDIT: this is what happens when you try to satisfy the "anyone should be able to use a computer" brain-worm.

    Right! We manly-men, living in a world where network connectivity never goes down, the power never goes out, and there's no such thing as a "typo" do not need your foolish "safety features!"



  • Well, the question is whether is validates the foreign after adding a new value or before. If it validates the constraint and then decides whether to add the value, then @CoyneTheDup is correct and you could never add anything. However, since I can verify that it is possible to add data to the table, it must add the value and then check to see if the value is there. And then, potentially (for a real foreign key, at least) delete it before throwing an error message. Is that really the best way to do it?


  • BINNED

    @blakeyrat said:

    Right! We manly-men, living in a world where network connectivity never goes down, the power never goes out, and there's no such thing as a "typo" do not need your foolish "safety features!"

    :strawman.mp3:



  • @Robert_Morson said:

    Is that really the best way to do it?

    You're guessing at the underlying mechanism. It's entirely possible that the constraint engine sees that as a no-op and simply ignores it.



  • Inserts happen first, then are discarded if invalid. You can see this behavior in skipped ID numbers from failed inserts due to foreign keys.

    I assume it looks like this:

    1. Insert value - Non final committed
    2. Check constraints (including FK)
    3. FK can see pending to commit data causing a valid item
    4. Insert succeeds.

    Now, I wonder what would happen if you could prevent the FK from reading uncommitted data. (transaction isolation level, for example)



  • @antiquarian said:

    EDIT: this is what happens when you try to satisfy the "anyone should be able to use a computer" brain-worm.

    Nobody listen when I say that if there is a driving license, and special version for truck and other, we should also have computer-usage license. And a computer-police to enforce them : making a database with more than one table in Excel is a felony.



  • @antiquarian said:

    Anyone who needs that shouldn't be allowed anywhere near a database.

    EDIT: this is what happens when you try to satisfy the "anyone should be able to use a computer" brain-worm.

    And what a somber warning it is, too. I can only thank my lucky stars that I haven't been the victim of a harmless no-op.

    @Jerome_Grimbert said:

    Nobody listen when I say that if there is a driving license, and special version for truck and other, we should also have computer-usage license. And a computer-police to enforce them : making a database with more than one table in Excel is a felony.

    How's that lisp-based OS coming along?



  • In other news: a coworker recently solved a problem where users could create an entry with invalid data that would cause the program1 to crash2 when displaying it by making a form field read-only3.

    1the gui, that is.
    2requiring someone to edit the live DB to sort it out.
    3it was literally just a foreign-key constraint. Users would try typing someone's name into the text box, but what they actually needed to do was click the search button, and pick the relevant person out of a list.


  • ♿ (Parody)

    @Jaime said:

    it was all about a friendly UI, not about data integrity.

    Hmm...I swear I replied to this last night. Must have gotten eaten in the outage or whatever...I recall saying something like:

    How is this friendly UI? Why give me something that's nonsense that I have to change instead of empty / unselected elements or whatever? If I forget to change something, can I get things into a bad state? I've never used this, so I don't know, but it seems like it would be easier to detect if the user left something blank.



  • @boomzilla said:

    How is this friendly UI?

    The "Design Table" form has a bunch of toolbar buttons that pop up fairly complicated dialogs - the relationships dialog is one of those. If you let the UI become invalid, then you get a situation where the problem could be in one of the many dialogs and the user has to go search and find the problem. So, the code to handle the save button becomes complex and the user has a hunting expedition to go on to deal with the issue.

    On the other hand, if every action is performed in such a way that it is locally validated, then errors are shown to the user immediately. For most fields this is simple, but for the "New Relationship" button, you have to either add a known valid relationship or pop up enough dialogs to know what relationship the user wants to create.

    This is consistent with the rest of the form's UI. For example, you add a new column by typing a name in the name column, and it automatically sets it up as a nullable nvarchar(10) column. You are free to change the type, but something valid is filled for you.


  • BINNED

    @Jaime said:

    This is consistent with the rest of the form's UI.

    So you're justifying it by saying that the rest of the UI is just as bad.


  • FoxDev

    @boomzilla said:

    Must have gotten eaten in the outage

    When the snapshot taking had finished, the server automatically put the forum back up just long enough for about three posts to be made, before it was taken down again for the delete/recreate ;)


  • ♿ (Parody)

    Yeah, that's what I concluded. I'm also re-reading a bunch of things this morning. 😩



  • @antiquarian said:

    So you're justifying it by saying that the rest of the UI is just as bad.

    No. Consistency is one of many factors that make a good UI. The error handling behavior of the "Design Table" form is better than the obvious alternatives.


Log in to reply