locked
Unable to insert column RRS feed

  • Question

  • The following table is created:

    CREATE TABLE Credit_Card
    (
    cc_no VARCHAR(18),
    cc_name VARCHAR(20) NOT NULL,
    cc_type VARCHAR(20) NOT NULL,
    cc_ExpDate VARCHAR(7) NOT NULL,
    bank VARCHAR(20) NOT NULL,
    member_id VARCHAR(5)
    CONSTRAINT credit_card_pk PRIMARY KEY (cc_no),
    CONSTRAINT credit_card_fk FOREIGN KEY (member_id) REFERENCES Member
    )

    However, there is error after executing the INSERT statement below:

    INSERT INTO Credit_Card VALUES('1111-0000-1212-3333', 'Master Card', 'Platinum', '04/2017', 'DBS', 'm0001');
    

    Error message:

    Msg 8152, Level 16, State 14, Line 1
    String or binary data would be truncated.
    The statement has been terminated.

    Don't know what went wrong. Would appreciate if there are some advice on this.

    Thank you.

    Wednesday, May 30, 2012 1:33 PM

Answers

  • Hmm...you're right. It's executed on a different DB.

    But after executing in the right one, there is also error:

    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the FOREIGN KEY constraint "credit_card_fk". The conflict occurred in database "INFT3007_Assignment1", table "dbo.Member", column 'member_id'.
    The statement has been terminated.

    That is telling you that you are trying to insert a record with a member_id that does not exist in the Member table.  You can check that with this query

    SELECT * FROM dbo.MEMBER
    WHERE MEMBER_ID = 'm0001'
    
    -----------

    Seems like you are really struggling with the basics here. Might be agood idea to get a learning sql server book and start working with the example code before trying to build a real system.

    One other note - I hope you are planning on doing some sort of encryption on the credit card information.


    Chuck Pedretti | Magenic – North Region | magenic.com




    Wednesday, May 30, 2012 2:40 PM

All replies

  • the lenght of your cc_no is 19 and you defined a varchar(18)
    • Proposed as answer by Chuck Pedretti Wednesday, May 30, 2012 1:37 PM
    Wednesday, May 30, 2012 1:36 PM
  • Your cc_no column is declared to be varchar(18), but you are trying to insert a 19 character literal value 1111-0000-1212-3333.  Change the table declaration to make cc_no varchar(19) not null.

    Tom

    • Proposed as answer by Naomi N Wednesday, May 30, 2012 3:54 PM
    Wednesday, May 30, 2012 1:38 PM
  • Oh...ya. a miscount on the no of characters.

    BTW, I've altered the column as follows:

    ALTER TABLE Credit_Card ALTER COLUMN cc_no VARCHAR(19)
    

    and also the Primary key constraint:

    ALTER TABLE Credit_Card ADD CONSTRAINT credit_card_pk PRIMARY KEY (cc_no)
    

    However, this gave me the following error:

    Msg 8111, Level 16, State 1, Line 1
    Cannot define PRIMARY KEY constraint on nullable column in table 'Credit_Card'.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    Wednesday, May 30, 2012 2:06 PM
  • You need to explicitly make cc_no NOT NULL if you want to use it as a primary key

    ALTER TABLE Credit_Card ALTER COLUMN cc_no VARCHAR(19) NOT NULL


    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, May 30, 2012 2:08 PM
  • Have added NOT NULL but the following error message appeared:

    Msg 8152, Level 16, State 14, Line 1
    String or binary data would be truncated.
    The statement has been terminated.

    Wednesday, May 30, 2012 2:14 PM
  • Script your table back out and post the script.  Also make sure you are using a field list with insert statements in production code.

    INSERT Credit_Card (
    	cc_no
    	,cc_name
    	,cc_type
    	,cc_ExpDate
    	,bank
    	,member_id
    	)
    VALUES (
    	'1111-0000-1212-3333'
    	,'Master Card'
    	,'Platinum'
    	,'04/2017'
    	,'DBS'
    	,'m0001'
    	)
    
    
    ------------


    Chuck Pedretti | Magenic – North Region | magenic.com


    Wednesday, May 30, 2012 2:21 PM
  • Hi, Pedretti

    Sorry. May I know exactly what you mean by "Script your table back out"?

    Wednesday, May 30, 2012 2:26 PM
  • Provide your new table definition so that we can see what actually changed.   Easiest way is to just right click on the table in SSMS and select

    Script table as > Create To > new query window.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, May 30, 2012 2:28 PM
  • Here's the entire script for Credit_Card table:

    USE [INFT3007_Assignment1]
    GO
    
    /****** Object:  Table [dbo].[Credit_Card]    Script Date: 05/30/2012 22:30:37 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Credit_Card](
    	[cc_no] [varchar](19) NOT NULL,
    	[cc_name] [varchar](20) NOT NULL,
    	[cc_type] [varchar](20) NOT NULL,
    	[cc_ExpDate] [varchar](7) NOT NULL,
    	[bank] [varchar](20) NOT NULL,
    	[member_id] [varchar](5) NOT NULL,
     CONSTRAINT [credit_card_pk] PRIMARY KEY CLUSTERED 
    (
    	[cc_no] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[Credit_Card]  WITH NOCHECK ADD  CONSTRAINT [credit_card_fk] FOREIGN KEY([member_id])
    REFERENCES [dbo].[Member] ([member_id])
    GO
    
    ALTER TABLE [dbo].[Credit_Card] NOCHECK CONSTRAINT [credit_card_fk]
    GO

    Wednesday, May 30, 2012 2:30 PM
  • OK - now what insert statement is causing your error?  Because the one you provided above seems to work fine


    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, May 30, 2012 2:33 PM
  • This one:

    INSERT INTO Credit_Card VALUES('1111-0000-1212-3333', 'Master Card', 'Platinum', '04/2017', 'DBS', 'm0001');
    

    Wednesday, May 30, 2012 2:35 PM
  • Odd - works fine for me.  Are you sure you are inserting into your modified table and not a copy of the table in a different database?

    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, May 30, 2012 2:37 PM
  • Hmm...you're right. It's executed on a different DB.

    But after executing in the right one, there is also error:

    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the FOREIGN KEY constraint "credit_card_fk". The conflict occurred in database "INFT3007_Assignment1", table "dbo.Member", column 'member_id'.
    The statement has been terminated.

    Wednesday, May 30, 2012 2:39 PM
  • Hmm...you're right. It's executed on a different DB.

    But after executing in the right one, there is also error:

    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the FOREIGN KEY constraint "credit_card_fk". The conflict occurred in database "INFT3007_Assignment1", table "dbo.Member", column 'member_id'.
    The statement has been terminated.

    That is telling you that you are trying to insert a record with a member_id that does not exist in the Member table.  You can check that with this query

    SELECT * FROM dbo.MEMBER
    WHERE MEMBER_ID = 'm0001'
    
    -----------

    Seems like you are really struggling with the basics here. Might be agood idea to get a learning sql server book and start working with the example code before trying to build a real system.

    One other note - I hope you are planning on doing some sort of encryption on the credit card information.


    Chuck Pedretti | Magenic – North Region | magenic.com




    Wednesday, May 30, 2012 2:40 PM
  • OK. It's working now. Maybe I had updated the member_id in a different DB and that's why there is no effect.

    Thank you.

    Wednesday, May 30, 2012 2:45 PM
  • the column name cc_no has the capacity of storing 18 varchar characters and u r trying to insert 19 characters which is out of its bounds

    there is the problem

    You can solve this issue by altering the table or by droping and re-creating the table or by setting the VARCHAR value to max

    eg: cc_no varchar(max) not null,

    if you are using sql server then the semicolon will not come in the syntax and if you are using mysql then no problem

    this will solve your problem

    Enjoy!!

    Wednesday, August 29, 2012 2:23 PM