0

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!!

5
Contributors
4
Replies
6
Views
6 Years
Discussion Span
Last Post by adam_k
0

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

Edited by happygeek: fake sig snipped

0

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)

Edited by buddylee17: n/a

0

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

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.