when I wright the statement in SQL 2005 error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ahamed Zainy
    New Member
    • Dec 2010
    • 4

    when I wright the statement in SQL 2005 error

    Msg 547, Level 16, State 0, Line 2
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customer_Co untry". The conflict occurred in database "Customor", table "dbo.Countr y", column 'Country_ID'.

    """"""""""""""" """"""""""""""" """"""""""""""" """""""""""
    Need Help Pls
  • gpl
    New Member
    • Jul 2007
    • 152

    #2
    It looks like you tried to enter a customer with an invalid country code

    It would help if you posted your database structure and the insert statement that you used

    Comment

    • Ahamed Zainy
      New Member
      • Dec 2010
      • 4

      #3
      Tnx for your reply Sir
      Here is my table contains
      Customer Table
      ----------------------
      Customer_Id int Identity notnull (PrimaryKey)
      fname varchar(30) notnull
      lname varchar(40) notnull
      Address varchar(100) notnull
      City varchar(30) notnull
      State varchar(20) notnull
      Country tinyint Notnull {Relationship withCountry_cod e}
      Postal varbinary(50) Null
      Mugshot image Null
      Acceptemail bit Null
      Balance money Notnull
      monthly_fee money NotNull

      My country Table Contains are

      Country_code tinyint notnull {PrimaryKey}
      Country_name varchar(50) notnull

      Country Code I made a relationship with Country in Customer table.
      PrimaryKey ForignKey
      Country--------Customer
      Country_Code-----Country

      Qoury I used are below

      select * from [Customer]
      insert into Customer
      values(101, 'Migoo', 'Mike', '101 main str', 'wilam', 'Ca', '1889',0,null, 155.00, 25.00)

      when I run the Query now I am getting a error message saying..
      Msg 257, Level 16, State 3, Line 2
      Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.


      when I try to save the tables another error saying

      Unable to create relationship 'FK_Customer_Co untry'.
      The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Customer_Co untry". The conflict occurred in database "Customer", table "dbo.Countr y", column 'Country_code'.

      PLS HELP ME SIR I Am DOING MY SCHOOL ASSIGNMENT END DATE IS ALL MOST NEAR....
      Last edited by Ahamed Zainy; Dec 22 '10, 10:43 AM. Reason: Made a mistake

      Comment

      • gpl
        New Member
        • Jul 2007
        • 152

        #4
        Ahamed
        Homework ? OK, as I said to my children, Ill help, but not do it for you!

        First hint - what values are you inserting into which fields ?
        You should quote them explicitly like this:
        Code:
        insert into Customer (
        Customer_Id , fname  , lname  , Address  , City ,  State , Country  , Postal , Mugshot , Acceptemail , Balance , monthly_fee
        )
        values(101, 'Migoo', 'Mike', '101 main str', 'wilam', 'Ca', '1889', 0, null, 155.00, 25.00)
        your data does not match the fields
        THE primary key is an Identity, which automatically generates the next number, so should not be mentioned in the list
        The country code is a tinyint - what value are you giving ... and how big should it be ?

        I hope these give you an idea how to fix your problem
        Graham

        Comment

        • Ahamed Zainy
          New Member
          • Dec 2010
          • 4

          #5
          Sir Tnx a lot for Taking ure valuable time I got the data into my table but Postal code apiers like '0x0000'I have given a value for it '1889'n I change the datatype to varchar (10),when i save my Customer table I am getting a error message saying {Customer' table
          - Unable to create relationship 'FK_Customer_Co untry'.
          The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Customer_Co untry". The conflict occurred in database "Customer", table "dbo.Countr y", column 'Country_code'.
          }
          pls help to fix this issue....I hope u wont mind helping......
          Tnx
          Regards
          Ahamed

          Comment

          • Rabbit
            Recognized Expert MVP
            • Jan 2007
            • 12517

            #6
            Here are your mistakes that you need to fix:
            1. Like gpl said, you don't specify what fields you're inserting into.
            2. Your postal code is of type VARBINARY but the data isn't binary
            3. Your country code is an integer but '123456' is not an integer, it's a string.

            Comment

            • Ahamed Zainy
              New Member
              • Dec 2010
              • 4

              #7
              tnx for ure reply Mr rabit I came a cross with inserting data nw i am getting a error when i save the customer table {Customer' table
              - Unable to create relationship 'FK_Customer_Co untry'.
              The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Customer_Co untry". The conflict occurred in database "Customer", table "dbo.Countr y", column 'Country_code'.
              }

              Comment

              • Rabbit
                Recognized Expert MVP
                • Jan 2007
                • 12517

                #8
                That could mean a number of things. We would need to see the alter table statement that you used.

                Comment

                Working...