Can I enforce user to enter value in minimum one of many nullable fields in SQL?

Question

Possible Duplicate:
One of the column between two columns should be NOT NULL. How to enforce it in schema?

I have a table with 5 null able fields and every record must have a value for one of those fields. Can I enforce user to enter value in minimum one of many nullable fields in SQL? Thanx

Use Check constraint on table. – Hamlet Hakobyan Jan 13 '13 at 08:26 — Hamlet Hakobyan, Jan 13 '13 at 08:26

score 5 · Answer 1 · answered Jan 13 '13 at 08:29

5

Yes, you can, using a CHECK constraint;

CREATE TABLE test
(
  id INT,
  val1 VARCHAR(32),
  val2 VARCHAR(32),
  val3 VARCHAR(32),
  val4 VARCHAR(32),
  val5 VARCHAR(32),
  CHECK (COALESCE(val1,val2,val3,val4,val5) IS NOT NULL)
);

...will require at least one of val1-val5 to be NOT NULL.

answered Jan 13 '13 at 08:29

Joachim Isaksson

176,943
25
281
294

Upvote! Using `COALESCE` is nice idea. – Hamlet Hakobyan Jan 13 '13 at 08:33

score 4 · Answer 2 · answered Jan 13 '13 at 08:29

4

ADD CONSTRAINT chkAnyNotNULL CHECK (col1 IS NOT NULL OR Col2 IS NOT NULL OR ....);

answered Jan 13 '13 at 08:29

Hamlet Hakobyan

32,965
6
52
68

score 1 · Answer 3 · answered Jan 13 '13 at 08:36

Yes. Using a CHECK CONSTRAINT;

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.MyTable') IS NOT NULL DROP TABLE MyTable

CREATE TABLE MyTable
(
    Col1    INT NULL,
    Col2    INT NULL,
    Col3    INT NULL,
    Col4    INT NULL,
    Col5    INT NULL,
    CONSTRAINT OneNonNull CHECK (COALESCE(Col1, Col2, Col3, Col4, Col5) IS NOT NULL)
)
GO

INSERT INTO MyTable
SELECT 1,2,3,4,5
GO

INSERT INTO MyTable
SELECT 1,NULL, NULL, NULL, NULL
GO

INSERT INTO MyTable
SELECT NULL, NULL, NULL, NULL, NULL
GO

SELECT *
FROM MyTable

Can I enforce user to enter value in minimum one of many nullable fields in SQL?

3 Answers3