Cannot create primary key Serial to table have existing data .

Posted by Ahmedsa under Sql Server on 1/27/2018 | Points: 10 | Views : 394 | Status : [Member] | Replies : 0
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 ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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 NULL
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)

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.




Responses

(No response found.)

Login to post response