Results 1 to 2 of 2

Thread: TRUNCATE Table

  1. #1
    Join Date
    Feb 2004
    Bangalore, India

    Unanswered: TRUNCATE Table


    (From Bol)
    "You Cannot Use Truncate Table On A Table Referenced By A Foreign Key

    1) States
    Statecode Tinyint Primary Key
    Statename Varchar(25)

    2) Rivers
    Rivercode Smallint Primary Key
    Rivername Varchar(40)

    3) Riverinstates
    Statecode Tinyint,
    Rivercode Smallint,
    Constraint Pkriverinstates Primary Key(Statecode,Rivercode),
    Constraint Fkriverinstates
    Foreign Key (Statecode) References States(Statecode),
    Foreign Key (Rivercode) References Rivers(Rivercode)

    after removing all rows from RiverInStates Table using TRUNCATE and the command completed successfully.

    when i tried to remove all rows from Rivers table , i got the following error.

    "Server: Msg 4712, Level 16, State 1, Line 76
    Cannot truncate table 'rivers' because it is being referenced by a FOREIGN
    KEY constraint."

    i thought the error may be due to the "Fkriverinstates" FOREIGN KEY CONSTRAINT. so i DROPped the constraint with
    ALTER TABLE RiverinStates DROP CONSTRAINT Fkriverinstates

    even after DROPping the constraint, im getting the same error
    can any one point out where the problem is?

    thanks in advance


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you have (or had) two foreign key constraints in Riverinstates -- one named Fkriverinstates and the other unnamed -- and you need to drop the unnamed one as well | @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