SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    URGENT: How can one add a logic to a MySQL Table to allow for example an INSERT IF X

    Hello,

    Is it possible that one can add logic to a MySQL Table so that MySQL Table only allows insertion if certain conditions are met.
    Say we have fields X1, X2, X3, X4

    Can one then add logic to this Table such as:

    Only allow INSERT IF X1 or X2 or X3 or X4 are NOT NULL.
    Otherwise reject the INSERT.

    Can such a logic be added to a MySQL Table and how?

    FYI, we need to URGENTLY do this since someone is hacked one of our main Table and is
    able to add data to it and I have blocked all such INSERT via the Php pages that we use
    to to make such an INSERT, so that is want I want to add this logic on MySQL Table
    level if possible.

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    declare the columns as NOT NULL

    oh, wait... did you mean you want at least one of the values to be not null? or all of them?

    if only one, then this logic needs to be implemented with php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    If there had to be a value, then set the fields to not accept null values.

    ALTER TABLE t1 CHANGE x1 VARCHAR(20) NOT NULL;

    Edit:

    dagnabbit - rudified again.....tha't what I get for ensuring the mySQL syntax didn't change.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ayayayay!
    Yes, I need to add a IF THEN to a MySQL Table, so that for example action INSERT is only allowed IF certain conditions are met.
    And for reason listed I was really hoping that such logic could be added on the Table level.

    Man this is really a sorely needed feature for MySQL, that is for MySQL developed to be able to add Table level logic which would
    then give the Table iron clad protection from a bad Php programmer, or hacker, from messing up with the Table.

    Anoox search engine volunteer

    www.anoox.com

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No of course the solution is not as simple as NOT NULL.
    That was just an example, and in that example the fields could be NULL individually, but NOT all of them at the same time.


    Quote Originally Posted by DaveMaxwell View Post
    If there had to be a value, then set the fields to not accept null values.
    ALTER TABLE t1 CHANGE x1 VARCHAR(20) NOT NULL;

    Edit:

    dagnabbit - rudified again.....tha't what I get for ensuring the mySQL syntax didn't change.

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    That was just an example
    so were our answers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    by the way, what you're looking for is the CHECK constraint

    all the complexity you need...

    except it's not implemented in mysql... yet... that i know of
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What is "CHECK constraint"?
    And how is that going to help us in this situation if it is not "implemented in mysql"!

    Anoox search engine volunteer

    www.anoox.com

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    A check constraint does what it says - it checks a condition (constraint) is met and if it is, the insert/update is carried out.
    (requiring a foreign key to have a corresponding record in another table is a constraint, but that depends on which engine you are running with MySQL)

    You could of course write a trigger to do the work for you.
    A trigger is a bit of code associated with a table that can be run before or after an insert, update or delete. you can write all sorts of constraints to be checked that way (no more than five people called john in the table for example!)

    Google will help you learn about triggers in mysql.
    Or read the online MySQL manual

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    And how is that going to help us in this situation if it is not "implemented in mysql"!
    given the URGENT status of your problem, i would have to say close to zero

    however, you did voice the desire to "add this logic on MySQL Table level if possible"

    i thought, for your future reference, that you might want to learn about CHECK constraint because that's what it does

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    triggers, sounds like exactly what I am looking for. I am on it.

    ThanX.

    Quote Originally Posted by Dr John View Post
    A check constraint does what it says - it checks a condition (constraint) is met and if it is, the insert/update is carried out.
    (requiring a foreign key to have a corresponding record in another table is a constraint, but that depends on which engine you are running with MySQL)

    You could of course write a trigger to do the work for you.
    A trigger is a bit of code associated with a table that can be run before or after an insert, update or delete. you can write all sorts of constraints to be checked that way (no more than five people called john in the table for example!)

    Google will help you learn about triggers in mysql.
    Or read the online MySQL manual

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,637
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Why is your app so loosey-goosey that it is letting these UPDATEs through. Branching logic like this is best left above the data tier . . .


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •