Yes, you can do this. See the [MySQL reference (version 5.5)][1].

&gt; A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE  index allows multiple NULL values for columns that can contain NULL. 

  [1]: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

Yes, if you make the product code column nullable (not declared with `NOT NULL`), the unique key will allow multiple rows with `NULL` product codes.

MySQL still allows for multiple rows to have a value of `NULL` in a unique column.

I&#39;m going to learn RESTful web services (it&#39;s better to say that I&#39;ll have to do this because it&#39;s a part of CS master degree program). 

I&#39;ve read some info in Wikipedia and I&#39;ve also read an article about REST at Sun Developer Network and I see that it&#39;s not easy technology, there are special frameworks for building RESTful apps, and it&#39;s often being compared to SOAP web services and programmer should understand when to use SOAP and when REST could be nice approach.

I remember that several years ago SOAP was very popular (fashionable?) and item &#39;SOAP&#39; had to be present in every good CV. But in practice it was used very rarely and for achieving very simple purposes.

It seems to me that REST is another &#39;last word of fashion&#39; (or I can be totally wrong because I haven&#39;t ever seen REST in practice).

Can you give me some examples were REST should be used and why we can&#39;t do the same without REST (or why we should spend much more time to do the same without REST)?  

**UPD**: Unfortunatelly I can&#39;t see any concrete arguments which can blow my mind in first comments. Make me think that REST is awesome technology!

I&#39;d like to see answers like this:

&gt; I was developing another complex
&gt; HelloWorld application and we need to 
&gt; transfer lots of / tiny data and I
&gt; proposed REST solution to my workmate:
&gt; 
&gt; &amp;ndash; Oh, damn! Jonny, we should
&gt; certainly use REST for implementing
&gt; this app!&lt;br/&gt; &amp;ndash; Yes, Billy, we
&gt; can use REST, but we would better use
&gt; SOAP. Trust me &#39;cause I know something
&gt; about developing HelloWorld
&gt; applications.&lt;br/&gt; &amp;ndash; But SOAP is
&gt; old-fashioned technology from the last
&gt; century and we can use better
&gt; one.&lt;br/&gt; &amp;ndash; Billy, are you ready
&gt; to spent 3 days for experimenting with
&gt; REST? We can do this with SOAP in 2
&gt; hours..&lt;br/&gt; &amp;ndash; Yes, I&#39;m sure
&gt; that we&#39;ll spent even more time to
&gt; achieve the same security/performance/
&gt; /scalability/whatever else with SOAP.
&gt; I&#39;m sure that HelloWorld applications
&gt; should be developed only with REST
&gt; from now.

Why do we need RESTful Web Services?

Is it possible to fire a mysql trigger for both the insert and update events of a table?

I know I can do the following

    CREATE TRIGGER my_trigger
        AFTER INSERT ON `table`
        FOR EACH ROW
    BEGIN
    .....
    END //

    CREATE TRIGGER my_trigger
        AFTER UPDATE ON `table`
        FOR EACH ROW
    BEGIN
    .....
    END //

But how can I do

    CREATE TRIGGER my_trigger
        AFTER INSERT ON `table` AND
        AFTER UPDATE ON `table`
        FOR EACH ROW
    BEGIN
    .....

Is it possible, or do I have to use 2 triggers? The code is the same for both and I don&#39;t want to repeat it.


MySQL Fire Trigger for both Insert and Update

I have a field that stores product codes. The codes are unique, but some products simply doesn&#39;t have a code. I can&#39;t invent codes because those are providers codes.

Is this kind of constraint possible in MySQL?

I&#39;m a noob with stored procedures and triggers, so if the solution involves one of these, please be patient.

Update:
The column is NOT Null. That&#39;s why I was unable to do this.

Unique constraint that allows empty values in MySQL

I have a field that stores product codes. The codes are unique, but some products simply doesn't have a code. I can't invent codes because those are providers codes.
Is this kind of constraint possible in MySQL?
I'm a noob with stored procedures and triggers, so if the solution involves one of these, please be patient.
Update:
The column is NOT Null. That's why I was unable to do this.

I have two tables with identical structure except for one column... Table 2 has an additional column in which I would insert the CURRENT_DATE()

I would like to copy all the values from table1 to table2.

If I use

    INSERT INTO dues_storage SELECT * FROM dues WHERE id=5;

it throws an error pointing to the difference in the number of columns. 

I have two questions:

1. How do I get around this?
2. How do I add the value for the additional date column (CURRENT_DATE()) in table2 within this same statement?

MYSQL: How to copy an entire row from one table to another in mysql with the second table having one extra column?

I want to change the data type of multiple columns from float to int.  What is the simplest way to do this?

There is no data to worry about, yet.

How do I change the data type for a column in MySQL?

I started by googling and found the article *[How to write INSERT if NOT EXISTS queries in standard SQL][1]* which talks about mutex tables.

I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (i.e., one query to check and one to insert is the result set is empty)?

Does a `unique` constraint on a field guarantee the `insert` will fail if it&#39;s already there?

It seems that with *merely* a constraint, when I issue the insert via PHP, the script croaks.

  [1]: http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/


How can I do &#39;insert if not exists&#39; in MySQL?

Performance question ...

I have a database of houses that have geolocation data (longitude &amp; latitude).

What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that I can perform a lot of queries where I&#39;m returning all the home records that are between x1 and x2 `latitude` and y1 and y2 `longitude`.

Right now, my database schema is

    ---------------------
    Homes   
    ---------------------
    geolat - Float (10,6)
    geolng - Float (10,6)
    ---------------------

And my query is:

    SELECT ... 
    WHERE geolat BETWEEN x1 AND x2
    AND geolng BETWEEN y1 AND y2

 - Is what I described above the best way to store the
   latitude and longitude data in MySQL using Float (10,6) and separating out the longitude/latitude? If not, what is? There exist Float, Decimal and even Spatial as a data type.
 - Is this the best way to perform the
   SQL from a performance standpoint? If not, what is?
 - Does using a different MySQL
   database-engine make sense?

**UPDATE: Still Unanswered**

I have 3 different answers below. One person say to use `Float`. One person says to use `INT`. One person says to use `Spatial`.

So I used MySQL &quot;EXPLAIN&quot; statement to measure the SQL execution speed. It appears that absolutely no difference in SQL execution (result set fetching) exist if using `INT` or `FLOAT` for the longitude and latitude data type..

It also appears that using the &quot;`BETWEEN`&quot; statement is SIGNIFICANTLY faster than using the &quot;`&gt;`&quot; or &quot;`&lt;`&quot; SQL statements. It&#39;s nearly 3x faster to use &quot;`BETWEEN`&quot; than to use the &quot;`&gt;`&quot; and &quot;`&lt;`&quot; statement.

With that being said, I still am unceratin on what the performance impact would be if using Spatial since it&#39;s unclear to me if it&#39;s supported with my version of MySQL running (v5.0.24) ... as well as how I enable it if supported.

**Any help would be greatly appreacited**



Database/SQL: How to store longitude/latitude data?

Is there a way to retrieve the auto generated key from a DB query when using a java query with prepared statements. 

For example, I know AutoGeneratedKeys can work as follows. 

    stmt = conn.createStatement();

    stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
    if(returnLastInsertId) {
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        auto_id = rs.getInt(1);
    } 

However. What if I want to do an insert with a prepared Statement. 

    String sql = &quot;INSERT INTO table (column1, column2) values(?, ?)&quot;;
    stmt = conn.prepareStatement(sql);
            
    //this is an error
    stmt.executeUpdate(Statement.RETURN_GENERATED_KEYS);
    if(returnLastInsertId) {
        //this is an error since the above is an error
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        auto_id = rs.getInt(1);
    } 

Is there a way to do this that I don&#39;t know about. It seems from the javadoc that PreparedStatements can&#39;t return the Auto Generated ID.

Is there a way to retrieve the autoincrement ID from a prepared statement

In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys.  There&#39;s basically two schools of thought in our group:

1:  

    Primary Table (Employee)   
    Primary Key is called ID
    
    Foreign table (Event)  
    Foreign key is called EmployeeID

or

2:  

    Primary Table (Employee)  
    Primary Key is called EmployeeID
    
    Foreign table (Event)  
    Foreign key is called EmployeeID

I prefer not to duplicate the name of the table in any of the columns (So I prefer option 1 above). Conceptually, it is consistent with a lot of the recommended practices in other languages, where you don&#39;t use the name of the object in its property names. I think that naming the foreign key `EmployeeID` (or `Employee_ID` might be better) tells the reader that it is the `ID` column of the `Employee` Table.  

Some others prefer option 2 where you name the primary key prefixed with the table name so that the column name is the same throughout the database.  I see that point, but you now can not visually distinguish a primary key from a foreign key.  

Also, I think it&#39;s redundant to have the table name in the column name, because if you think of the table as an entity and a column as a property or attribute of that entity, you think of it as the ID attribute of the `Employee`, not the `EmployeeID` attribute of an employee. I don&#39;t go an ask my coworker what his `PersonAge` or `PersonGender` is. I ask him what his Age is.

So like I said, it&#39;s a raging debate and we go on and on and on about it.  I&#39;m interested to get some new perspectives.

Primary key/foreign Key naming convention

After going thru some tutorials on SQL Server 2008&#39;s new feature &quot;SPARSE COLUMN&quot;, I have found that it doesn&#39;t take any space if the column value is 0 or NULL but when there is a value, it takes 4 times the space a regular(non sparse) column holds.

If my understanding is correct, then why I will go for that at the time of database design?
And if I use that, then at what situation will I be?

Also out of curiosity, how does no space get reserved when a column is defined as sparse column (I mean to say, what is the internal implementation for that?)


Why &amp; When should I use SPARSE COLUMN? (SQL SERVER 2008)

And what are the pitfalls to avoid? Are there any deal breaks for you? E.g., I&#39;ve heard that exporting/importing the Cassandra data is very difficult, making me wonder if that&#39;s going to hinder syncing production data to development environment.

BTW, it&#39;s very hard to find good tutorials on Cassandra, the only one I have http://arin.me/code/wtf-is-a-supercolumn-cassandra-data-model is still pretty basic.

Thanks.

What&#39;s The Best Practice In Designing A Cassandra Data Model?

Let&#39;s say you have a table Orders with a foreign key to a Customer Id. Now, suppose you want to add an Order without a Customer Id, (whether that should be possible is another question) you would have to make the foreign key NULL... Is that bad practice or would you rather work with a link table between Orders and Customers? Although the relationship is 1 to n, a link table would make it n to n. On the other hand, with a link table, I don&#39;t have those NULLS anymore... 

There won&#39;t actually be a lot of NULL&#39;s in the database, because a record with a foreign key to NULL is just temporarily until a customer for the order is added.

(In my case it isn&#39;t an Order and a Customer).

EDIT: What about a unassigned Customer to link to?

Nullable Foreign Key bad practice?

I need to create a survey where answers are stored in a database. I&#39;m just wondering what would be the best way to implement this in the database, specifically the tables required. The survey contains different types of questions. For example: text fields for comments, multiple choice questions, and possibly questions that could contain more than one answer (i.e. check all that apply).

I&#39;ve come up with two possible solutions:

 1. Create a giant table which contains
    the answers for each survey
    submission. Each column would
    correspond to an answer from the
    survey. i.e. SurveyID, Answer1,
    Answer2, Answer3
    
    I don&#39;t think this is the best way
    since there are a lot of questions
    in this survey and doesn&#39;t seem very
    flexible if the survey is to change.
 2. The other thing I thought of was
    creating a Question table and Answer
    table. The question table would
    contain all the questions for the
    survey. The answer table would contain
    individual answers from the survey,
    each row linked to a question.
    
    A simple example:
    
    **tblSurvey**: SurveyID

    **tblQuestion**: QuestionID, *SurveyID*, QuestionType, Question  

    **tblAnswer**: AnswerID, *UserID*, *QuestionID*, Answer

    **tblUser**: UserID, UserName

    My problem with this is that there
    could be tons of answers which would
    make the Answer table pretty huge.
    I&#39;m not sure that&#39;s so great when it
    comes to performance.

I&#39;d appreciate any ideas and suggestions.


Database design for a survey

What is the purpose of naming your constraints (unique, primary key, foreign key)?

Say I have a table which is using natural keys as a primary key:

    CREATE TABLE Order
    (
        LoginName        VARCHAR(50)    NOT NULL,
        ProductName      VARCHAR(50)    NOT NULL,
        NumberOrdered    INT            NOT NULL,
        OrderDateTime    DATETIME       NOT NULL,
        PRIMARY KEY(LoginName, OrderDateTime)
    );

What benefits (if any) does naming my PK bring?

Eg.
Replace:

        PRIMARY KEY(LoginName, OrderDateTime)

With:

        CONSTRAINT Order_PK PRIMARY KEY(LoginName, OrderDateTime)

Sorry if my data model is not the best, I&#39;m new to this!

What is the purpose of constraint naming

This is a follow-on question from the one I asked [here][1].

Can constraints in a DB have the same name?

Say I have:

    CREATE TABLE Employer
    (
        EmployerCode    VARCHAR(20)    PRIMARY KEY,
        Address         VARCHAR(100)   NULL
    )


    CREATE TABLE Employee
    (
        EmployeeID      INT            PRIMARY KEY,
        EmployerCode    VARCHAR(20)    NOT NULL,
        CONSTRAINT employer_code_fk FOREIGN KEY (EmployerCode) REFERENCES Employer
    )


    CREATE TABLE BankAccount
    (
        BankAccountID   INT            PRIMARY KEY,
        EmployerCode    VARCHAR(20)    NOT NULL,
        Amount          MONEY          NOT NULL,
        CONSTRAINT employer_code_fk FOREIGN KEY (EmployerCode) REFERENCES Employer
    )

Is this allowable? Does it depend on the DBMS (I&#39;m on SQL Server 2005)? If it is not allowable, does anyone have any suggestions on how to work around it?


  [1]: https://stackoverflow.com/questions/1397440/

Can there be constraints with the same name in a DB?

How can I drop all user tables in oracle?

I have problem with constraints. When I disable all it is still no possible.

How to drop all user tables?

Is there a way to set a SQL constraint for a numeric field that min value should be 1234 and max value should be 4523?

SQL constraint minvalue / maxvalue?

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?

    GO
    IF OBJECT_ID(&#39;dbo.[Course]&#39;,&#39;U&#39;) IS NOT NULL
        DROP TABLE dbo.[Course]
    GO
    IF OBJECT_ID(&#39;dbo.[Student]&#39;,&#39;U&#39;) IS NOT NULL
        DROP TABLE dbo.[Student]

Content Type	Original Author	Original Content on Stackoverflow
Question	The Disintegrator	View Question on Stackoverflow
Solution 1 - Mysql	lutz	View Answer on Stackoverflow
Solution 2 - Mysql	chaos	View Answer on Stackoverflow
Solution 3 - Mysql	cg.	View Answer on Stackoverflow

Unique constraint that allows empty values in MySQL

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

MySQL Fire Trigger for both Insert and Update

Why do we need RESTful Web Services?

Attributions