Where is the incorrect brackets

asked 2020-08-27T07:03:21.257+00:00
Simon Evans 81 Reputation points

Apparently my bracketing is incorrect, new to adding constraints

CREATE TABLE [dbo].[TestCasesIdentifier2](
    [TestCaseIdentifierId] [int] IDENTITY(1,1) NOT NULL,
    [TestCaseId] [int] NOT NULL,
    [TestCaseSequence] [int] NOT NULL,
    [FieldName] [varchar](100) NOT NULL,
    [FieldValue] [varchar](100) NOT NULL,
    [AlphaNumeric] [bit] NOT NULL,
    [FieldValueIsDateTime] [bit] NOT NULL, 
    CONSTRAINT [PK_TestCasesIdentifier] PRIMARY KEY CLUSTERED 

(
    [TestCaseIdentifierId] ASC
)   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

        ON [PRIMARY],


 CONSTRAINT CHK_FieldValue_IsDate CHECK
  (
       (
            [FieldValueIsDateTime] = 1
             AND LEN([FieldValue]) = 19
             AND SUBSTRING([FieldValue],11,1) = 'T'
             AND TRY_CAST([FieldValue] AS datetime2) IS NOT NULL
       )

            OR [FieldValueIsDateTime] = 0
  )

) ON [PRIMARY]
GO


ALTER TABLE [dbo].[TestCasesIdentifier2]  ADD CONSTRAINT fk_TestCasesIdentifier_TestCases FOREIGN KEY([TestCaseId])
REFERENCES [dbo].[TestCases] ([TestCaseId])
GO

ALTER TABLE [dbo].[TestCasesIdentifier2] ADD  DEFAULT ((0)) FOR [FieldValueIsDateTime]
GO
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2020-08-27T08:24:40.49+00:00
    EchoLiu-MSFT 14,416 Reputation points

    Hi @Simon Evans

    I tested your statement and the error is:Foreign key 'fk_TestCasesIdentifier_TestCases' references invalid.The table where the foreign key is located should be created before the table. So, please try to create the table where the foreign key is located, and then create the table.

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Best Regards
    Echo

    No comments