...

View Full Version : Constraints



kryssy23
05-26-2011, 02:54 AM
hey all,
I was wondering if there is such a bit of code that can check the date of birth against the current date and if there is 18+ years stores the data but if its under 18 theres an error?

at the moment this is my code. it creates the table and inserts both entries.


create table Cust
DateOfBirth DATETIME NULL CHECK (DateOfBirth < DATEADD(yy, -18, GETDATE()));



INSERT INTO Cust (DateOfBirth) VALUES (26-04-1988);
INSERT INTO Cust (DateOfBirth) VALUES (26-04-1998);


any help would be greatly appreciated :)

bazz
05-26-2011, 03:32 AM
I was wondering if there is such a bit of code that can check the date of birth against the current date and if there is 18+ years stores the data but if its under 18 theres an error?


nope. should be done in your server-side scripting language.

at the moment this is my code.




create table Cust
DateOfBirth DATETIME NULL CHECK (DateOfBirth < DATEADD(yy, -18, GETDATE()));

INSERT INTO Cust (DateOfBirth) VALUES (26-04-1988);
INSERT INTO Cust (DateOfBirth) VALUES (26-04-1998);



Try this


create table cust
( date_of_birth DATE not null) engine.....

insert into cust
( date_of_birth)
values ( '1998-04-26')


I think there's little point in making it a DATE column and then not storing in date format.

Old Pedant
05-26-2011, 08:11 PM
You *could* make such a check if you used a Stored Procedure to do the INSERT.

The SP would return a status of some kind indicating if the INSERT was done or not.

Many DB purists would argue that you should *ALWAYS* use Stored Procs when adding/updating your database, just so that you can build in data integrity checks. Granted, that's usually done when the DB administrator and the application programmer are different people or even different teams--not when all the work is done by one person--but even for a one person shop it's not a bad idea, to protect against your own future mistakes.

guelphdad
05-27-2011, 02:31 PM
To be clear MySQL does not support CHECK CONSTRAINTs at all.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum