Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Cannot insert the value NULL into column 'City'

    Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls. INSERT fails.

    <code>
    create table country(country_code nvarchar(2), country_name nvarchar(255))
    create table states(country_code nvarchar(2),state_code nvarchar(20),state_name nvarchar(255))
    create table weblocations (country_code nvarchar(2), state_code nvarchar(20),city_name nvarchar(255),timezoneid varchar(255))


    INSERT INTO Location
    ([Country],[City], [State] )

    SELECT
    country.country_name AS [Country],
    weblocations.city_name AS [City],
    states.state_name AS [State]
    FROM
    country
    LEFT JOIN
    weblocations ON country.country_code = weblocations.country_code
    LEFT JOIN
    states ON weblocations.state_code = states.state_code;



    CREATE TABLE [dbo].[Location](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Country] [nvarchar](max) NOT NULL,
    [City] [nvarchar](max) NOT NULL,
    [State] [nvarchar](max) NOT NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    </code>
    What should I do?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Either stop inserting NULL values into the city column, or change the definition to permit NULL values.

    Without more background on your problem, I don't know which of those two answers is appropriate.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls. INSERT fails. <<

    Almost everything you have here is wrong. It violates ISO-11179 and uses absurd defaults. Look at what you are doing!

    CREATE TABLE Country
    (country_code VARCHAR(2),
    country_name VARCHAR(255));

    Do you really have only one country? That is what you said! Why is the ISO country code in varying length and in Unicode? What country on Earth has a name that is 255 Chinese characters long? Where is the key? A table has to have a key by definition. All your columns are NULL-able, more NULLsin this one posting than entire databases! You created a garbage pile that will fill with trash data.

    CREATE TABLE Countries
    (country_code CHAR(3) NOT NULL PRIMARY KEY,
    -- ISO 3166-1 alpha-3 - Wikipedia, the free encyclopedia
    country_name VARCHAR(25) NOT NULL);

    Now let's do the rest of your schema. The term “states” is used in the USA only; the ISO term for political units within a country is “province”. Did you consult the Postal Union about the lengths of these data elements?

    Please post an example of a time_zone_id that is longer than the Heart Sutra in Chinese. Time zones are shown as a displacement from UTC. The format is “+|-hh:mm”; you might want to read any book on SQL and pay attention to the WITH TIMEZONE option.

    You have no DRI, so these non-tables might as well be 1950's mag tapes.

    CREATE TABLE Provinces
    (country_code CHAR(3) NOT NULL
    REFERENCES Countries(country_code)
    ON UPDATE CASCADE,
    state_code VARCHAR(5) NOT NULL,
    PRIMARY KEY (country_code, state_code),
    province_name VARCHAR(15) NOT NULL);

    Your web location is table is wrong. A time zone is an attribute of a city, not a whole country or province. You left out the city_name in the web locations table!

    CREATE TABLE Web_Locations
    (country_code CHAR(2) NOT NULL,
    province_code VARCHAR(5) NOT NULL,
    FOREIGN KEY (country_code, province_code)
    REFERENCES States (country_code, province_code)
    IN UPDATE CASCADE,
    city_name VARCHAR(35) DEFAULT 'Unknown' NOT NULL, -- use a default!
    PRIMARY KEY (country_code, province_code, city_name),
    time_zone CHAR(255) NOT NULL
    CHECK (time_zone LIKE '[+-][0-2][0-9]:[0-5][0-9]'));

    Perhaps the scary thing in all of this was your insertion attempt. Read ISO-11179 and any book on RDBMS. We do not use IDENTITY in RDBMS. It cannot be key by definition. The correct way to name a data element is <attribute>_<attribute property>. You had the names right and made them wrong!

    Your problem is that you do not know the basics of data, or RDBMS. You also do not do basic research.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Take it down a notch, Joe.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •