Contraint Problem - 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 2000 Forums

SQL Server Development (2000)

Contraint Problem

Author

Topic

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-28 : 23:02:57

Hi,
'm Ssrry for so many questions, but I need some help with yet another problem.

CREATE TABLE cust_sample
(
cust_id int
PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_country char(15),
cust_credit_limit money,
)

I would like to create a constraint on the above table lets the user put in a value for cust_credit_limit only if the value of cust_country is "Germany". The cust_country is anythis else than "Germany", then cust_credit_limit should be left blank. How do i eforce this (..If possible) preferably with the help of a constraint?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 23:33:02

[code]
alter table cust_sample
add constraint
CK_cust_sample_credit_limit
check
(
cust_country = 'Germany' or
cust_credit_limit is null
)

[/code]

CODO ERGO SUM

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-29 : 01:08:30

Hey MVJ,
It isn't working. I tried typing out some dummy data through SQL Enterprise Manager but it didnt work. When i typed "Germany" in the cust_country field, the databse still allowed me to leave the cust_credit_limit field blank. This shouldnt be allowed.

Kristen
Test

22859 Posts

Posted - 2006-09-29 : 01:42:50

I would like to create a constraint on the above table ~~lets~~ forces the user put in a value for cust_credit_limit ...

you mean?

Just change the logic of MVJ's Check Constraint to include both conditions - i.e. both:

Country = 'Germany' and CreditLimit is NOT NULL

or

Country <> 'Germany' and CreditLimit is NULL -- take care if Country allows NULLs too

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-29 : 09:42:38

I have a bad habit of coding things exactly the way people ask, not the way they should ask.

CODO ERGO SUM

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-10-02 : 09:42:59

hey guys,
sorry to be buggin you again but it isnt working.

INSERT INTO cust_sample(cust_name, cust_country) VALUES('George', 'USA')

I keep getting a constraint error. Why is this happening? The code for my table is:

CREATE TABLE [dbo].[cust_sample] (
[cust_id] [int] IDENTITY (1, 1) NOT NULL ,
[cust_name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cust_address] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_country] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cust_credit_limit] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cust_sample] WITH NOCHECK ADD
CONSTRAINT [PK__cust_sample__3A81B327] PRIMARY KEY CLUSTERED
(
[cust_id]
) ON [PRIMARY] ,
CONSTRAINT [CK_cust_sample_credit_limit] CHECK ([cust_country] = 'Germany' and [cust_credit_limit] is not null)
GO

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 09:59:34

Your constraint says

[cust_country] = 'Germany'

and therefore ONLY Germany is a valid value.

What I think you have described your constraint as needing to handle is (as I said above), and in Plain English:


Country = 'Germany' and CreditLimit is NOT NULL

OR

Country <> 'Germany' and CreditLimit is NULL -- take care if Country allows NULLs too

Kristen

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-10-02 : 19:14:42

hey kristen,
you might think i'm dumb but i couldn't understand what you meant exactly.

What i need is:

Users hsould abe able to type 'any' country name in the country field. Null are not allowed in the country field. If the country filled in 'Germany' then a cust_credit_limit should not be empty.

How do I achieve this? Could you modify the contraint I have given you, please!

Thanks.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-10-02 : 19:33:38

quote:

(cust_country = 'Germany' and cust_credit_limit is not null) or cust_country != 'Germany'

DavidM

Production is just another testing cycle

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-10-02 : 22:01:30

Thank you everyone!

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 00:38:55

mridang_agarwal I don't think you are dumb, but I do think you need to figure out enough so you understand the solution - rather than you using answers that folk give you without perhaps understanding them, as then you won't be able to test and support them.

As an example you seem happy with byrmol's constraint, but it does not cover one of your stated conditions:

"The cust_country is anythis else than "Germany", then cust_credit_limit should be left blank."

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-03 : 05:55:41

Give the guy a break. You only gave him the answer two times and you didn't even write the exact code for him.

CODO ERGO SUM

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-03 : 09:33:24

Does this business rule create a transitive dependancy between cust_country and cust_credit_limit money?

Jay White

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-03 : 09:37:56

quote:
Does this business rule create a transitive dependancy between cust_country and cust_credit_limit money?

did anyone else read this as transvestite depends?

I really, REALLY need some more coffee...

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-10-03 : 19:28:59

quote:
but it does not cover one of your stated conditions

I don't think he/she knows what they want..

Perhaps..

(cust_country = 'Germany' and cust_credit_limit is not null) or (cust_country != 'Germany'and cust_credit_limit is null)

quote:
Does this business rule create a transitive dependancy between cust_country and cust_credit_limit money?

Possibly..

cust_id (ID) determines cust_country (CC)
(cust_id and cust_country) determines cust_credit_limit (CCL)

ID -> CC
{ID,CC} -> CCL

The question is if CC -> CCL is valid for all values

If it does then it would be a transitive dependancy..
ID -> CC, CC -> CCL gives ID -> CCL

My money is on there not being a transitive dependancy and this schema is in 1NF.

You at least have to give the bloke credit for wanting to put a constraint on it in the first place.

DavidM

Production is just another testing cycle

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-10-04 : 13:26:23

CREATE TABLE cust_sample
(
cust_id int
PRIMARY KEY,
cust_name char(50),
cust_address1 char(50),
cust_address2 char(50),
cust_address3 char(50),
cust_country char(15),
cust_credit_limit money,
)

hey guys, in this table if i woant to create a constraint that specifies that either cust_address1,cust_address2 or cust_address3 has to be filled in - what would be the logic?

Kristen
Test

22859 Posts

Posted - 2006-10-04 : 14:41:39

What have you tried so far?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources