CHECK

CHECK-constraint-example.sql

/* how to use the CHECK constraint to enforce the data type and length of the AgentCode column in the Agent table: */

CREATE TABLE [dbo].[Agent](
  [AgentID] [int] IDENTITY(1,1) NOT NULL,
  [AgentCode] [char](8) NULL,
  [Invalid] [bit] NULL,
  [ExtSysAgentGuid] [uniqueidentifier] NULL,
  [BusinessName] [nvarchar](200) NULL,
  ...
  CONSTRAINT [CHECK_AgentCode] CHECK ([AgentCode] IS NULL OR [AgentCode] LIKE '____-____')
) ON [PRIMARY]

/*
The CHECK_AgentCode constraint specifies that the AgentCode column must be either NULL or a 8-character string that matches the pattern ____-____. This pattern ensures that the AgentCode column is always 8 characters long and that it consists of two hyphens separated by four characters.

Here is an explanation of the CHECK_AgentCode constraint:

The CHECK keyword specifies that this is a CHECK constraint.
The AgentCode column is the column that is being constrained.
The CHECK ([AgentCode] IS NULL OR [AgentCode] LIKE '____-____') expression specifies the condition that must be met for the constraint to be satisfied.
*/

ForeignKeys-Null-YetMandatory.sql

CREATE TABLE Agent (
  AgentID int IDENTITY(1,1) NOT NULL,
  AgentCode varchar(8) NULL,
  Invalid bit NULL,
  Suburb nvarchar(50) NULL,
  State nvarchar(50) NULL,
  Postcode char(4) NULL,
  Country nvarchar(50) NULL,
  WebSite nvarchar(256) NULL,
  ABN char(11) NULL,
  PRIMARY KEY (AgentID)
);

CREATE TABLE Applicant (
  ApplicantID int IDENTITY(1,1) NOT NULL,
  Invalid bit NULL,
  Title int NULL,
  GivenName nvarchar(40) NULL,
  FamilyName nvarchar(40) NULL,
  Position nvarchar(50) NULL,
  Email nvarchar(256) NULL,
  PhoneNumber varchar(20) NULL,
  MobileNumber varchar(20) NULL,
  Suburb nvarchar(50) NULL,
  State nvarchar(50) NULL,
  Postcode char(4) NULL,
  Country nvarchar(50) NULL,
  IsWebsitePresent bit NULL,
  WebSite nvarchar(256) NULL,
  IsApplicantPrimary bit NULL,
  PRIMARY KEY (ApplicantID)
);

CREATE TABLE Application (
  ApplicationID uniqueidentifier NOT NULL,
  ApplicationIncrement int NULL,
  CreatedDateTime datetime NULL,
  UpdatedDateTime datetime NULL,
  LastModifiedByEmail nvarchar(256) NULL,
  DateApplicationSubmitted datetime NULL,
  ApplicantID int NULL,
  AgentID int NULL,
  ApplicationStatus varchar(50) NULL,
  IsActive bit NOT NULL,
  PRIMARY KEY (ApplicationID),
  FOREIGN KEY (ApplicantID) REFERENCES Applicant (ApplicantID),
  FOREIGN KEY (AgentID) REFERENCES Agent (AgentID)
);

/*
An Application can only be created by an Applicant or an Agent, BUT NOT BOTH; write the necessary constraint such that the Application record enforces either of  ApplicantID or   AgentID 

==>
The necessary constraint to enforce that an Application record can only be created by an Applicant or an Agent, but not both, is as follows:
*/
CONSTRAINT [CK_Application_ApplicantOrAgent] CHECK (
  ApplicantID IS NOT NULL OR AgentID IS NOT NULL
);

/*
This constraint will ensure that at least one of the ApplicantID or AgentID columns is not NULL for each row in the Application table. 
This will prevent an application from being created by neither an applicant nor an agent.

Here is an explanation of the constraint:
- The CHECK keyword specifies that this is a check constraint.
- The CK_Application_ApplicantOrAgent is the name of the constraint.
- The CHECK (ApplicantID IS NOT NULL OR AgentID IS NOT NULL) expression specifies the condition that must be met for the constraint to be satisfied. In this case, the condition is that either the ApplicantID or AgentID column must not be NULL.
*/
CREATE TABLE Application (
  ApplicationID uniqueidentifier NOT NULL,
  ApplicationIncrement int NULL,
  CreatedDateTime datetime NULL,
  UpdatedDateTime datetime NULL,
  LastModifiedByEmail nvarchar(256) NULL,
  ClaimRandomIdentifier varchar(50) NULL,
  DateApplicationSubmitted datetime NULL,
  ApplicantID int NULL,
  AgentID int NULL,
  ApplicationStatus varchar(50) NULL,
  SynchronisationStatus varchar(20) NULL,
  DateTimeLastSynchronised datetime NULL,
  SyncError varchar(20) NULL,
  SyncErrorDesc varchar(4000) NULL,
  LoggedInUserId uniqueidentifier NULL,
  LoggedInUserABN nvarchar(11) NULL,
  IsActive bit NOT NULL,
  PRIMARY KEY (ApplicationID),
  FOREIGN KEY (ApplicantID) REFERENCES Applicant (ApplicantID),
  FOREIGN KEY (AgentID) REFERENCES Agent (AgentID),
  CONSTRAINT [CK_Application_ApplicantOrAgent] CHECK (
    ApplicantID IS NOT NULL OR AgentID IS NOT NULL
  )
);