Constraint where both columns cannot be null, but one can

[agr8lemon]

I'm a bit of a SQL newbie so I'm wondering if you can help me. I've looked on Google it for some time, but maybe I'm not using the proper terms.

Here is my question:

I have TableA that has the following columns: ID(PK), location, departA, departB.

I would like

a constraint across departA and departB so that both of them can not have NULL values. Both are able to have values.
If one has a NULL value, the other must not be NULL.
The values can be the same for both columns in the row
departA must be unique for its column
departB must be unique for its column

I hope that makes sense, but if not I can clarify.

Thank you for the help!!

[codewall]

you can set the departA and departB to unique or you can add additional condition in your program if you use this sql, check each entry before they inserted into database.

SNIP

[buddylee17]

Not sure about the unique part, as a unique constraint will only support one null value in the column. It sounds like you potentially could have many.

Use a check constraint to verify both aren't null:

ALTER TABLE TableA ADD CONSTRAINT CK_BothDepartsNotNull
    CHECK (departA IS NOT NULL OR departB IS NOT NULL)

[ashif90]

hai,friends i have one doubt in sql here i mentioned that please help me.

COLUMN NAME1 COLUMN NAME2

A 1

A 2

A NULL

B 3

C NULL

this is my table from that i have to select that not null values of A and B and also select NULL value of C.that is if A having NULL values only and C having NOT NULL values only means I have to select NULL values of A and not null values of C.that is if A having both NULL and NOT NULL values means i want NOT NULL only of A and C having NULL values means i want NULL values of C.at the same time if C having NULL AND NOT NULL value means i want the NOT NULL values of C.please help me soon.am waiting

[adam_k]

1st of all don't hijack old threads.

I'd use dynamic SQL to figure out the query, but you are not showing any effort.