SQL Integrity Contraints

[mattclements]

Hey,

I am looking at modifying my SQL Script to include an Integrity Contraint for my A-Level Project. One table includes a Behaviour Code as the Primary Key. I want this to be displayed as LL00 (2 Composery letters, followed by 2 composery numbers). I think it should be something like:

 

ALTER TABLE behaviour_code
  Then some Integrity Constraint

 

Cheers in advance.

Matt

[mattclements]

Any ideas people?

 

This is for an Assignment due in Monday  ???

 

Cheers

Matt

[Barand]

I would have expected it to be a CHECK constraint. All I could find in the MySQL manual was

 

"The CHECK  clause is parsed but ignored by all storage engines. "

[aschk]

CHECK is not a valid part of MySQL (yet, maybe v6.0)

Thus your problem might be resolved by using triggers instead. However you're going to have to split up your column and parse it (in the trigger) and reject the INSERT/UPDATE based on that.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

 

The above url should get your started with triggers. And you might consider using a REGEX http://dev.mysql.com/doc/refman/5.0/en/regexp.html to check if the value is in the format you are expecting.

[mattclements]

Hey,

Yer with some more research I found out the same.... This isn't going to have to be a "production" database as it's just for an assignment. Ive got an extention to finish this off....

 

Anyway... So far im thinking:

 

ALTER TABLE students
CONSTRAINT form
CHECK (form LIKE 10LLa);

 

Basically I only want users to be able to enter 1 Optional Number, then 1 Compulsary Numbers, then 2 Compulsary Letters, followed by 1 Optional Letter... Such as:

13JC

or 14JCa

or 9JC

or 9JCa

Also is there any way of just limiting the number values to be between 9-14?

 

Thanks,

Matthew

[fenway]

Why not validate this in the app?

[aschk]

If this is the case then just use a PHP regular expression.

 

<?php
$regex = "/^[0-9]{1,2}[a-zA-Z]{2,3}$/";
if(preg_match($regex,$str)){
  // it matched
} else {
  // it DIDN'T match.
}
?>

[mattclements]

This Assignment uses SQL only - No other language else I would use PHP.... is this even physically possible?

 

Cheers

Matt

[fenway]

This Assignment uses SQL only - No other language else I would use PHP.... is this even physically possible?

 

Cheers

Matt

At this stage, only with a trigger.

[mattclements]

What would the Trigger look like?

 

Cheers

Matt

[aschk]

That's for you to read up on. I've included the link to triggers in one of my previous posts.

As it's an "assignment" i'm guesing you're supposed to be doing the hard work