Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: Problem in foreign key with on delete no action

    Hi friends i am facing problem with using foreign key with on delete no action.
    -I created two tables one for department and another for employees
    - created a foreign key refrences departments

    when i try to delete a departement from departments table then it gives a error

    Msg 547, Level 16, State 0, Line 1
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "zz". The conflict occurred in database "study", table "dbo.tbdep", column 'depdcod'.

    Plz check my code where i am wrong
    ----------------------------------------------------------------
    create table tbdep (depdcod int primary key not null, depname varchar(50))
    insert tbdep values(10,'sales')
    insert tbdep values(20,'MARKETIN')
    insert tbdep values(30,'acounts')


    select * from tbdep


    create table tbemp (ename varchar(50),depcod int)

    insert tbemp values('kdjfkjd',10)
    insert tbemp values('SD',20)
    insert tbemp values('FD',20)
    insert tbemp values('GGF',10)
    insert tbemp values('FGFG',30)


    alter table tbemp
    add constraint zz foreign key(depcod) references tbdep(depdcod) on delete no action
    delete from tbdep where depdcod=10

    select * from tbdep
    select * from tbemp
    -------------------------------------------------------------------

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    before i answer your question, could you take a second and tell us in your own words what "no action" means

    what did you expect would happen when you tried to delete department 10 which still has employees in it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    2
    no action
    :-No action means if i delete primary key from department table then records relating to that departmen are not affected

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Really?

    NO ACTION:
    The Database Engine raises an error and the delete action on the row in the parent table is rolled back.
    http://msdn.microsoft.com/en-us/library/ms174979.aspx

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    NO ACTION does not mean that key violations are permitted.

    NO ACTION is supposed to indicate whether triggers fire when a row is deleted. In SQL Server NO ACTION means the same as RESTRICT (the default).

    If you don't want to enforce the foreign key then you might as well drop the constraint altogether. What purpose would the foreign key serve if you could delete referenced rows without deleting the dependent rows?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    If you don't want to enforce the foreign key then you might as well drop the constraint altogether. What purpose would the foreign key serve if you could delete referenced rows without deleting the dependent rows?
    here's a hypothetical example

    suppose you have a store which sells items

    some of these items are brought in from vendors, some are made in the back room by our staff (e.g. custom floral arrangements)

    the vendor_id foreign key in the items table is optional, i.e. allows NULL

    so far so good (and don't mess up the example by saying that the FK should be split off into its own table, please )

    when the vendor_id for a specific item is NOT NULL, then we send a percentage of the sale price to the vendor

    now imagine a vendor goes out of business, but we have considerable stock on hand

    we no longer need to send the vendor a percentage, so we can set the vendor_id on those items to NULL

    thus, ON DELETE SET NULL

    in other words, delete referenced rows without deleting the dependent rows

    quod erat demonstrandum

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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    C'est magnifique!
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Rudy,
    Your example doesn't explain why you would want a vendor_id for floral arrangements that aren't supplied by a valid vendor.

    ON DELETE SET NULL effectively "deletes" the dependent rows by changing them to something else and thereby removing the referencing row. A null foreign key does indeed allow the constraint to be violated though. In my view that seriously degrades the usability and effectiveness of the database. Not least because it forces developers to check every constraint to see whether it is the type of key that can be violated or the type that can't.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    Rudy,
    Your example doesn't explain why you would want a vendor_id for floral arrangements that aren't supplied by a valid vendor.
    well, that's just the point -- you wouldn't, because some items aren't supplied by a vendor, so those would be the ones with vendor_id NULL

    maybe you missed the example -- non-vendor items made up in the back room by our staff

    in life, dportas, there are two kinds of people, those who think of stuff and run them up the flagpole, and those who sit back all day and shoot them down

    i know which kind i am

    confucious say: "man who say it cannot be done should not interfere with man who is doing it"

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

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    [QUOTE=r937]well, that's just the point -- you wouldn't, because some items aren't supplied by a vendor, so those would be the ones with vendor_id NULL

    maybe you missed the example -- non-vendor items made up in the back room by our staff

    I didn't miss it. If those items don't have a vendor then there is no need for them to have a vendor_id. The idea that they supposedly need a null vendor_id is something you didn't explain - but if you don't explain it then I don't see what the point of your example is.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my example had vendor_id in the items table, so, yeah, it would need to be NULL for those non-vendor items

    i expect you're going to say that the vendor_id FK should be split off into a separate table, such that there wouldn't need to be any NULLs, because items that don't have a vendor wouldn't have a row in that table

    that would work too, eh
    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
  •