[Foreign keys][1] enforce [referential integrity][2]. These constraints guarantee that a row in a table `order_details` with a field `order_id` referencing an `orders` table will never have an `order_id` value that doesn&#39;t exist in the `orders` table. 

Foreign keys aren&#39;t required to have a working relational database (in fact [MySQL&#39;s default storage][4] engine doesn&#39;t support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the *C* in [ACID][5] to stand.

As for your concerns regarding performance, in general there&#39;s a performance cost, but will probably be negligible. I suggest putting in all your foreign key constraints, and only experiment without them if you have real performance issues that you cannot solve otherwise.


  [1]: http://en.wikipedia.org/wiki/Foreign_key
  [2]: http://en.wikipedia.org/wiki/Referential_integrity
  [3]: http://en.wikipedia.org/wiki/Sql_join
  [4]: http://en.wikipedia.org/wiki/MyISAM
  [5]: http://en.wikipedia.org/wiki/ACID


One reason is that a set of tables with foreign key constraints cannot be sharded into multiple databases.

I&#39;m trying to get an image from an url using a `byte` stream. But i get this error message:

&gt; This stream does not support seek operations.

This is my code:

    byte[] b;
    HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create(url);
    WebResponse myResp = myReq.GetResponse();

    Stream stream = myResp.GetResponseStream();
    int i;
    using (BinaryReader br = new BinaryReader(stream))
    {
        i = (int)(stream.Length);
        b = br.ReadBytes(i); // (500000);
    }
    myResp.Close();
    return b;

What am i doing wrong guys?

Error &quot;This stream does not support seek operations&quot; in C#

How can I set the charset with JAX-RS? I&#39;ve tried `@Produces(&quot;text/html; charset=UTF-8&quot;)` but that was ignored and only `text/html` was send with the HTTP header. I want to set the charset within a MessageBodyWriter, but don&#39;t want to extract the media type by analysing the @Produces annotation via reflection by myself.

How to set the charset with JAX-RS?

I was wondering,

What will be my motivation to use constraint as foreign key in MySQL, as I am sure that I can rule the types that are added?

Does it improve performance?

Why use Foreign Key constraints in MySQL?

I was wondering,
What will be my motivation to use constraint as foreign key in MySQL, as I am sure that I can rule the types that are added?
Does it improve performance?

A) 
   
    select decode(count(*), 0, &#39;N&#39;, &#39;Y&#39;) rec_exists
    from (select &#39;X&#39;
          from dual
          where exists (select &#39;X&#39;
                        from sales
                        where sales_type = &#39;Accessories&#39;));

B)

    select decode(count(*), 0, &#39;N&#39;, &#39;Y&#39;) rec_exists
    from (select &#39;X&#39;
          from sales
          where sales_type = &#39;Accessories&#39;); 

C) Something else (specify)

EDIT: It was hard to pick the &quot;correct&quot; answer, as the best approach depends on what you want to do after checking if the value exists, as pointed out by APC. I ended up picking the answer by RedFilter, since I had originally envisioned this check as a function by itself.

What&#39;s the most efficient way to check if a record exists in Oracle?

I am looking to replace values in a particular column. For example the following column values

    column name
    ----------
    Test1
    Test2
    Test3
    Test12

should be (replacing `est1` with `rest1`)

    column name
    ----------
    Trest1
    Test2
    Test3
    Trest12


How to replace specific values in a oracle database column?

I&#39;m reading through some old code at work, and have noticed that there are several views with an `order by 1` clause.  What does this accomplish? 

Example:

    Create view v_payment_summary AS
    SELECT A.PAYMENT_DATE,
           (SELECT SUM(paymentamount)
              FROM payment B
             WHERE PAYMENT_DATE = B.PAYMENT_DATE
               and SOME CONDITION) AS SUM_X,
           (SELECT SUM(paymentamount)
              FROM payment B
             WHERE PAYMENT_DATE = B.PAYMENT_DATE
               and SOME OTHER CONDITION) AS SUM_Y    
    FROM payment A    
    ORDER BY 1;



What is the purpose of Order By 1 in SQL select statement?

How do I do the following?

    select top 1 Fname from MyTbl

In [Oracle 11g][1]?

  [1]: https://en.wikipedia.org/wiki/Oracle_Database#Version_numbering


How do I do top 1 in Oracle?

Does anyone know how can I do a count in SQL Server based on condition.

Example:

How can I do a column count for records with name &#39;system&#39;, and total CaseID records in the table?

Customer table

    UserID     CaseID     Name
    1          100        alan
    1          101        alan
    1          102        amy
    1          103        system
    1          104        ken
    1          105        ken
    1          106        system  

The result will display like below:

    UserID    TotalCaseID    TotalRecordsWithSystem
    1         7              2


Count based on condition in SQL Server

what should be the ideal size for storing IPv4, IPv6 addresses as a string in the MySQL database. should varchar(32) be sufficient?

Size for storing IPv4, IPv6 addresses as a string

I am using MySQL in localhost as a &quot;query tool&quot; for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

It&#39;s working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

How to shrink/purge ibdata1 file in MySQL

When the SQL query below is executed:

    UPDATE shop_category 
    SET name = &#39;Secolul XVI - XVIII&#39; 
        AND name_eng = &#39;16th to 18th centuries&#39; 
    WHERE category_id = 4768

The following error is raised:

    1292 - Truncated incorrect DOUBLE value: &#39;Secolul XVI - XVIII&#39;

How to fix this?

---

`shop_category` table structure:

    category_id   mediumint(8)
    name        varchar(250)
    name_eng      varchar(250)

MYSQL Truncated incorrect DOUBLE value

If you try to create a TEXT column on a table, and give it a default value in MySQL, you get an error (on Windows at least). I cannot see any reason why a text column should not have a default value. No explanation is given by the MySQL documentation. It seems illogical to me (and somewhat frustrating, as I want a default value!). Anybody know why this is not allowed?

Why can&#39;t a text column have a default value in MySQL?

I get this error

    `establish_connection&#39;: Please install the mysql2 adapter: `gem install activerecord-mysql2-adapter` (no such file to load -- active_record/connection_adapters/mysql2_adapter) (RuntimeError)
      from /opt/ruby-enterprise-1.8.7-2010.02/lib/ruby/gems/1.8/gems/activerecord-

[Here][1] is dump of whole error and my config and gemfile.

  [1]: http://pastie.org/1088017

RuntimeError with mysql2 and rails3 (bundler)

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

MySQL DROP all tables, ignoring foreign keys

Let&#39;s say my table structure looks something like this:

    CREATE TABLE [dbo].[table1] (
        [id] [int] IDENTITY(1,1) NOT NULL,
        [data] [varchar](255) NOT NULL,
        CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
    )
    
    CREATE TABLE [dbo].[table2] (
        [id] [int] IDENTITY(1,1) NOT NULL,
        [table1_id] [int] NOT NULL,
        [data] [varchar](255) NOT NULL,
        CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
    )

The `[id]` field of the first table corresponds to the `[table1_id]` field of the second. What I would like to do is insert data into both tables in a single transaction. Now I already know how to do this by doing INSERT-SELECT-INSERT, like this:

    BEGIN TRANSACTION;
    DECLARE @id [int];
    INSERT INTO [table1] ([data]) VALUES (&#39;row 1&#39;);
    SELECT @id = SCOPE_IDENTITY();
    INSERT INTO [table2] ([table1_id], [data]) VALUES (@id, &#39;more of row 1&#39;);
    COMMIT TRANSACTION;

That&#39;s all good and fine for small cases like that where you&#39;re only inserting maybe a handful of rows. But what I need to do is insert a couple hundred thousand rows, or possibly even a million rows, all at once. The data is coming from another table, so if I was only inserting it into a single table, it would be easy, I&#39;d just have to do this:

    INSERT INTO [table] ([data])
    SELECT [data] FROM [external_table];

But how would I do this and split the data into `[table1]` and `[table2]`, and still update `[table2]` with the appropriate `[table1_id]` as I&#39;m doing it? Is that even possible?

How can I INSERT data into two tables simultaneously in SQL Server?

I would like to know if it&#39;s possible in InnoDB in `MySQL` to have a `table` with **foreign key** that references another table in a different `database` ? 

And if so, how this can be done ?

MySQL InnoDB foreign key between different databases

I have tables that I&#39;ve tried setting PK FK relationships on but I want to verify this. How can I show the PK/FK restraints? I saw [this][1] manual page, but it does not show examples and my google search was fruitless also. My database is `credentialing1` and my constrained tables are `practices` and `cred_insurances`.


  [1]: http://dev.mysql.com/doc/refman/5.5/en/table-constraints-table.html

Show constraints on tables command

I&#39;m doing some maintenance work on a database application and I&#39;ve discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there&#39;s no foreign key constraints on the tables.

I&#39;m trying to add FK constraints on these columns, but I&#39;m finding that, because there&#39;s already a whole load of bad data in the tables from previous errors which have been naively corrected, I need to find the rows which don&#39;t match up to the other table and then delete them.

I&#39;ve found some examples of this kind of query on the web, but they all seem to provide examples rather than explanations, and I don&#39;t understand why they work.

Can someone explain to me how to construct a query which returns all the rows with no matches in another table, and what it&#39;s doing, so that I can make these queries myself, rather than coming running to SO for every table in this *mess* that has no FK constraints?

Content Type	Original Author	Original Content on Stackoverflow
Question	Dejell	View Question on Stackoverflow
Solution 1 - Sql	Daniel Vassallo	View Answer on Stackoverflow
Solution 2 - Sql	Ben Wheeler	View Answer on Stackoverflow

Why use Foreign Key constraints in MySQL?

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

How to set the charset with JAX-RS?

Error "This stream does not support seek operations" in C#

Attributions