PostgreSQL serial column

4 posts, 1 answers
  1. Michael
    Michael avatar
    29 posts
    Member since:
    Nov 2010

    Posted 23 Nov 2010 Link to this post

    Hi,

    I port MSSQL "Music Store" database to PostgreSQL database for testing MVC2. Now tables have primary keys and is type of SERIAL. Now when I add new record, exception is raised for duplicate key value! In reality, it is added to a record earlier in AlbumId field, value of 0. Now adding a new record also trying to enter the 0! How can I get OA ORM to do a sequence of key columns?

    Here is code for new album :
    //
            // GET: /StoreManager/Create
      
            public ActionResult Create()
            {
                var viewModel = new StoreManagerViewModel
                {
                    Album = new Album(),
                    Genres = storeDB.Genres.OrderBy(b => b.Name).ToList(),
                    Artists = storeDB.Artists.OrderBy(c => c.Name).ToList()
                };
                  
                return View(viewModel);
            }

    And here is for adding :
    //
            // POST: /StoreManager/Create
      
            [HttpPost]
            public ActionResult Create(Album album)
            {
                if (ModelState.IsValid)
                {
                    // Save Album
                    storeDB.Add(album);
                    storeDB.SaveChanges();
      
                    return RedirectToAction("Index");
                }
                else
                {
                    // Invalid - Redisplay with errors
                    var viewModel = new StoreManagerViewModel
                    {
                        Album = album,
                        Genres = storeDB.Genres.OrderBy(b => b.Name).ToList(),
                        Artists = storeDB.Artists.OrderBy(c => c.Name).ToList()
                    };
      
                    return View(viewModel);
                }
            }

    Here is table definition :
    CREATE TABLE "public"."Album" (
      "AlbumId" SERIAL, 
      "GenreId" INTEGER NOT NULL
      "ArtistId" INTEGER NOT NULL
      "Title" VARCHAR(160), 
      "Price" NUMERIC(10,2) NOT NULL
      "AlbumArtUrl" VARCHAR(1024) DEFAULT '/Content/Images/placeholder.gif'::character varying
      CONSTRAINT "PK__Album__97B4BE370AD2A005" PRIMARY KEY("AlbumId"), 
      CONSTRAINT "FK_Album_Genre" FOREIGN KEY ("GenreId")
        REFERENCES "public"."Genre"("GenreId")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        NOT DEFERRABLE, 
      CONSTRAINT "FK__Album__ArtistId__276EDEB3" FOREIGN KEY ("ArtistId")
        REFERENCES "public"."Artist"("ArtistId")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        NOT DEFERRABLE
    ) WITH OIDS;
      
    CREATE INDEX "ArtistId_idx_Album" ON "public"."Album"
      USING btree ("ArtistId");
      
    CREATE INDEX "GenreId_idx_Album" ON "public"."Album"
      USING btree ("GenreId");


    Thanks, Michael.
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 25 Nov 2010 Link to this post

    Hello Michael,

    I successfully reproduced this error on my side.
    It seems that PostgreSql cannot find the correct sequence for the Album table because its name is not lowercase. You could see under the Sequences node that this sequence is named “Album_AlbumId_seq”. In order to avoid the error you should set the sequence’s name to lowercase("album_albumid_seq") and update your domain model using Update from database dialog.
    I think that will help you.

    Sincerely yours,
    Damyan Bogoev
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
  4. Michael
    Michael avatar
    29 posts
    Member since:
    Nov 2010

    Posted 25 Nov 2010 Link to this post

    Hi  Damyan,

    Sequence name is automatic created by Database, if ORM can't work with mixed case I will change sequence name. Finaly i saw that exception about sequence name is not found ... But to get to that step, I had to manually in the generated model to explore and sets the IDENTITY column, because model mapping wizard is not have that made. It's a real problem. So I got the first entry in the column to 0 (zero) and when the new row is added again, exception is raised because ORM try to insert 0 (zero) again. He actualy did not understand that is sequence, than ordinary INTEGER field.

    Michael.
  5. Answer
    Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 25 Nov 2010 Link to this post

    Hello Michael,

    We found the cause for this problem and have fixed it. Currently we are in a testing cycle of the service pack and I hope that we will be able to release it by the end of the week.
    I am sorry for the inconvenience caused.

    All the best,
    Damyan Bogoev
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top