Results 1 to 2 of 2

Thread: TRUNCATE Table

  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: TRUNCATE Table

    Hi,

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

    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
    Cheers....

    baburajv

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    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
  •