Re: Date Table with Check Condition

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Oct 3, 4:25 pm, Gus <G...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I need to have a way to allow Users to add up to 4 years with each year
having 4 dates identified to belong to that year. I need to allow them the
capability to check or specify when a date for a certain year has been
completed.

ANSI-92 Query Mode syntax:

CREATE TABLE Users
(
user_ID VARCHAR(10) NOT NULL UNIQUE
)
;
CREATE TABLE UsersSpecialYears
(
user_ID VARCHAR(10) NOT NULL
REFERENCES Users (user_ID),
year_start_date DATETIME NOT NULL,
CONSTRAINT special_year_start_date__must_be_year_start
CHECK (year_start_date =
DATESERIAL(DATEPART('YYYY', year_start_date), 1, 1)),
UNIQUE (year_start_date, user_ID),
seq INTEGER NOT NULL,
CONSTRAINT special_year_seq__value_range
CHECK (seq BETWEEN 1 AND 4),
UNIQUE (user_ID, seq)
)
;
CREATE TABLE UsersSpecialOccasions
(
user_ID VARCHAR(10) NOT NULL
REFERENCES Users (user_ID),
year_start_date DATETIME NOT NULL,
FOREIGN KEY (year_start_date, user_ID)
REFERENCES UsersSpecialYears (year_start_date, user_ID),
occasions_date DATETIME NOT NULL,
CONSTRAINT special_occasion_date__no_time_element
CHECK (occasions_date = DATEVALUE(occasions_date)),
CONSTRAINT special_occasion_date__within_year
CHECK (DATEDIFF('YYYY', year_start_date, occasions_date) = 0),
UNIQUE (user_ID, occasions_date),
seq INTEGER NOT NULL,
CONSTRAINT special_occasion_seq__value_range
CHECK (seq BETWEEN 1 AND 4),
UNIQUE (year_start_date, user_ID, seq)
)
;
CREATE TABLE UsersSpecialOccasionsCompleted
(
user_ID VARCHAR(10) NOT NULL,
occasions_date DATETIME NOT NULL,
UNIQUE (user_ID, occasions_date),
FOREIGN KEY (user_ID, occasions_date)
REFERENCES UsersSpecialOccasions (user_ID, occasions_date)
)
;

Jamie.

--


.