2

I'm using SQL Server 2008 R2 and would like to make one of two columns required on a table; in other words, if the user provides a value for one, the other isn't required but the user can provide a value for both. How do you do this using check constraints? Thanks.

Update: Here's the table definition; the two columns are alternateID and securityID:

CREATE TABLE [dbo].[Employee](
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [alternateID] [nvarchar](50) NULL,
    [securityID] [nvarchar](50) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [job] [nvarchar](50) NULL,
    [organizationalUnit] [nvarchar](50) NOT NULL,
    [costCenter] [nvarchar](50) NOT NULL,
    [notes] [nvarchar](95) NULL,
    [createdDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [employeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
3

Expanding on ypercube's question comments:

The first thing you need to do is make the securityID column nullable:

ALTER TABLE dbo.Employee
ALTER COLUMN securityID nvarchar(50) NULL;

Then the required check constraint is quite straightforward:

ALTER TABLE dbo.Employee
ADD CONSTRAINT [CK dbo.Employee securityID or alternateID must not be NULL]
CHECK (securityID IS NOT NULL OR alternateID IS NOT NULL);

Regarding the 'friendly' name I chose for the check constraint, see:

Naming CHECK and UNIQUE Constraints by Greg Low.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.