No problem doing this. Schemas really have no impact when establishing foreign key relationships between tables. Just make sure the appropriate people have the permissions necessary for the schemas you intend to use.

If you&#39;re in an organization where different people have authority over different schemas, I think it&#39;s good practice to give the other schema the ability to disable, or even drop and recreate, your constraint. 

For example, they could need to drop or truncate their table and then reload it to handle some (very weird) support issue. Unless you want to get called in the middle of the night, I recommend giving them the ability to temporarily remove your constraint. (I also recommend setting your own alerts so that you&#39;ll know if any of your external constraints get disabled or dropped). When you&#39;re crossing organizational/schema lines, you want to play well with others. The index that Vincent mentioned is another part of that. 

This will work exactly as a foreign key that references a table in its own schema.

As with regular foreign keys, don&#39;t forget to index `my_id` if the parent key is ever updated or if you delete entries from the parent table (unindexed foreign keys can be a source of massive contention and the index is usually useful anyway).

The only thing I ran into was making sure the permission existed on the other schema.  The usual stuff - if those permission(s) disappear for whatever reason, you&#39;ll hear about it.

One reason this can cause problems is you need to be careful to delete things in the right order.  This can be good or bad depending on how important it is to never have orphans in your tables.

I am writing my first Android database backend and I&#39;m struggling to unit test the creation of my database.

Currently the problem I am encountering is obtaining a valid Context object to pass to my implementation of SQLiteOpenHelper.  Is there a way to get a Context object in a class extending TestCase?  The solution I have thought of is to instantiate an Activity in the setup method of my TestCase and then assigning the Context of that Activity to a field variable which my test methods can access...but it seems like there should be an easier way.

Android Unit Tests Requiring Context

Say `my_instance` is of model `MyModel`.  
I&#39;m looking for a good way to do:

    my_model = get_model_for_instance(my_instance)

I have not found any really direct way to do this.
So far I have come up with this:

    from django.db.models import get_model
    
    my_model = get_model(my_instance._meta.app_label, my_instance.__class__.__name__)

Is this acceptable? Is it even a sure-fire, best practice way to do it?  
There is also `_meta.object_name` which seems to deliver the same as `__class__.__name__`. Does it? Is better or worse? If so, why?

Also, how do I know I&#39;m getting the correct model if the app label occurs multiple times within the scope of the project, e.g. &#39;auth&#39; from &#39;django.contrib.auth&#39; and let there also be &#39;myproject.auth&#39;?  
Would such a case make `get_model` unreliable?

Thanks for any hints/pointers and sharing of experience!

django: best practice way to get model from an instance of that model

I tried to create a foreign key on one of my tables, referencing a column of a table in a different schema.

Something like that:

    ALTER TABLE my_schema.my_table ADD (
      CONSTRAINT my_fk
        FOREIGN KEY (my_id)
        REFERENCES other_schema.other_table(other_id)
    )

Since I had the necessary grants, this worked fine.

Now I wonder if there are reasons for not referencing tables in a different schema, or anything to be careful about?

Foreign key reference to table in another schema

I tried to create a foreign key on one of my tables, referencing a column of a table in a different schema.
Something like that:
<pre><code class="hljs language-sql">ALTER TABLE my_schema.my_table ADD (
 CONSTRAINT my_fk
 FOREIGN KEY (my_id)
 REFERENCES other_schema.other_table(other_id)
)
</code></pre>
Since I had the necessary grants, this worked fine.
Now I wonder if there are reasons for not referencing tables in a different schema, or anything to be careful about?

I have a constraint called `users.SYS_C00381400`.  How do I find what that constraint is?  Is there a way to query all constraints?

Oracle find a constraint

I have the following table schema which maps user_customers to permissions on a live MySQL database:

    mysql&gt; describe user_customer_permission;
    +------------------+---------+------+-----+---------+----------------+
    | Field            | Type    | Null | Key | Default | Extra          |
    +------------------+---------+------+-----+---------+----------------+
    | id               | int(11) | NO   | PRI | NULL    | auto_increment |
    | user_customer_id | int(11) | NO   | PRI | NULL    |                |
    | permission_id    | int(11) | NO   | PRI | NULL    |                |
    +------------------+---------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)

I would like to remove the primary keys for user_customer_id and permission_id and retain the primary key for id.

When I run the command:

    alter table user_customer_permission drop primary key;

I get the following error:

    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

How can I drop a column&#39;s primary key?

Remove Primary Key in MySQL

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one `SELECT` statement. What is the best practice? Any advice on building indexes?

Please use these table/column names in your answer:

 - customer: `id`, `name`
 - purchase: `id`, `customer_id`, `item_id`, `date`

And in more complicated situations, would it be (performance-wise) beneficial to denormalize the database by putting the last purchase into the customer table?

If the (purchase) `id` is guaranteed to be sorted by date, can the statements be simplified by using something like `LIMIT 1`?

SQL join: selecting the last records in a one-to-many relationship

I have a SQL Server database of organizations, and there are many duplicate rows. I want to run a select statement to grab all of these and the amount of dupes, but also return the ids that are associated with each organization.

A statement like:  

    SELECT     orgName, COUNT(*) AS dupes  
    FROM         organizations  
    GROUP BY orgName  
    HAVING      (COUNT(*) &gt; 1)

Will return something like

    orgName        | dupes  
    ABC Corp       | 7  
    Foo Federation | 5  
    Widget Company | 2 

But I&#39;d also like to grab the IDs of them. Is there any way to do this? Maybe like a 

    orgName        | dupeCount | id  
    ABC Corp       | 1         | 34  
    ABC Corp       | 2         | 5  
    ...  
    Widget Company | 1         | 10  
    Widget Company | 2         | 2  

The reason being that there is also a separate table of users that link to these organizations, and I would like to unify them (therefore remove dupes so the users link to the same organization instead of dupe orgs). But I would like part manually so I don&#39;t screw anything up, but I would still need a statement returning the IDs of all the dupe orgs so I can go through the list of users.

Finding duplicate rows in SQL Server

From my understanding you don&#39;t gain much by setting an index in a column that will hold few distinct values. 

I have a column that holds a boolean value (actually it&#39;s a small int, but I&#39;m using it as a flag), and this column is used in the `WHERE` clauses of most of the queries I have. 
In a theoretical &quot;average&quot; case, half of the records&#39; values will be 1 and the other half, 0. 

So, in this scenario, the database engine could avoid a full table scan, but will have to read a lot of rows anyway (total rows/2). 

So, should I make this column an index?

*I&#39;m using Mysql 5, but I&#39;m more interested in a general rationale on why it does / does not make sense indexing a column that I know that will have a low cardinality.*

Does it make sense to use an index that will have a low cardinality?

What does `:` stand for in a query?

    INSERT INTO MyTable (ID) VALUES (:myId)

How does it fetch the desired value?


**Edit:** Also what is that sign called? I wanted to search on google, but what&#39;s the name for `:`?


  [1]: http://stackoverflow.com/questions/2177978/whats-the-in-a-query/2181662#2181662

What does the colon sign &quot;:&quot; do in a SQL query?

How do i list all tables in a schema in Oracle SQL?

How do I list all tables in a schema in Oracle SQL?

I&#39;m trying to do a SELECT INTO using Oracle. My query is:

    SELECT * INTO new_table FROM old_table;

But I get the following error:

    SQL Error: ORA-00905: missing keyword
    00905. 00000 -  &quot;missing keyword&quot;

Any ideas what&#39;s wrong?

-----------------
The Standard behavior of the above should be as I originally thought:
However Oracle implemented it totally differently in their own dialect of SQL
[Oracle Docs on Insert ... Select][1] 


  [1]: http://docs.oracle.com/cd/E17952_01/refman-5.0-en/insert-select.html

SELECT INTO using Oracle

I just stumbled upon something in ORACLE SQL (not sure if it&#39;s in others), that I am curious about. I am asking here as a wiki, since it&#39;s hard to try to search symbols in google... 

I just found that when checking a value against a set of values you can do 

    WHERE x = ANY (a, b, c)

As opposed to the usual 

    WHERE x IN (a, b, c)

So I&#39;m curious, what is the reasoning for these two syntaxes? Is one **standard** and one some oddball Oracle syntax? Or are they both standard? And is there a preference of one over the other for performance reasons, or ? 

Just curious what anyone can tell me about that &#39;= ANY&#39; syntax. 
CheerZ!

Oracle: &#39;= ANY()&#39; vs. &#39;IN ()&#39;

I have a very large database with hundreds of tables, and after many, many product upgrades, I&#39;m sure half of them aren&#39;t being used anymore. How can I tell if a table is is actively being selected from? I can&#39;t just use Profiler - not only do I want to watch for more than a few days, but there are thousands of stored procedures as well, and profiler won&#39;t translate the SP calls into table access calls.

The only thing I can think of is to create a clustered index on the tables of interest, and then monitor the `sys.dm_db_index_usage_stats` to see if there are any seeks or scans on the clustered index, meaning that data from the table was loaded. However, adding a clustered index on every table is a bad idea (for any number of reasons), as isn&#39;t really feasible.

Are there other options I have? I&#39;ve always wanted a feature like a &quot;SELECT trigger&quot;, but there are probably other reasons why SQL Server doesn&#39;t have that feature either.

**SOLUTION:**

Thanks, Remus, for pointing me in the right direction. Using those columns, I&#39;ve created the following SELECT, which does exactly what I want.

      WITH LastActivity (ObjectID, LastAction) AS 
      (
           SELECT object_id AS TableName,
                  last_user_seek as LastAction
    	     FROM sys.dm_db_index_usage_stats u
            WHERE database_id = db_id(db_name())
            UNION 
           SELECT object_id AS TableName,
                  last_user_scan as LastAction
    	     FROM sys.dm_db_index_usage_stats u
            WHERE database_id = db_id(db_name())
            UNION
           SELECT object_id AS TableName,
                  last_user_lookup as LastAction
             FROM sys.dm_db_index_usage_stats u
            WHERE database_id = db_id(db_name())
      )
      SELECT OBJECT_NAME(so.object_id) AS TableName,
             MAX(la.LastAction) as LastSelect
        FROM sys.objects so
        LEFT
        JOIN LastActivity la
          on so.object_id = la.ObjectID
       WHERE so.type = &#39;U&#39;
         AND so.object_id &gt; 100
    GROUP BY OBJECT_NAME(so.object_id)
    ORDER BY OBJECT_NAME(so.object_id)

How can I tell if a database table is being accessed anymore? Want something like a &quot;SELECT trigger&quot;

I&#39;m trying to figure out the &quot;best practices&quot; for deciding whether or not to add an auto-incrementing integer as the primary key to a table.

Let&#39;s say I have a table containing data about the chemical elements. The atomic number of each element is unique and will never change.  So rather than using an auto-incrementing integer for each column, it would probably make more sense to just use the atomic number, correct?

Would the same be true if I had a table of books?  Should I use the ISBN or an auto-incrementing integer for the primary key?  Or a table of employees containing each person&#39;s SSN?

When to use an auto-incremented primary key and when not to?

Our software currently runs on MySQL. The data of all tenants is stored in the same schema. Since we are using Ruby on Rails we can easily determine which data belongs to which tenant. However there are some companies of course who fear that their data might be compromised, so we are evaluating other solutions.

So far I have seen three options:

* Multi-Database (each tenant gets its own - nearly the same as 1 server per customer)
* Multi-Schema (not available in MySQL, each tenant gets its own schema in a shared database)
* Shared Schema (our current approach, maybe with additional identifying record on each column)

Multi-Schema is my favourite (considering costs). However creating a new account and doing migrations seems to be quite painful, because I would have to iterate over all schemas and change their tables/columns/definitions.

**Q:** Multi-Schema seems to be designed to have slightly different tables for each tenant - I don&#39;t want this. Is there any RDBMS which allows me to use a multi-schema multi-tenant solution, where the table structure is shared between all tenants?

P.S. By multi I mean something like ultra-multi (10.000+ tenants).

How to create a multi-tenant database with shared table structures?

In developing a shopping cart application I&#39;ve found that I needed to save settings and configurations based on the administrator&#39;s preferences and requirements. This information can be anything from company information, Shipping account IDs, PayPal API keys, notification preferences, etc.

It seems highly inappropriate to create a table to store a single row in a relational database system.

What is the appropriate way to store this information?

Note: my DBMS is SQL Server 2008 and programming layer is implemented with ASP.NET (in C#).

Content Type	Original Author	Original Content on Stackoverflow
Question	Peter Lang	View Question on Stackoverflow
Solution 1 - Sql	Randy Minder	View Answer on Stackoverflow
Solution 2 - Sql	Jim Hudson	View Answer on Stackoverflow
Solution 3 - Sql	Vincent Malgrat	View Answer on Stackoverflow
Solution 4 - Sql	OMG Ponies	View Answer on Stackoverflow
Solution 5 - Sql	David Oneill	View Answer on Stackoverflow

Foreign key reference to table in another schema

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

django: best practice way to get model from an instance of that model

Android Unit Tests Requiring Context

Attributions