Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: how to check if picklist is used in other teble

    Hi,

    I am new to SQL. I have PicklistGroup table and PicklistDetails table.
    PicklistGroupID & PicklistDetailsID are used as foreign key in other tables.

    I want to write a stored procedure to check if PicklistGroupID & PicklistDetailsID is used in any other table or not. So that i will not allow user to delete it.

    Please help with this stored procedure.
    Thanks
    Amit

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amit_n View Post
    So that i will not allow user to delete it.
    good news: you don't have to do anything, especially not write a stored procedure

    preventing deletion of a row if there is a dependent foreign key is a built-in feature of foreign keys

    all you have to do is declare ON DELETE NO ACTION when defining the foreign key

    vwalah!

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

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You shouldn't check for any dependencies yourself. Let SQL Server do that for you. You must first tell SQL Server what references exist within your database, by defining a foreign key on those columns.

    This is the way how you define a Foreign Key, when the table Mytable has a column with name PicklistDetailsId that references a record in the table PicklistDetails with primary key Id. You will have to do that for all the tables that reference another table.
    Code:
    alter table dbo.MyTable
       add constraint MyTable_PicklistDetails foreign key (PicklistDetailsId)
          references dbo.PicklistDetails (Id)
    In your code you can check for the specific error code that you get back from SQL Server when you try to delete a record from the picklist table that is already referenced to by a record in any other table.
    Last edited by Wim; 03-31-11 at 09:59. Reason: Didn't saw r937 alredy answered it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Mar 2011
    Posts
    2
    Thanks a lot both suggestions will work for me.

    But how will I check this if user changing a status of PICKLISTITEM as a In-Active and my application actually using the same PICKLISTITEM.

    Amit

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your program should check the IsActive column and only show values from active records in the pick list.

    1) The no-longer active choices that were made in the past, can stay that way (unless you are allowed to change the user input).
    2) And for new records, the user will only be able to select active elements from the pick list.
    And both are what you normally want.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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