From MySQL Documentation: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

&gt; Some database systems have deferred
&gt; checks, and `NO ACTION` is a deferred
&gt; check. In MySQL, foreign key
&gt; constraints are checked immediately,
&gt; so `NO ACTION` is the same as `RESTRICT`.



It is to comply with standard SQL syntax. Like the [manual][1] says: (emphasis mine)

&gt; NO ACTION: **A keyword from standard SQL**. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. **Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.**

[1]: http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

They are identical in MySQL.

In the SQL 2003 standard there are 5 different referential actions:

    CASCADE
    RESTRICT
    NO ACTION
    SET NULL
    SET DEFAULT

The difference between `NO ACTION` and `RESTRICT` is that according to the standard, `NO ACTION` is deferred while `RESTRICT` acts immediately.

Consider the following JPQL query:

    SELECT foo FROM Foo foo
    INNER JOIN FETCH foo.bar bar
    WHERE bar.baz = :baz

I&#39;m trying to translate this into a Criteria query. This is as far as I have gotten:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery&lt;Foo&gt; cq = cb.createQuery(Foo.class);
    Root&lt;Foo&gt; r = cq.from(Foo.class);
    Fetch&lt;Foo, Bar&gt; fetch = r.fetch(Foo_.bar, JoinType.INNER);
    Join&lt;Foo, Bar&gt; join = r.join(Foo_.bar, JoinType.INNER);
    cq.where(cb.equal(join.get(Bar_.baz), value);

The obvious problem here is that I am doing the same join twice, because `Fetch&lt;Foo, Bar&gt;` doesn&#39;t seem to have a method to get a `Path`.
Is there any way to avoid having to join twice? Or do I have to stick with good old JPQL with a query as simple as that?

How to properly express JPQL &quot;join fetch&quot; with &quot;where&quot; clause as JPA 2 CriteriaQuery?

I&#39;ve noticed a couple of oddities when dealing with named pipes (FIFOs) under various flavors of UNIX (Linux, FreeBSD and MacOS X) using Python.  The first, and perhaps most annoying is that attempts to open an empty/idle FIFO read-only will block (unless I use `os.O_NONBLOCK` with the lower level `os.open()` call).  However, if I open it for read/write then I get no blocking.

Examples:

    f = open(&#39;./myfifo&#39;, &#39;r&#39;)               # Blocks unless data is already in the pipe
    f = os.open(&#39;./myfifo&#39;, os.O_RDONLY)    # ditto

    # Contrast to:
    f = open(&#39;./myfifo&#39;, &#39;w+&#39;)                           # does NOT block
    f = os.open(&#39;./myfifo&#39;, os.O_RDWR)                   # ditto
    f = os.open(&#39;./myfifo&#39;, os.O_RDONLY|os.O_NONBLOCK)   # ditto


I&#39;m just curious why.  Why does the open call block rather than some subsequent read operation?

Also I&#39;ve noticed that a non-blocking file descriptor can exhibit to different behaviors in Python.  In the case where I use `os.open()` with the `os.O_NONBLOCK` for the initial opening operation then an `os.read()` seems to return an empty string if data isn&#39;t ready on the file descriptor.  However, if I use `fcntl.fcnt(f.fileno(), fcntl.F_SETFL, fcntl.GETFL | os.O_NONBLOCK)` then an `os.read` raises an exception (`errno.EWOULDBLOCK`)

Is there some other flag being set by the normal `open()` that&#39;s not set by my `os.open()` example?  How are they different and why?

Why does a read-only open of a named pipe block?

What&#39;s the difference in a MySQL FK between `RESTRICT` and `NO ACTION`? From the doc they seem exactly the same. Is this the case? If so, why have both?

MySQL RESTRICT and NO ACTION

<p>What's the difference in a MySQL FK between <code>RESTRICT</code> and <code>NO ACTION</code>? From the doc they seem exactly the same. Is this the case? If so, why have both?</p>


I have a table A and there is one primary key ID.

Now I want to go through all rows in A.

I found something like &#39;for each record in A&#39;, but this seems to be not how you do it in MySQL.

Thing is for each row I want to take a field and transform it, insert it into another table and then update some of the row&#39;s fields. I can put the select part and the insert into one statement, but I don&#39;t know how to get the update in there as well. So I want to loop. And for practice I don&#39;t want to use anything else than MySQL.

**edit**

I would appreciate an example.

And a solution which does not need to be put into a procedure.

**edit 2**

okay think of this scenario:

Table A and B, each with fields ID and VAL.

Now this is the pseudo code for what I want to do:

    for(each row in A as rowA)
    {
      insert into B(ID, VAL) values(rowA[ID], rowA[VAL]);
    }

basically copying content of A into B using a loop.

(this is just a simplified example, of course you wouldn&#39;t use a loop for this.)
}

How can I loop through all rows of a table? (MySQL)

I&#39;ve just started getting into Node.js. I come from a PHP background, so I&#39;m fairly used to using MySQL for all my database needs.

How can I use MySQL with Node.js?



  [1]: https://github.com/Guille/node.dbslayer.js

MySQL with Node.js

I have two columns in table users namely `registerDate and lastVisitDate` which consist of datetime data type. I would like to do the following.

 1. Set registerDate defaults value to MySQL NOW()
 2. Set lastVisitDate default value to `0000-00-00 00:00:00` Instead of null which it uses by default.

Because the table already exists and has existing records, I would like to use Modify table.  I&#39;ve tried using the two piece of code below, but neither works.  

    ALTER TABLE users MODIFY registerDate datetime DEFAULT NOW()
    ALTER TABLE users MODIFY registerDate datetime DEFAULT CURRENT_TIMESTAMP;

It gives me Error : `ERROR 1067 (42000): Invalid default value for &#39;registerDate&#39;`

Is it possible for me to set the default datetime value to NOW() in MySQL?

Set NOW() as Default Value for datetime datatype?

I have the following MySql query:

    select t1.*
    from Table1 t1
    inner join Table2 t2
    on t1.CommonID = t2.CommonID
    where t1.FilterID = 1

It takes about 30 seconds to run, which was strange, because if I comment out the join or the where clause it takes less than a second:
i.e.

    select t1.*
    from Table1 t1
    where t1.FilterID = 1

or

    select t1.*
    from Table1 t1
    inner join Table2 t2
    on t1.CommonID = t2.CommonID

each take less than a second.

Then there is the STRAIGHT_JOIN keyword, which I can find one reference of, here:
[http://dev.mysql.com/doc/refman/5.0/en/join.html][1]

&gt; STRAIGHT_JOIN is similar to JOIN,
&gt; except that the left table is always
&gt; read before the right table. This can
&gt; be used for those (few) cases for
&gt; which the join optimizer puts the
&gt; tables in the wrong order.

What?
I can write:

    select t1.*
    from Table1 t1
    STRAIGHT_JOIN  Table2 t2
    on t1.CommonID = t2.CommonID
    where t1.FilterID = 1

and the query executes in less than a second.

Even stranger, I can write:

    select STRAIGHT_JOIN  t1.*
    from Table1 t1
    inner join Table2 t2
    on t1.CommonID = t2.CommonID
    where t1.FilterID = 1

and it takes less than a second, and this syntax does not appear to even be legal.

I would guess the second example means that a STRAIGHT_JOIN will be used whenever an INNER JOIN is written, but I can’t find any documentation about it.

What is going on here, and how can the “join optimizer” result in such relatively poor performance?  Should I always use STRAIGHT_JOIN?  How can I tell when to use it or not?

Table1 and Table2 both have integer primary keys; FilterID is a foreign key to another table; the CommonID columns are both foreign keys to a third table.  They both have indexes on them.  The database engine is InnoDB.

Thanks


  [1]: http://dev.mysql.com/doc/refman/5.0/en/join.html

Why does STRAIGHT_JOIN so drastically improve this query, and what does it mean when it is written after the SELECT keyword?

Here is a gross oversimplification of an intense setup I am working with. `table_1` and `table_2` both have auto-increment surrogate primary keys as the ID. `info` is a table that contains information about both `table_1` and `table_2`.

    table_1 (id, field)  
    table_2 (id, field, field)
    info ( ???, field)



I am trying to decided if I should make the primary key of `info` a composite of the IDs from `table_1` and `table_2`. If I were to do this, which of these makes most sense?    
*( in this example I am combining ID 11209 with ID 437 )* 

`INT(9)` 11209437   *(i can imagine why this is bad)*  
`VARCHAR (10)`    11209-437   
`DECIMAL (10,4)` 11209.437   

Or something else? 

Would this be fine to use this as the Primary Key on a MYSQL MYISAM DB?


How to properly create composite primary keys - MYSQL

Before I dive really deep into MongoDB for days, I thought I&#39;d ask a pretty basic question as to whether I should dive into it at all or not. I have basically no experience with nosql.

I did read a little about some of the benefits of document databases, and I think for this new application, they will be really great. It is always a hassle to do favourites, comments, etc. for many types of objects (lots of m-to-m relationships) and subclasses - it&#39;s kind of a pain to deal with.

I also have a structure that will be a pain to define in SQL because it&#39;s extremely nested and translates to a document a lot better than 15 different tables.

But I am confused about a few things. 

1. Is it desirable to keep your database normalized still? I really don&#39;t want to be updating multiple records. Is that still how people approach the design of the database in MongoDB?

2. What happens when a user favourites a book and this selection is still stored in a user document, but then the book is deleted? How does the relationship get detached without foreign keys? Am I manually responsible for deleting all of the links myself?

3. What happens if a user favourited a book that no longer exists and I query it (some kind of join)? Do I have to do any fault-tolerance here?

MongoDB normalization, foreign key and joining

I have a model `Coupon`, and a model `Photo` with a `ForeignKey` to it:

    class Photo(models.Model):
        coupon = models.ForeignKey(Coupon,
                                   related_name=&#39;description_photos&#39;)
        title = models.CharField(max_length=100)
        image = models.ImageField(upload_to=&#39;images&#39;)

I set up inlines in the admin so now I&#39;m able to add photos to a coupon from the admin.

I attempt to add one, and upload is successful, but then I get Django&#39;s debug page with this error:

    IntegrityError at /admin/coupon/coupon/321/
    (1452, &#39;Cannot add or update a child row: a foreign key constraint fails (`my_project`.`coupon_photo`, CONSTRAINT `coupon_id_refs_id_90d7f06` FOREIGN KEY (`coupon_id`) REFERENCES `coupon_coupon` (`id`))&#39;)

What is this and how can I solve this problem?

(If it matters, this is a MySQL database.)

**EDIT:** I tried it on an Sqlite3 database that has a slightly different dataset, and it worked, so perhaps there&#39;s loose data in my current DB? How can I find it and delete it?

Django &quot;Cannot add or update a child row: a foreign key constraint fails&quot;

![enter image description here][1]


  [1]: http://i.stack.imgur.com/6ejPD.png

How do I design a scheme such this in MongoDB? I think there are no foreign keys!

Foreign keys in mongo?

I&#39;m designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them :P

Anyways, I&#39;ve decided to use InnoDB because of it&#39;s Foreign Key support. One thing I noticed though is that it allows you to set On Update and on Delete options for foreign keys. Can someone explain where &quot;Restrict&quot;, &quot;Cascade&quot; and set null could be used in a simple example?

For example, say I have a `user` table which includes a `userID`. And say I have a message table `message` which is a many-to-many which has 2 foreign keys (which reference the same primary key, `userID` in the `user` table). Is setting the On Update and On Delete options any useful in this case? If so, which one do I choose? If this isn&#39;t a good example, could you please come up with a good example to illustrate how these could be useful?

Thanks

Foreign key constraints: When to use ON UPDATE and ON DELETE

Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

I have been experimenting with face detection in OpenCV (Open Source Computer Vision Library), and found that one could use Haar cascades to detect faces as there are several of them provided with OpenCV. However, I have noticed that there are also several LBP cascades. After doing some research, I found that LBP stands for Local Binary Patterns, and it can also be used for face detection, according to the [OpenCV Face Detection Documentation][1]. 

What I would like to know is, which works better? Which one performs faster, and which one is more accurate? It seems that LBP performs faster, but I&#39;m not 100% sure about that either. Thanks.

  [1]: http://opencv.itseez.com/doc/tutorials/objdetect/cascade_classifier/cascade_classifier.html

Haar Cascades vs. LBP Cascades in Face Detection

In PostgreSQL 8 is it possible to add `ON DELETE CASCADES` to the both foreign keys in the following table without dropping the latter?

    # \d scores
            Table &quot;public.scores&quot;
     Column  |         Type          | Modifiers
    ---------+-----------------------+-----------
     id      | character varying(32) |
     gid     | integer               |
     money   | integer               | not null
     quit    | boolean               |
     last_ip | inet                  |
    Foreign-key constraints:
       &quot;scores_gid_fkey&quot; FOREIGN KEY (gid) REFERENCES games(gid)
       &quot;scores_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)

Both referenced tables are below - here:

    # \d games
                                         Table &quot;public.games&quot;
      Column  |            Type             |                        Modifiers
    ----------+-----------------------------+----------------------------------------------------------
     gid      | integer                     | not null default nextval(&#39;games_gid_seq&#39;::regclass)
     rounds   | integer                     | not null
     finished | timestamp without time zone | default now()
    Indexes:
        &quot;games_pkey&quot; PRIMARY KEY, btree (gid)
    Referenced by:
        TABLE &quot;scores&quot; CONSTRAINT &quot;scores_gid_fkey&quot; FOREIGN KEY (gid) REFERENCES games(gid)

And here:

    # \d users
                    Table &quot;public.users&quot;
       Column   |            Type             |   Modifiers
    ------------+-----------------------------+---------------
     id         | character varying(32)       | not null
     first_name | character varying(64)       |
     last_name  | character varying(64)       |
     female     | boolean                     |
     avatar     | character varying(128)      |
     city       | character varying(64)       |
     login      | timestamp without time zone | default now()
     last_ip    | inet                        |
     logout     | timestamp without time zone |
     vip        | timestamp without time zone |
     mail       | character varying(254)      |
    Indexes:
        &quot;users_pkey&quot; PRIMARY KEY, btree (id)
    Referenced by:
        TABLE &quot;cards&quot; CONSTRAINT &quot;cards_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;catch&quot; CONSTRAINT &quot;catch_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;chat&quot; CONSTRAINT &quot;chat_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;game&quot; CONSTRAINT &quot;game_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;hand&quot; CONSTRAINT &quot;hand_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;luck&quot; CONSTRAINT &quot;luck_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;match&quot; CONSTRAINT &quot;match_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;misere&quot; CONSTRAINT &quot;misere_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;money&quot; CONSTRAINT &quot;money_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;pass&quot; CONSTRAINT &quot;pass_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;payment&quot; CONSTRAINT &quot;payment_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;rep&quot; CONSTRAINT &quot;rep_author_fkey&quot; FOREIGN KEY (author) REFERENCES users(id)
        TABLE &quot;rep&quot; CONSTRAINT &quot;rep_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;scores&quot; CONSTRAINT &quot;scores_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)
        TABLE &quot;status&quot; CONSTRAINT &quot;status_id_fkey&quot; FOREIGN KEY (id) REFERENCES users(id)

And also I wonder if it makes sense to add 2 index&#39;es to the former table?

**UPDATE:** Thank you, and also I&#39;ve got the advice at the mailing list, that I could manage it in 1 statement and thus without explicitly starting a transaction:

    ALTER TABLE public.scores
    DROP CONSTRAINT scores_gid_fkey,
    ADD CONSTRAINT scores_gid_fkey
       FOREIGN KEY (gid)
       REFERENCES games(gid)
       ON DELETE CASCADE;


How to add &quot;on delete cascade&quot; constraints?

I think I misunderstood the meaning of cascading in the context of a `@ManyToOne` relationship.

The case:

    public class User {
    
       @OneToMany(fetch = FetchType.EAGER)
       protected Set&lt;Address&gt; userAddresses;
    
    }
    
    public class Address {
    
       @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
       protected User addressOwner;
    
    }

What is the meaning of the `cascade = CascadeType.ALL`? For example, if I delete a certain address from the database, how does the fact that I added the `cascade = CascadeType.ALL` affect my data (the `User`, I guess)?

What is the meaning of the CascadeType.ALL for a @ManyToOne JPA association

If I have two relations in a database, like this:

    CREATE TABLE Courses (
      CourseID int NOT NULL PRIMARY KEY,
      Course VARCHAR(63) NOT NULL UNIQUE,
      Code CHAR(4) NOT NULL UNIQUE
    );

    CREATE TABLE BookCourses (
      EntryID int NOT NULL PRIMARY KEY,
      BookID int NOT NULL,
      Course CHAR(4) NOT NULL,
      CourseNum CHAR(3) NOT NULL,
      CourseSec CHAR(1) NOT NULL
    );

and I establish a foreign key relationship between the two, like this:

    ALTER TABLE BookCourses
    ADD FOREIGN KEY (Course)
    REFERENCES Courses(Code)
    ON DELETE CASCADE;

Then you can see that the `Course` attribute in the `BookCourses` relation references the `Code` attribute in the `Courses` relation.

My question is when a deletion occurs in either of the two relations, which way does the deletion cascade? If I delete a tuple in the `Courses` relation, will it delete all referencing tuples in the `BookCourses` relation, or is it the other way around?

SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

I have two tables like here:

    DROP   TABLE  IF EXISTS schemas.book;
    DROP   TABLE  IF EXISTS schemas.category;
    DROP   SCHEMA IF EXISTS schemas;
    CREATE SCHEMA schemas;
    
    CREATE TABLE schemas.category (
      id          BIGSERIAL PRIMARY KEY,
      name        VARCHAR   NOT NULL,
      UNIQUE(name)
    );
    
    CREATE TABLE schemas.book (
      id          BIGSERIAL PRIMARY KEY,
      published   DATE      NOT NULL,
      category_id BIGINT    NOT NULL REFERENCES schemas.category
                                ON DELETE CASCADE 
                                ON UPDATE CASCADE,
      author      VARCHAR   NOT NULL,
      name        VARCHAR   NOT NULL,
      UNIQUE(published, author, name),
      FOREIGN KEY(category_id) REFERENCES schemas.category (id)
    );

So the logic is simple, after user removes all book under category x, x gets removed from cats, i tried method above but doesn&#39;t work, after i clean table book, table category still populated, what&#39;s wrong?

Content Type	Original Author	Original Content on Stackoverflow
Question	Erebus	View Question on Stackoverflow
Solution 1 - Mysql	Anthony Accioly	View Answer on Stackoverflow
Solution 2 - Mysql	Nanne	View Answer on Stackoverflow
Solution 3 - Mysql	Wolph	View Answer on Stackoverflow

MySQL RESTRICT and NO ACTION

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Why does a read-only open of a named pipe block?

How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery?

Attributions