January 27, 2018 at 6:56 am
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
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
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