January 26, 2012 at 4:38 pm
I'm a student and a newbie with databases. I was wondering if anyone could provide me some information on constraints. What would the basic way to write a constraint for the following.
A death date preceding a birth date
A pregnancy flag for a male
Authority for welfare to minors granted to senior citizens
Authority for family subsistence payments for wealthy residents
Aid to unmarried females when a boyfriend or father shares expenses
What would it be? and are there other ways our group could write it?
Thanks for the help!!
January 26, 2012 at 6:38 pm
I would google the syntax for creating tables and creating constraints. One good site for SQL Server syntax: http://msdn.microsoft.com/en-us
Once you get the syntax, get on SQL Server and play around with creating the table and constraints and inserting records into the table to see if it worked. I did the problems you listed as a practice (I'm a newbie as well), and was able to fulfill all the problems with CHECK constraints.
If you still have trouble, post the code you come up with and people here will definitely help you out.
January 27, 2012 at 7:37 am
This bol link will explain check constraints. http://msdn.microsoft.com/en-us/library/ms188258.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2012 at 9:49 am
earnest.williams (1/26/2012)
I'm a student and a newbie with databases. I was wondering if anyone could provide me some information on constraints. What would the basic way to write a constraint for the following.A death date preceding a birth date
A pregnancy flag for a male
Authority for welfare to minors granted to senior citizens
Authority for family subsistence payments for wealthy residents
Aid to unmarried females when a boyfriend or father shares expenses
What would it be? and are there other ways our group could write it?
Thanks for the help!!
For some of these constraints we'd need to see the tables and database to determine how to implement them.
Based on what you've given us it looks like you could use check constraints for all of these. You should read the links provided, but I'll give you a quick example for your first one(adds constraint AFTER table is already created):
ALTER TABLE dbo.persons
ADD CONSTRAINT CK_validate_death_date CHECK (death_date is null OR death_date >= birth_date)
This will insure that the death date occurs on or after the birth date (assuming you could die the same day you were born, still-birth)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 27, 2012 at 10:01 am
Jack Corbett (1/27/2012)
earnest.williams (1/26/2012)
I'm a student and a newbie with databases. I was wondering if anyone could provide me some information on constraints. What would the basic way to write a constraint for the following.A death date preceding a birth date
A pregnancy flag for a male
Authority for welfare to minors granted to senior citizens
Authority for family subsistence payments for wealthy residents
Aid to unmarried females when a boyfriend or father shares expenses
What would it be? and are there other ways our group could write it?
Thanks for the help!!
For some of these constraints we'd need to see the tables and database to determine how to implement them.
Based on what you've given us it looks like you could use check constraints for all of these. You should read the links provided, but I'll give you a quick example for your first one(adds constraint AFTER table is already created):
ALTER TABLE dbo.personsADD CONSTRAINT CK_validate_death_date CHECK (death_date is null OR death_date >= birth_date)
This will insure that the death date occurs on or after the birth date (assuming you could die the same day you were born, still-birth)
The only think you MIGHT want to add to that is that it will allow a death_date when there is no birth_date.
You could modify the constraint slightly like this.
ALTER TABLE dbo.persons
ADD CONSTRAINT CK_validate_death_date CHECK (death_date is null OR death_date >= isnull(birth_date, death_date + 1))
This will ensure you don't have a death_date when there is no birth_date. This may not be important in your case because it is certainly possible that the birth_date simply isn't known. But, it is important from an understanding perspective.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2012 at 8:22 am
ok so here is what i came up with, can you guys tell me how it looks?
A death date preceding a birth date
ALTER TABLE TableName
ADD CONSTRAINT constraintname
CHECK (death_date is NOT NULL OR death_date >= birth_date)
GO
A pregnancy flag for a male
ALTER TABLE TableName
ADD CONSTRAINT chkcnst CHECK (if(SexColumn='Male') then PregnancyFlagColumn=TRUE);
GO
Authority for welfare to minors granted to senior citizens
ALTER TABLE TableName
ADD CONSTRAINT chkcnst CHECK (if(PeopleType='Senior Citizens') then Authorityforwelfaretominors=true);
GO
Authority for family subsistence payments for wealthy residents
ALTER TABLE TableName
ADD CONSTRAINT chkcnst CHECK (if(PeopleType='wealthy residents') then Authorityforfamilysubsistencepayments=true);
GO
Aid to unmarried females when a boyfriend or father shares expenses
ALTER TABLE TableName
ADD CONSTRAINT chkcnst CHECK
(
if((shareperson='boyfriend' OR shareperson='father') AND shareexpenseflag=true) then
Aidtounmarriedfemale=true;
);
GO
January 29, 2012 at 9:13 am
There's no IF.. THEN construct in constraints.
The constraint is a logical expression that returns true or false only, no other values
Look at how the first one is done
CHECK (death_date is NULL OR death_date >= birth_date)
If that evaluates to true, then the row is valid, if it evaluates to false then the row is not valid. You need to express the rest of the constraints the same way, a logical expression that returns either true or false.
Eg, the second one would be
CHECK ((SexColumn='Female' AND PregnancyFlagColumn = 'Y') OR PregnancyFlagColumn = 'N')
That assumes that the PregnancyFlagColumn is a char that is either Y or N. If its anything else or nullable you'd have to adjust that.
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
January 29, 2012 at 9:21 am
Thanks Gila!
So the code should read like this?
ALTER TABLE TableName
ADD CONSTRAINT chkcnst CHECK((SexColumn='Female' AND PregnancyFlagColumn = 'Y') OR PregnancyFlagColumn = 'N') ;
GO
January 29, 2012 at 9:40 am
Assuming that SexColumn contains only 'Male' and 'Female' and that PregnancyFlagColumn is a char that is either Y or N, yes.
Just one thing, please give constraints meaningful names.
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
January 30, 2012 at 7:29 am
earnest.williams (1/29/2012)
ok so here is what i came up with, can you guys tell me how it looks?A death date preceding a birth date
ALTER TABLE TableName
ADD CONSTRAINT constraintname
CHECK (death_date is NOT NULL OR death_date >= birth_date)
GO
Aside from the other comments look at your constraint and ask yourself if that is really going to do what you want. This says death_date is NOT NULL. The second half of your check will NEVER be evaluated because if death_date is not null there is no point checking if it is after the birthdate. Look at what Jack posted above. You want the check to allow for nulls but if it is NOT null then the value should be greater than birth_date.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2012 at 3:51 am
My two cents worth: there really isn't any need to have the word 'Column' in your column definitions. It just clutters up your SQL.
January 31, 2012 at 8:51 am
earnest.williams (1/26/2012)
I'm a student and a newbie with databases. I was wondering if anyone could provide me some information on constraints. What would the basic way to write a constraint for the following.A death date preceding a birth date
A pregnancy flag for a male
Authority for welfare to minors granted to senior citizens
Authority for family subsistence payments for wealthy residents
Aid to unmarried females when a boyfriend or father shares expenses
What would it be? and are there other ways our group could write it?
Thanks for the help!!
There are several different types of table and column constraints: check, foreign key, default, nullability; and they all come into play.
OK, so some of your business rules are based on attributes (like age, marital status or income) that are temporal or subject to update over time, so you need a table that captures a claimant's current profile at the time each claim is paid in something like a CLAIM table. You should NOT maintain these indicators as columns in the primary CLAIMANT table, because that violates a couple of normalization rules and functionally it's less accountable; you would lose historical audit capability.
Here is a rough 5 minute illustration of what you may need. Even better, with some additional design work, you could probably enforce your rules using a data-driven refrence table and foreign key constraints instead of a check constraint.
create table claimant
(
ssn char(9) not null primary key,
sex_code char(1) not null,
dob smalldatetime not null,
dod smalldatetime null
);
create table claimant_claim
(
primary key ( ssn, claim_code, claim_date ),
ssn char(9) not null,
claim_code char(2) not null
foreign key (claim_code) references claim ( claim_code ),
claim_date smalldatetime not null,
sex_code char(1) not null
foreign key (sex_code) references sex ( sex_code ),
age_code char(2) not null
foreign key (age_code) references age ( age_code ),
income_codechar(2) not null,
foreign key (income_code) references income ( income_code ),
dependent_code char(2) not null
foreign key (dependent_code) references dependent ( dependent_code ),
maritial_code char(2) not null
foreign key (maritial_code) references marital ( maritial_code ),
check
(
case
-- Authority for welfare to minors granted to senior citizens:
when claim_code = 'WELLFARE' and age_code in ('01','02') then 1
-- Authority for family subsistence payments for wealthy residents:
when claim_code = 'SUBSISTENCE' and income_code in ('01','02','03') then 1
-- Aid to unmarried females when a boyfriend or father shares expenses:
when claim_code = 'SINGLEMOM' and sex_code = 'F' and maritial_code = 'S' and dependent_code = '00' then 1
else 0
end = 1
)
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 30, 2022 at 7:39 pm
This reply has been reported for inappropriate content.
I, too, am a student, learning programming languages, and thank you all for sharing your experiences. It's very difficult for students to learn programming on their own initiative and therefore I have a question: how do you allocate time for learning? I always try to reduce the time to write essays just by browsing free essay samples here: https://eduzaurus.com/free-essay-samples/penny-debate/ and not spending a lot of time on it. If you have any other tips, please share them and not only here
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply