nullability

Question

nullability

neelam choudhary

Valued Member

Points: 63
More actions
February 22, 2006 at 7:02 pm

#93648

in database i have a table in which entry in one column should be allowed only when a particular column is filled .
also if the particular column is filled the column is required to be filled
so how should i implement it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

You have two options:

Implement this login in the appropriate insert and update stored procedures, or use an instead of trigger for insert and update.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It should be doable as a table constraint

Something like something like (ColA IS NULL AND ColB IS NULL) OR (ColA IS NOT NULL AND ColB IS NOT NULL)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Muaddib Old Hand Points: 350 More actions · Answer 3

In your table design, make both columns nullable. Then add an INSERT/UPDATE trigger to check that if inserted.ColA = 'xx' and inserted.ColB is NULL then rollback else happy days!

jp.emvia SSC Journeyman Points: 99 More actions · Answer 4

Use computed columns.

Example :

CREATE TABLE BaseTable

(PrimaryKey int PRIMARY KEY IDENTITY(1,1),

Color nvarchar(10) NOT NULL,

Material nvarchar(10) NOT NULL,

ComputedCol AS (Color + Material)

)

GO

OR

you can check constraint:

CHECK (CreditRating >= 1 and CreditRating <= 5)

I hope this will help you!

Best Wishes,

JP

or

CONSTRAINT CK_emp_id CHECK (emp_id LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'

OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

or

CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')

OR emp_id LIKE '99[0-9][0-9]')