Post reply

Cannot create primary key Serial to table have existing data .

  • January 27, 2018 at 6:56 am

    #343435

    Problem

    Cannot create primary key Serial to table have existing data .

    Table structure

    USE [Malahy]GO/****** Object: Table [dbo].[SCFRentContainer]  Script Date: 2018/01/27 3:12:54 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[SCFRentContainer]([BranchCode] [int] NOT NULL,[TrxDate] [datetime] NOT NULL,[LineNumber] [int] NOT NULL,[Price] [decimal](18, 2) NULL,[CustomerName] [nvarchar](500) NULL,[CustomerPhone] [nvarchar](500) NULL,[Notes] [nvarchar](500) NULL,[Back] [bit] NULL,[Payed] [bit] NULL,[SubLdgCode] [int] NULL,[ContainerNumber] [nvarchar](500) NULL,[Site] [nvarchar](500) NULL,[TrxDateRent] [date] NULL,[TaxSet] [float] NULL,[TaxValue] [float] NULL, CONSTRAINT [PK_SCFRentContainer] PRIMARY KEY CLUSTERED ([BranchCode] ASC,[TrxDate] ASC,[LineNumber] 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

    Actually i need to add new field Name Serial to be primary key with three keys found before 

    meaning after add  Serial field primary keys will be 4 keys (

    BranchCode,TrxDate,LineNumber,Serial

    ALTER TABLE dbo.SCFRentContainer ADD

    Serial int NOT NULLALTER TABLE dbo.SCFRentContainerDROP CONSTRAINT PK_SCFRentContainerALTER TABLE dbo.SCFRentContainer ADD CONSTRAINTPK_SCFRentContainer PRIMARY KEY CLUSTERED (BranchCode,TrxDate,LineNumber,Serial) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]ALTER TABLE dbo.SCFRentContainer SET (LOCK_ESCALATION = TABLE)

    I got error after applying script above

    Msg 4901, Level 16, State 1, Line 1
    ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Serial' cannot be added to non-empty table 'SCFRentContainer' because it does not satisfy these conditions.

  • January 28, 2018 at 1:39 am

    #1977633

    When you add a new column to a table with existing records, as you're trying to do here, SQL will use a default value of "NULL" for the column value for the existing records. Because you've told SQL you want this column to be non-nullable, it can't set the values of the those existing records to NULL; so you need to tell it what value to use. One way to do this is by adding a DEFAULT constraint.

    Here's an example modification to your second query, where I've set the value of the new column for existing rows to 0.
    ALTER TABLE dbo.SCFRentContainer ADD Serial INT NOT NULL DEFAULT(0)

    ALTER TABLE dbo.SCFRentContainer
    DROP CONSTRAINT PK_SCFRentContainer

    ALTER TABLE dbo.SCFRentContainer ADD CONSTRAINT PK_SCFRentContainer PRIMARY KEY CLUSTERED (BranchCode,TrxDate,LineNumber,Serial) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ALTER TABLE dbo.SCFRentContainer SET (LOCK_ESCALATION = TABLE)

  • February 12, 2018 at 3:04 am

    #1979395

    In order to add primary key select that field and click on primary key. field_name datatype() primary key;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply