Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    9

    Unanswered: No further entries of a value in table

    Hi all,

    I have a table which stores phone numbers of a customer in a table.

    Say this table is as below

    CustomerName - PhoneNumber
    Customer 1 - Phone number 1
    Customer 2 - Phone number 2
    Customer 2 - Phone number 3
    Customer 3 - Phone number 4

    What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.

    Any idea in the right direction will be much appreciated.

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ALTER TABLE phonenumbers
    ADD CONSTRAINT ihatecustomer2
      ( CHECK 2 <= ( SELECT COUNT(*) FROM phonenumbers WHERE custno = 2 ) )
    not tested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why? Did Cust #2 do something bad?

    This makes no sense
    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.

  4. #4
    Join Date
    Sep 2011
    Posts
    9

    Working

    Thanks r937 the idea was just perfect.

    I could not run your script but I could implement your idea successfully.

    create function retCustPHCount()
    returns int
    ...
    select @retval = count(*) from phonenumbers where customer = 'Customer2'
    return @retval
    ...

    alter table custphonenumber
    add constraint ihatecustomer2
    check (dbo.retCustPHCount <= 2);
    Last edited by jack_sparrow; 03-30-12 at 11:18.

  5. #5
    Join Date
    Sep 2011
    Posts
    9

    Dirty workaround

    Brett this is a dirty workaround to stop users from adding charges that they cannot map to an internal account. Now they will have to put in the effort to correctly allocate charges to the right customer.

Posting Permissions

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