CODEWITHSUNDEEP
sqlsql-serverconstraints
Avinash
Avinash

Reputation: 543

Constraint for restricting NULL values in combination of columns

I'm trying to create a table with primary key. For e.g below is the table structure

+--------+----------+------+-----------+--------------+
| ID     | NAME     | SOLD | PURCHASED | MANUFACTURED |
+--------+----------+------+-----------+--------------+
| 522345 | ProductA | 23   |   NULL    | 45           |
+--------+----------+------+-----------+--------------+
| 502345 | ProductB | NULL | 56        |   NULL       |
+--------+----------+------+-----------+--------------+

Here I have to make sure that all 3 columns(SOLD/PURCHASED/MANUFACTURED) should not be NULL. But any 1 or 2 columns can be NULL. How I can create a constraint for this case? Please suggest. I have tried the below query but it is expecting values for all 3 columns. I'm running out of ideas.

CREATE TABLE MYTABLE (
    ID VARCHAR(20) NOT NULL,
    NAME VARCHAR(50),
    SOLD INT NOT NULL,
    PURCHASED INT NOT NULL,
    MANUFACTURED INT NOT NULL,
    CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED 
        (
        [ID] ASC,
        [SOLD] ASC,
        [PURCHASED] ASC,
        [MANUFACTURED] ASC
        )
)

Upvotes: 1

Views: 356

Answers (2)

Ali Youhanaei
Ali Youhanaei

Reputation: 384

Use trigger to rollback transaction like this:

create trigger dbo.check_insert_null_items on dbo.MYTABLE after insert as
begin
    if exists ( select * from inserted where SOLD is null and PURCHASED is null and MANUFACTURED is null)
    begin
        rollback transaction
        raiserror ('error: ... ', 16, 1)
    end
end

Upvotes: 0

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can try CHECK CONSTRAINT

CREATE TABLE MYTABLE (
    ID VARCHAR(20) NOT NULL,
    NAME VARCHAR(50),
    SOLD INT,
    PURCHASED INT,
    MANUFACTURED INT,
    CONSTRAINT CHK_NULL CHECK ((SOLD IS NOT NULL) OR (MANUFACTURED IS NOT NULL) OR (PURCHASED IS NOT NULL))
)

Upvotes: 4

Related Questions