How to show multiple individuals>

Question

How to show multiple individuals>

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

Thanks for the correction, Scott. I plumb forgot about that aspect of CHECKs (mostly because I make end-date columns NOT NULL). They're like FK's in that aspect... they don't have to have values present (NULL) but any value inserted (NULL is not a value) must pass the check.

Just to demonstrate, I fixed a couple of minor errors in Joe's CREATE TABLE code and did his INSERT of data and you are correct... the CHECK constraint did NOT prevent the inserts where the end date was null.

I've also corrected my previous post so that no one is led astray.

CREATE TABLE dbo.Employee_Leave
(company_duns CHAR(8) NOT NULL,
emp_id CHAR(4) NOT NULL,
leave_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
leave_end_date DATE,
CHECK(leave_start_date <= leave_end_date),
PRIMARY KEY(company_duns, emp_id, leave_start_date)
);

INSERT INTO dbo.Employee_Leave
VALUES
(1, 1001, '2020-12-22', NULL),
(1, 1001, '2020-12-23', NULL),
(1, 1001, '2020-12-24', NULL),
(1, 1001, '2020-12-28', NULL),
(1, 1001, '2020-12-29', NULL),
(1, 1001, '2020-12-30', NULL),
(1, 1001, '2020-12-31', NULL),
(1, 1002, '2020-11-23', NULL),
(1, 1002, '2020-11-24', NULL),
(1, 1002, '2020-11-25', NULL);

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

This is a common misconception about the three valued logic and SQL. We have three logical values: true, false and unknown. The search condition used in WHERE or ON clauses tests (false, unknown), then the search condition rejects it. However, if the search condition in a CHECK() constraint tests (true, unknown), then the search condition accepts it. We call this the "benefit of the doubt." Rule. The reason we designed it this way was that otherwise the constant testing for null would've made a mess out of the check constraints. If you need to get fancier than that, a full ANSI standard SQL has a <expression> IS [NOT] {TRUE | FALSE | UNKNOWN } clause you can use. I've never seen anybody do it, but that might be because most implementations of SQL don't have it

Please post DDL and follow ANSI/ISO standards when asking for help.