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

    Restrict values in column

    Hi all,

    Please could you check the below case and advise what would be the best way to proceed.

    I have a table with more than 10K records in it. I want to restrict any data insert into the table if the value of column 1 is 'xyz'. The table currently has multiple rows with column 1 as 'xyz' and these rows should remain in the table.

    May be this can be achieved by triggers, but I am not sure if it would work as there are already rows with column 1 as 'xyz'.

    Thank you for your time.

    Arun

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If I understand your requirements correctly, this would be better handled with a constraint than a trigger. But first, clean up your existing data.

    I confess I don't understand the purpose of a column that is restricted to a constant value....
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    In other words you want the existing rows that contain your 'xyz' constant value to be left "as is" and you want the user to be able to UPDATE or DELETE those rows at will, but you don't want any more rows to be added with 'xyz' in that column.

    If I understand the problem correctly, I don't think that a constraint can do what you want. Depending on which database engine you are using, a trigger may be able to provide that functionality.

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

  4. #4
    Join Date
    Sep 2011
    Posts
    9
    thanks for the comments.

    Sorry...may be I did not explain the case clearly.

    I am having a table with CustomerID and their PhoneNumbers. I want to limit any new phone numbers being added to the customer.

    So customer xyz might be having 5 phone numbers right now. I now need to restrict any new phone numbers being added to this customer. I need to keep the details of the 5 phone numbers already assigned to this customer, so I cannot delete those entries.

    I need to restrict this data insert for specific customers, so a blanket ban on insert will not work.

    Thank you

    Arun

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Which DBMS are you using?

  6. #6
    Join Date
    Sep 2011
    Posts
    9
    Microsoft SQL Server 2008.

    Thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    So some of the customers will be limited to five phone numbers, some will be limited to more or less than five, and others will have no limit???

    Please post the DDL to build the table or tables involved, and the sample data for at least five customers. There are too many potential twists for me to even guess at a solution yet, and the schema snd sample data should give all of us a better handle on what you need.

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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    There are too many potential twists for me to even guess at a solution yet...
    that was my feeling too, right from post #1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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