none
Is it possible to force a value in at least one of several columns?

    Question

  • Is it possible to define a constraint that says at least one of a set of columns must have a value?

    I have a table of securities.  There are three types of security identifiers that I am recording: CUSIP, SEDOL, ISIN.  So I have a column for each type of identifier.  Is it possible to set a constraint to make sure that at least one of those columns has a value?

    Thanks.

    J


    http://digitalcamel.blogspot.com/

    Sunday, March 02, 2014 5:25 AM

Answers

  • Hi J,

    Try like this,

    
    CREATE TABLE T1
     (
     CUSIP int ,
     SEDOL int,
     ISIN int,
     CHECK (CUSIP IS NOT NULL OR SEDOL IS NOT NULL OR ISIN IS NOT NULL)
     )
    
     INSERT T1 SELECT 1,NULL,NULL
     INSERT T1 SELECT NULL,2,NULL
     INSERT T1 SELECT NULL,NULL,3
     INSERT T1 SELECT NULL,NULL,NULL
    
     SELECT * FROM T1


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Marked as answer by Digital Camel Sunday, March 02, 2014 6:08 AM
    Sunday, March 02, 2014 6:04 AM
    Moderator
  • Can be simplified as below

    CREATE TABLE T1
     (
     CUSIP int ,
     SEDOL int,
     ISIN int,
     CHECK (COALESCE(CUSIP,SEDOL,ISIN) IS NOT NULL)
     )
     


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 02, 2014 6:22 AM

All replies

  • Hi J,

    Try like this,

    
    CREATE TABLE T1
     (
     CUSIP int ,
     SEDOL int,
     ISIN int,
     CHECK (CUSIP IS NOT NULL OR SEDOL IS NOT NULL OR ISIN IS NOT NULL)
     )
    
     INSERT T1 SELECT 1,NULL,NULL
     INSERT T1 SELECT NULL,2,NULL
     INSERT T1 SELECT NULL,NULL,3
     INSERT T1 SELECT NULL,NULL,NULL
    
     SELECT * FROM T1


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Marked as answer by Digital Camel Sunday, March 02, 2014 6:08 AM
    Sunday, March 02, 2014 6:04 AM
    Moderator
  • Perfect, thanks...

    J


    http://digitalcamel.blogspot.com/

    Sunday, March 02, 2014 6:09 AM
  • ALTER TABLE [dbo].[youtable]  WITH CHECK ADD  CONSTRAINT [CK_yourtable] CHECK  ((((isnull([CUSIP],'')+isnull([SEDOL],''))+isnull([ISIN],''))<>''))
    GO
    
    ALTER TABLE [dbo].[yourtable] CHECK CONSTRAINT [CK_yourtable]
    GO
    

    Sunday, March 02, 2014 6:12 AM
    Moderator
  • Can be simplified as below

    CREATE TABLE T1
     (
     CUSIP int ,
     SEDOL int,
     ISIN int,
     CHECK (COALESCE(CUSIP,SEDOL,ISIN) IS NOT NULL)
     )
     


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 02, 2014 6:22 AM