SQL Server Performance Forum – Threads Archive

check constrain how?

I have a table with homephone and office phone fields. either one of the field should have value when inserting records. meaning either one may be null when other has value. whats the best way to achieve this?. triggers may slow down this process if use. thanks

quote:Originally posted by joseln I have a table with homephone and office phone fields. either one of the field should have value when inserting records. meaning either one may be null when other has value. whats the best way to achieve this?. triggers may slow down this process if use. thanks

Try this: create table #test
(
homephonevarchar(20),
officephonevarchar(20),
CHECK ((homephone is not null and officephone is null) OR
(homephone is null and officephone is not null) OR
(homephone is not null and officephone is not null)
)
)
Karl Grambow www.sqldbcontrol.com

A shorter solution exists thus: CREATE TABLE test
(homephone varchar(20), officephone varchar(20),
CHECK (DATALENGTH(COALESCE(homephone,officephone,”))>0)
) It works as requested and even has the added advantage of checking for empty (zero length strings) in the column values Nathan H. Omukwenyi
MVP [SQL Server]

quote:Originally posted by vbkenya A shorter solution exists thus: CREATE TABLE test
(homephone varchar(20), officephone varchar(20),
CHECK (DATALENGTH(COALESCE(homephone,officephone,”))>0)
) It works as requested and even has the added advantage of checking for empty (zero length strings) in the column values Nathan H. Omukwenyi
MVP [SQL Server]

Good point. I always overlook the COALESCE function.[:I] Karl Grambow www.sqldbcontrol.com

Try ISNULL() instead of COALESCE() – I remember COALESCE isn’t the fastest function out there in set-based operations.

Agreed. COALESCE is definitely slower than ISNULL() OR even IS NOT NULL when used, in this case, during multiple/bulk(>10000) row data inserts. But for single row inserts, the difference is barely noticable to the human eye. The beauty of COALESCE() revolves around it’s ability to accept more than one parameter. Nathan H. Omukwenyi
MVP [SQL Server]

True, but that’s one of the reasons I prefer to do this type of stuff in triggers.

Here is an interesting article about it
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx Madhivanan Failing to plan is Planning to fail

if use this (below)
CREATE TABLE test
(homephone varchar(20), officephone varchar(20),
CHECK (DATALENGTH(COALESCE(homephone,officephone,”))>0)
) when inserting records ie insert into test (homephone, officephone) values (‘46847638’,”) constrain error throws…

Try CREATE TABLE test
(homephone varchar(20), officephone varchar(20),
CHECK (DATALENGTH(COALESCE(homephone,officephone,”))>0 or DATALENGTH(COALESCE(officephone,homephone,”))>0)
) Madhivanan Failing to plan is Planning to fail

quote:Originally posted by joseln if use this (below)
CREATE TABLE test
(homephone varchar(20), officephone varchar(20),
CHECK (DATALENGTH(COALESCE(homephone,officephone,”))>0)
) when inserting records ie insert into test (homephone, officephone) values (‘46847638’,”) constrain error throws…

What is the error message you get? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Joseln, the constraint as it is checks also for empty strings in the column entries. Are you sure you want to allow empty strings in your fields? If you allow them, somebody might enter (”,”) or (NULL,”) or (”,NULL) and that would still be useless.
Nathan H. Omukwenyi
MVP [SQL Server]

No i dont want both to be null or empty. you are right. but i dont get error when i insert empty string for the below insert into test (homephone, officephone) values (‘234234324’,”)
but i get error when insert into test (homephone, officephone) values (”,’5866543758′)
for me, it can allow empty even when other have value which happens in the first insert statement but not the second.
thanks

ps. IGNORE THE ABOVE POST Its working with CREATE TABLE test
(homephone varchar(20), officephone varchar(20),
CHECK (DATALENGTH(COALESCE(homephone,officephone,”))>0 or DATALENGTH(COALESCE(officephone,homephone,”))>0)
) thanks madhi , nathan and others.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">insert into test (homephone, officephone) values (‘234234324′,”) but i get error when insert into test (homephone, officephone) values (”,’5866543758′) <hr height="1" noshade id="quote"></blockquote id="quote"> Well. Before posting check the query again. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>