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.