Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Duplicate Checking Across Tables

    T1 <-- T2 <-- T3
    Is it possible to define a unique constraint involving one column from T3 and one from T1?

    e.g. Customer has multiple Application(s). Application has multiple Ticket(s). The TicketNumber should be unique for every Customer.

    Thanks & Regards.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what are the primary keys for those tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    In the example,
    Customer - CustomerId (Natural Key)
    Application - Surrogate Key
    Ticket - Surrogate Key

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joatmon
    Ticket - Surrogate Key
    presumably you're using an autoincrement or summat similar?

    then it will be unique for every customer because it's unique, full stop

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    Sorry, I will attempt to make it a little more clear.

    TicketId is unique because it is autoincrement. However there is an additional attribute TicketNumber which is what I need to make unique within a customer. That is, I need to allow same ticket number across customers (C1-T1 and C2-T1), but need to ensure uniqueness within a customer (C1-T1 is unique). In summary, the CustomerId+TicketNumber combination should be unique. (Currently this is enforced by the application code and I am wondering whether any of the RDBMS support defining this as a DB constraint).

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Only a few DBMS engines allow multi-column constraints. All of the DBMS engines that I can think of allow UNIQUE INDEX creation, which would do what you want.

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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    ...which would do what you want.
    only if CustomerID is redundantly carried in the Ticket table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by r937
    only if CustomerID is redundantly carried in the Ticket table
    Although there is an applications table, they are probably referring to a join table (customers can have multiple applications) in which the customer id should be carried over to the ticket table unless he has a design problem where applications is not normalized.

Posting Permissions

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