Results 1 to 25 of 25

Thread: [RESOLVED] Constraint within primary key - sql server database

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Resolved [RESOLVED] Constraint within primary key - sql server database

    Sorry to bother you guys with what I thought would be an easy question but googling isn't returning a solution to me.
    I have a table with a primary key and one of the other columns in the table is a box number that has to be unique per PK.
    That is, PK = 1 and Box number 1, 2, 3, and PK = 2 and Box number 1, 2, 3 are allowed. PK = 1 and Box number 2, 2 are not allowed.
    Is a constraint I want? Trigger? Index? I would think the simpler the better. Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: Constraint within primary key - sql server database

    Sorry, don't understand the requirement
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,469

    Re: Constraint within primary key - sql server database

    Do you mean like this

    PK Box
    1 1
    2 2
    1 2
    1 3

    So PK1 Box 2 can not be entered again?

    If so the a UNIQUE INDEX on PK and Box would support that
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    @GaryMazzone, thanks I will try that!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,469

    Re: Constraint within primary key - sql server database

    Looking again I would create the PK as a combination of the currentPK and theBoxnumber. That would make more sense
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    Yes, I was considering that too. Thanks for the update; I have not done anything further with this yet.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,996

    Re: Constraint within primary key - sql server database

    This definitely fits the bill for compound primary key with both those fields.

    If you have an additional "business" requirement for the BOX to be contiguously sequential that could rest in the application layer, or just a SPROC that does a:

    Code:
    Begin Tran
    
    Declare @NextBox int
    Set @NextBox=IsNull((Select Max(Box) From....),0)+1
    
    Insert....
    
    Commit

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    I'm sorry! I don't know what's wrong with me today!

    Columns I have in the table right now are:
    xtblMROrderItems.Control
    xtblMROrderItems.MROrderMasterControl
    xtblMROrderItems.BoxNumber

    What I meant to say is I need to have a unique BoxNumber for each MROrderMasterControl. The latter is a foreign key.
    So now I am thinking, based on what you guys are recommending, is that I don't even need the Control column. The other two can be the compound PK. But I'm not sure yet.
    Yes, @szlmany, the box # should be sequential. I am letting the user created these rows in a grid, and I initialize each new row to the next available box number.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,699

    Re: Constraint within primary key - sql server database

    You want a unique constraint across MROrderMasterCntrol and BoxNumber. To do that you simply create an index with both those columns and tick the Unique checkbox.

    Whether or not you should make that index the primary key is more debatable and you'll have to decide for yourself but here are a few things for you to consider:-
    1. Can either of those fields contain a null? If they can contain a null then they simply cannot be part of a primary key so you'll need to use something else (presumably Control, but you could also set up a completely new primary key column if needed)

    2. Do you feel that MROrderMasterCntrol and BoxNumber naturally describe an individual or is their uniqueness just a by-product/co-incidence of the problem you're trying to resolve. If you're going to go with natural keys then it's important that the primary key you choose should drive the decision. It's hard to verbalise what I mean so I'll provide a simple example. I'm a parts retailer. Every part has a unique part number and a unique description. Because both are unique and can't be null both are candidates to be the primary key. You really should choose part number, though, because it's whole purpose is to identify a part. The description's uniqueness has come from a more woolly business decision and it's purpose is to describe the part to a customer rather than to identify the part. N.b. there's no perfect right or wrong answer to this - it's a judgement call.

    3. There's a raging debate about whether you should use natural primary keys (particularly compound ones) or surrogate keys, even when a natural one is available. Have a google for natural vs surrogate keys for more info but broadly, natural keys have the advantage of being meaningful to developers and dbas, making working on the data easier. On the other hand surrogate keys have the advantage of being smaller (typically some type of int rather than lengthy strings) which means smaller indexes and they also force a clean separation of the domain from the implementation.

    If you decide that you want to use MROrderMasterCntrol and BoxNumber as a primary key then no need to create the unique index described above - simply defining them as the primary key will create that unique index as a by-product.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,469

    Re: Constraint within primary key - sql server database

    I tend to go with surrogate keys personally. I would rather have the key have no meaning on its own and be smaller, it is simpler when being used as a parent in an Foreign Key relationship (at least to me anyway)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: Constraint within primary key - sql server database

    Agree with Gary, but the UseCase for unique combinations in Detail-Tables is a given.

    e.g.:
    Master-Table holds information about a employee
    Detail-Table holds information about phones issued to your employees in a "1:m"-Relation
    In your Detail-Table you have a FK pointing to the PK of employee, meaning you can issue multiple phones to a single employee
    A Field in that Detail-Table is "Phonenumber"

    Now imagine issuing a phone with number "0123-456-789" for new device "iPhone" while that phonenumber is already in use with old device "Android" for the same employee

    If you don't have that constraint, the DBMS wouldn't complain
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,699

    Re: Constraint within primary key - sql server database

    I swing both ways and find it highly situational. If I'm reasonably confident the natural key won't stop being unique in the future and if the number instances of the value appearing (either in the main table or in foreign key references) then I'll favour the natural key. The maintainability outweighs the extra hardware costs. If I don't have that confidence or the values are going to appear a lot then I swing towards a surrogate.

    Incidentally, I really like compound keys, whether they're natural or surrogate. They make querying much easier and can be more efficient. E.g. in a Customer > Order > Order Line hierarchy, if the Order Line's PK is CustomerNo, OrderNo, OrderLineNo I can look up the customer for an order line without needing to include the Order table in the query. That's a poor example but you get the idea.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,996

    Re: Constraint within primary key - sql server database

    In general,I dislike surrogate keys.

    And I really like the use of CLUSTERED COMPOUND in this case, if these are "lines" of the "order" (which I'm guessing this is basically the business model we are mocking, no pun intended)...

    With CLUSTERED all "rows" are physically on disk together. This increases the likelihood of only needing "one" I/O operation on the server to return all rows for an order. I've exploited this technique on many large enterprise systems, causing me to need sometimes 5 or 6 components for a compound primary key.
    Last edited by szlamany; Mar 26th, 2021 at 07:23 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,699

    Re: Constraint within primary key - sql server database

    CLUSTERED COMPOUND
    Agreed with one small caveat, I don't like clustering if inserts are likely to go in the middle of an index rather than add to the end because... page splits. But yeah, orders and order lines are usually a perfect for clustered compound keys because inserts almost always go exclusively to the end and it naturally groups an order together on disk.


    edit>I'm going to add a small (and pedantic) correction to the above. Clustering doesn't directly ensure the order of records on disk, it ensures their order on the B Tree. Always a good one to get right in interviews .
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,341

    Re: Constraint within primary key - sql server database

    Like Funky I fall into the sometimes camp. Depends on how much control I have. If I can, I'll go with as much natural keys as I can. Sometimes it just isn't possible. Addresses are among the worst. Especially if you have to also maintain a history of addresses. When it comes to people and addresses, I generally will go with a surrogate just to make things easier. For everything else, I'll try to see if there's a natural key.

    @Zivoni ... I'm guessing that your use-case there was for using a surrogate for the phone table?
    I can do that with a natural key:
    EmpId, PhnNum, DateIssued The type then becomes incidental and isn't part of the key.
    1,123-456-7890,2018-01-01 (Android)
    1,123-456-7890,2020-06-01 (iPhone)
    Then the Employee gets a new phone with a new number:
    1,123-456-0987,2020-09-01 (iPhone)
    Another employee is hired and given the original phone of the first employee:
    2,123-456-7890,2020-10-01 (iPhone)

    You create an index on EmpId, and index on PhoneNumber (maybe, if you do any lookups on it, if not, then don't), and then create a PKey on EmpId, PhNum, and DateIssued.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,996

    Re: Constraint within primary key - sql server database

    @fd

    Fill factors will get you better performance if page splits occur...

    https://www.brentozar.com/archive/20...ut-fillfactor/

    "In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty. The empty space will be present on the page as itís written on disk, and itíll take up the same amount of space as it sits in memory."

    And yes - understanding where in the index world things live helps on interviews I guess. But your point about trees - "leaf" levels - makes an earlier argument for "smaller" index keys "pointless", imo (index puns, lol - makes a DB nerd smile!).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    So this was a worthwhile conversation for me simply because (please don't roll your eyes at me), I never even considered surrogate vs natural keys unless it screamed out at me that it should be a natural key, like EmpID in our Emps table. After reading your posts I read more here which of course none of you needs to read because you're already smart but the cool thing about their example (for me) is that of the six cities posted in this article's screenshot, one of them is where I grew up! (I know, who cares? LOL). But considering it's a small town in a small state, I thought that was neat!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  18. #18
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,996

    Re: Constraint within primary key - sql server database

    Quote Originally Posted by MMock View Post
    but the cool thing about their example (for me) is that of the six cities posted in this article's screenshot, one of them is where I grew up! (I know, who cares? LOL). But considering it's a small town in a small state, I thought that was neat!
    Interesting for me as well, as that city in my matching home state of CT was a past client of mine as well! If you all went to HS in the 1990's I might have your data on some old PC somewhere in my garage!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  19. #19

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    Quote Originally Posted by szlamany View Post
    If you all went to HS in the 1990's I might have your data on some old PC somewhere in my garage!
    Off by a decade, and a bit more! Graduated in 1980 (and while I did attend public schools in South Windsor up through 9th grade, I went to H.S. at East Catholic in Manchester).
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  20. #20
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,996

    Re: Constraint within primary key - sql server database

    1980 - that was my first year coding - PDP-11 mini computers. We had a dozen schools in CT - East Windsor was one of them (I can't remember the IT directors name - yikes!). Driving my 1969 Chevelle SS all over the state writing "report card" print and "rank in class" calculations.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #21

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    Love those old muscle cars! Must've been nice.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  22. #22

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: Constraint within primary key - sql server database

    My table's natural compound key is beautiful!
    My code is working!
    I am releasing my applicatin and maybe it will get tested (we test things live around here) this afternoon if we get a shipment of boxes in.
    Thanks for the thorough discussion; love you guys!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  23. #23
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,699

    Re: [RESOLVED] Constraint within primary key - sql server database

    I read more here which of course none of you needs to read because you're already smart
    That article provided a really good breakdown of pros and cons that should be valuable to any DB professional, no matter how smart or experienced.

    index puns
    A query walks into a bar, sees two tables and asks, "do you mind if I join you?"
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  24. #24

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,237

    Re: [RESOLVED] Constraint within primary key - sql server database

    @fd, you have made my day. That is hilarious!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  25. #25
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: [RESOLVED] Constraint within primary key - sql server database

    Quote Originally Posted by funkydexter View Post
    a query walks into a bar, sees two tables and asks, "do you mind if i join you?"
    bruahahahahahahahah
    rotfl
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

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



Click Here to Expand Forum to Full Width