How to make this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to make this?

Author

Topic

Swirl
Starting Member

2 Posts

Posted - 2013-05-05 : 16:32:36

I have the following table:

create table Sale(
bankname char(8) null,
controleoptiename char(10) not null,
creditcardnummer numeric(19) null,
username char(10) not null,
accountnummer numeric(7) null,
constraint pk_username primary key(username)
)

I'm trying to make a case/trigger with the following rule:
If the word "Creditcard" is inserted in controleoptiename then creditnummer must contain numbers else it will be NULL.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-05 : 20:49:26

Add a table-level check constraint like shown below:


alter table Sale
add constraint chkAccountNumber check
(
	(controleoptienname = 'Creditcard' and accountnummer is not null)
	or
	(controleoptienname <> 'Creditcard' and accountnummer is null)
);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 02:19:41

it should be


alter table Sale
add constraint chkreditCardInfo check
(
	controleoptienname <> 'Creditcard' OR creditnummer IS NOT NULL
);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Swirl
Starting Member

2 Posts

Posted - 2013-05-06 : 04:01:27

ty guys !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 04:28:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-06 : 08:18:41

quote:
Originally posted by visakh16

it should be

alter table Sale
add constraint chkreditCardInfo check
(
controleoptienname <> 'Creditcard' OR creditnummer IS NOT NULL
);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

No. It should not be.

The OP's requirement is a "logical biconditional" which cannot be expressed as a simple OR condition. Think about the case where the controleoptienname = 'Cash' and creditnummer = 12345. This condition should not be allowed.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-06 : 11:01:32

James is right.
Just a minor modification to his query:
[CODE]

alter table dbo.Sale
add constraint chkAccountNumber check
(
(controleoptiename = 'Creditcard' and creditcardnummer is not null)
or
(controleoptiename <> 'Creditcard' and creditcardnummer is null)
);

[/CODE]

visakh16's code does not work in the following scenario:
[CODE]
INSERT INTO dbo.sale(bankname, controleoptiename, creditcardnummer, username, accountnummer)
VALUES('HSBC', 'Cash', 12345, 'Joe Smoke', 12345.0); -- row will be added when it should not
[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-06 : 11:06:08

quote:
Originally posted by MuMu88

James is right.
Just a minor modification to his query:
[CODE]

alter table dbo.Sale
add constraint chkAccountNumber check
(
(controleoptiename = 'Creditcard' and creditcardnummer is not null)
or
(controleoptiename <> 'Creditcard' and creditcardnummer is null)
);

[/CODE]

visakh16's code does not work in the following scenario:
[CODE]
INSERT INTO dbo.sale(bankname, controleoptiename, creditcardnummer, username, accountnummer)
VALUES('HSBC', 'Cash', 12345, 'Joe Smoke', 12345.0); -- row will be added when it should not
[/CODE]

Ah, sorry, I missed that. Thank you MuMu88!!!

Would it help if I said that, that was really what I had in mind?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 00:33:35

quote:
Originally posted by James K

quote:
Originally posted by visakh16

it should be

alter table Sale
add constraint chkreditCardInfo check
(
controleoptienname <> 'Creditcard' OR creditnummer IS NOT NULL
);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

No. It should not be.

The OP's requirement is a "logical biconditional" which cannot be expressed as a simple OR condition. Think about the case where the controleoptienname = 'Cash' and creditnummer = 12345. This condition should not be allowed.

yep thats true
thanks for the catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources