While craeting 2nd table with foreign key getting an error?

Question

CREATE TABLE [dbo].[tbl_AddItems]( [id] [int] IDENTITY(11) NOT NULL [ProductId] AS ('SHPLT'+right('0000'+CONVERT([varchar](10)[ID](0))(4)))PERSISTED NOT NULL CONSTRAINT PK_ProductId PRIMARY KEY CLUSTERED(ProductId) [ProductCategory] [varchar](20) NULL [ProductName] [varchar](30) NULL [Price] [decimal](6 2) NULL [ProductCode] [varchar](20) NULL [Descriptions] [varchar](max) NULL [Stock] [int] NULL [Discount] [int] NULL ) CREATE TABLE [dbo].[tbl_AddImages] ( [Id] INT IDENTITY(11) NOT NULL [ProductId] AS ('SHPLT'+right('0000'+CONVERT([varchar](10)[ID](0))(4)))PERSISTED NOT NULL CONSTRAINT [FK_ProductId] FOREIGN KEY([ProductId]) REFERENCES [tbl_AddItems] ([ProductId]) [ImgId] [varchar](20) NULL [ImgPath] [varchar](100) NULL ) here getting an error while creating tbl_AddItems  CREATE TABLE failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.  Can anyone help me?

Khan Abrar Ahmed · Answer

Hi try the below scripts --================= SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_AddItems]( [id] [int] IDENTITY(11) NOT NULL [ProductId] AS ('SHPLT'+right('0000'+CONVERT([varchar](10)[ID](0))(4))) PERSISTED NOT NULL [ProductCategory] [varchar](20) NULL [ProductName] [varchar](30) NULL [Price] [decimal](6 2) NULL [ProductCode] [varchar](20) NULL [Descriptions] [varchar](max) NULL [Stock] [int] NULL [Discount] [int] NULL CONSTRAINT [PK_ProductId] PRIMARY KEY CLUSTERED ( [ProductId] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_AddImages]( [Id] [int] IDENTITY(11) NOT NULL [ProductId] AS ('SHPLT'+right('0000'+CONVERT([varchar](10)[ID](0))(4))) PERSISTED NOT NULL [ImgId] [varchar](20) NULL [ImgPath] [varchar](100) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_AddImages] WITH CHECK ADD CONSTRAINT [FK_ProductId] FOREIGN KEY([ProductId]) REFERENCES [dbo].[tbl_AddItems] ([ProductId]) GO ALTER TABLE [dbo].[tbl_AddImages] CHECK CONSTRAINT [FK_ProductId] GO

Manas Mohapatra · Answer

Execute below script: SELECT is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on FROM sys.databases; Check 1st column(ANSI_NULLS) value if it is OFF then make it OFF by using below queries. SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT ON; SET QUOTED_IDENTIFIER ON; After executing above code please try to create table.