Constraints in Tables - Page 2

andrewst · #16 (**permalink**) 11-12-08, 10:54

Quote:

Originally Posted by r937

i think you'll find that MOD, LEN, and MID are not ANSI SQL functions

Fair enough - I was misled by a Google result on "ANSI SQL" leading me to w3schools, which I took to be documenting ANSI SQL.

But my question is - doesn't ANSI SQL have anything equivalent to these functions? I have no idea, just kind of expected it would. After all, Joe Celko must need to write check constraints to verify his VINs and whatnot? In fact, having now Googled for "celko check substring" and found this article it appears to be:

Code:

( SUBSTRING (person_Number FROM 1 FOR 1) = 'P'
AND SUBSTRING (person_Number FROM 2 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 3 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 4 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 5 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 6 FOR 1) BETWEEN '0' AND '9'
)

We don't need to test the maximum length, since the column type does that.

r937 · #17 (**permalink**) 11-12-08, 11:25

that's much nicer

yeah, a lot of people are misled by stuff they find on w3schools

and that forensic article by celko is a classic

Grich · #18 (**permalink**) 11-12-08, 17:38

Thanks andrewst for the research. Just one thing (it's going to sound lame): Were do you place this piece of code in the database script?

Quote:

Originally Posted by andrewst

Code:

( SUBSTRING (person_Number FROM 1 FOR 1) = 'P'
AND SUBSTRING (person_Number FROM 2 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 3 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 4 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 5 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING (person_Number FROM 6 FOR 1) BETWEEN '0' AND '9'
)

Pat Phelan · #19 (**permalink**) 11-12-08, 18:05

I'm not sure that this would work (I don't have an ISO compliant SQL that I can test with), but how about:

Code:

person_number BETWEEN 'P00000' AND 'P99999'

Note that this still does zilch to enforce sequence. That problem I don't know how to fix using strictly standard SQL.

-PatP

r937 · #20 (**permalink**) 11-12-08, 18:37

pat, that constraint would allow P10SXR

andrewst · #21 (**permalink**) 11-13-08, 04:45

Quote:

Originally Posted by Grich

Thanks andrewst for the research. Just one thing (it's going to sound lame): Were do you place this piece of code in the database script?

Something like:

Code:

CREATE TABLE person
(
person_Number VARCHAR(6) PRIMARY KEY,
person_Name VARCHAR(50) NOT NULL,
person_UserName VARCHAR(20) NOT NULL,
person_Password VARCHAR(15) NOT NULL,
constraint person_number_chk check
  ( SUBSTRING (person_Number FROM 1 FOR 1) = 'P'
  AND SUBSTRING (person_Number FROM 2 FOR 1) BETWEEN '0' AND '9'
  AND SUBSTRING (person_Number FROM 3 FOR 1) BETWEEN '0' AND '9'
  AND SUBSTRING (person_Number FROM 4 FOR 1) BETWEEN '0' AND '9'
  AND SUBSTRING (person_Number FROM 5 FOR 1) BETWEEN '0' AND '9'
  AND SUBSTRING (person_Number FROM 6 FOR 1) BETWEEN '0' AND '9'
  )
)

Grich · #22 (**permalink**) 11-13-08, 06:16

Thanks andrewst

. I'll try it out.

stolze · #23 (**permalink**) 11-13-08, 08:48

You can use GENERATED ALWAYS AS IDENTITY to come up with the number. (This is in ISO/IEC 9075-02:2003 in subclause 11.4, "<column definition>".) Then you build a view on top of the base table and in the view, you convert the number to a string and prepend the 'P'.

p.s: MOD is ISO SQL (which ANSI adopted as its standard, too) - but for a completely different purpose.

Peter.Vanroose · #24 (**permalink**) 12-05-08, 16:31

Quote:

Originally Posted by Pat Phelan

CONSTRAINT user_type LIKE 'P[0-9][0-9][0-9][0-9][0-9]'

Actually, this *is* (close to) an ANSI SQL standard, namely the SQL2003 standard for regular expressions in the new "SIMILAR" predicate:

Code:

CONSTRAINT user_type SIMILAR TO 'P[0-9][0-9][0-9][0-9][0-9]'

or slightly shorter:

Code:

CONSTRAINT user_type SIMILAR TO 'P[0-9]{5}'

Internet Services

Web Development

Digital Marketing

Consumer Tech