1

I am trying to set the studentID field so that it can only accept 8 digits. I know that I can't do this with check constraints because my SQL won't accept it. Also how would I set a field so that it cannot accept a number that is less than one in the EventID field?

OK. SO, from the link I was given I have tried to make a trigger. Would this be accurate? I am unsure of the syntax or why there are so many semi colons in one statement. I also assume I would need a trigger for every table containing studentID? Thank you

CREATE TABLE STUDENT (
StudentID       Numeric(8) NOT NULL,
Password        CHAR(15)   NOT NULL,
CONSTRAINT STUDENTSTUDID_PRI_KEY PRIMARY KEY(StudentID));

CREATE TRIGGER STUD_ID_TRIG
BEFORE INSERT ON STUDENT
FOR EACH ROW BEGIN

DECLARE numLength INT;
SET numLength = (SELECT LENGTH(NEW.StudentID));

IF (numLength = 8 ) THEN
SET NEW.col = 1/0;
END IF;
END;

Can someone explain to me what the SET NEW.COL = 1/0; means?

Pete
  • 83
  • 1
  • 7
  • Possible duplicate of [How to abort INSERT operation in MySql trigger?](http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger) – Drew Nov 30 '15 at 16:44
  • If you mean I am the same poster then no. Thanks for the link though – Pete Nov 30 '15 at 16:56
  • I don't understand how that example works for my code – Pete Nov 30 '15 at 16:58
  • the signal concept. A dupe doesn't have to line up completely right, it has to show "oh this would have been a good search term" – Drew Nov 30 '15 at 16:59
  • Nope. You've lost me. My question is about having a fixed length number. It is a primary key so there will be no duplicates – Pete Nov 30 '15 at 17:02
  • then enforce it in the gui. if you want the db to enforce it, there is the url above – Drew Nov 30 '15 at 17:03
  • I need to do it in the code. I do not understand what is going on in the code in the url or how it relates to my problem. – Pete Nov 30 '15 at 19:19

2 Answers2

1

Borrowing from the fine Answer mentioned above in comments, or at least the question it hung under, (and keeping its funny custom error message),

I give this Answer below:

Schema

CREATE TABLE STUDENT
(   StudentID int(8) not null primary key,  -- 8 is display width, otherwise meaningless. It is an int
    Password        CHAR(15)   NOT NULL
);

Trigger

drop trigger if exists `STUDENT_SomeTrigger`;
DELIMITER $$
CREATE TRIGGER `STUDENT_SomeTrigger`
BEFORE INSERT ON `STUDENT`
FOR EACH ROW
BEGIN
    DECLARE msg varchar(1000);
    IF (New.StudentId < 10000000 or New.StudentId > 99999999) THEN
        # it is not 8 digits,  don't allow it
        # note, leading zeros don't count!
        # you made it an int (well you called it NUMERIC, same thing)
        # if you mean for it to be a string, then do so
        set msg = "DIE: You broke the rules... I will now Smite you, hold still...";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;

    -- Do any other code here you may want to occur if it's all OK or leave blank it will be
    --  skipped if the above if is true
END
$$
DELIMITER ;

Test it

insert STUDENT(StudentID,password) values (12345678,'OpenSesame');
-- 1 row(s) affected
insert STUDENT(StudentID,password) values (1234567,'No Insert');
-- Error Code: 1644. DIE: You broke the rules... I will now Smite you, hold still...
insert STUDENT(StudentID,password) values (123456789,'No Insert');
-- Error Code: 1644. DIE: You broke the rules... I will now Smite you, hold still...
insert STUDENT(StudentID,password) values (87654321,'Ok also');
-- 1 row(s) affected

Look at data

select * from STUDENT;
+-----------+------------+
| StudentID | Password   |
+-----------+------------+
|  12345678 | OpenSesame |
|  87654321 | Ok also    |
+-----------+------------+

That is how a trigger would achieve your requirement of ID sizing. That is, the ID must be 8 digits.

So that is why I marked it as a duplicate, but you wanted to see it in action, which is understandable.

Please see the Manual page on Trigger Syntax.

Please see the Manual page on Signal. A long page on it. A quick excerpt:

SIGNAL is the way to “return” an error. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. Also, it provides control over the error's characteristics (error number, SQLSTATE value, message). Without SIGNAL, it is necessary to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to return an error.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Is this still okay for MySQL? I notice you changed the syntax of the primary key. Are the indents important? Also, it is not obvious to me why it understands it as less than 8 digits and not 'less than 10 million' – Pete Nov 30 '15 at 20:45
  • yes, that is mysql, PK all on 1 line my friend. Your way give it the flexibility of giving it a custom name. Either way. – Drew Nov 30 '15 at 20:47
  • So, the wayI did my primary keys originally will not work in MySQL? I am reading some conflicting documentation – Pete Nov 30 '15 at 20:49
  • I never do it your way. Whatever gets it in force – Drew Nov 30 '15 at 20:52
  • when you are done, issue a `show create table STUDENT`. The results will be obvious. – Drew Nov 30 '15 at 20:53
  • What is the' drop' and $$ delimiter at the top and bottom about? Would I need to do this trigger on every table containing the StudentID? – Pete Nov 30 '15 at 21:11
  • as for delimiters, see this I wrote (at the bottom), in [This Link](http://stackoverflow.com/a/33905535) – Drew Nov 30 '15 at 21:22
  • as for the `drop`, you can't create that which already exists. Just like a table, if you want to redefine it. As for a table, you `alter table`. Assuming you want your pre-existing data! But for stored procs, events, triggers, that is code. So you drop them, then re-create – Drew Nov 30 '15 at 21:24
  • As for do you need to do this for every table? The trigger would need to be created for any table that you say the GUI can't do it for you. Frankly, I would do it in the GUI. That said, the db enforcement sure makes it iron clad. – Drew Nov 30 '15 at 21:25
  • We have to do it in the code as part of the spec. This is a nightmare. The delimiter makes a little more sense now. Why do you need the ; at all inside of the $$ delimiter? Is the indentation important or is it just to make it more readable? – Pete Nov 30 '15 at 21:32
  • From that link: `Then re-set it back to a ; at the end. ` Because that is the delimiter that mysql is used to (that which ends a command) – Drew Nov 30 '15 at 21:34
  • I mean the one at the end of this line - INSERT INTO tbl_seq (thatId,thing,theWhen) VALUES (NEW.id,'frog',now()); – Pete Nov 30 '15 at 21:36
  • Are the apostophes neccesary for the trigger name? I didn't use them for my table names – Pete Nov 30 '15 at 21:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96602/discussion-between-drew-and-pete). – Drew Nov 30 '15 at 21:43
  • There I answered those in that chat link – Drew Nov 30 '15 at 21:47
  • I need to head out. Please vote if you found this helpful. I have a few people to help. Over and out – Drew Nov 30 '15 at 21:47
  • Thank you. Sorry for all the questions. What does this line mean? SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; – Pete Nov 30 '15 at 21:52
0

It is best to have Foreign Keys fields to be the same datatype as the field that they refer to. I know that there is some leeway for how different the datatypes can be (e.g. signed int can refer to unsigned int), but I'm not sure where that line is.

That being said. If the STUDENT.StudentID is limited to 8 digits, then so will all the FK fields because of the nature of FK constraints.

Dan
  • 10,614
  • 5
  • 24
  • 35