Mailing List Archive


Back to the month index Back to the list index
Alex Tang (altitude@petrified.cic.net)
Mon, 6 Jan 1997 19:05:06 -0500 (EST) 

Message-Id: <199701070005.TAA27223@petrified.cic.net>
Subject: [mSQL] updating unique index field?
Date: Mon, 6 Jan 1997 19:05:06 -0500 (EST)
From: Alex Tang <altitude@petrified.cic.net>

Hi folks.

I was just wondering, is this a bug?

The table test is defined as follows:

      #
      # Table structure for table 'test'
      #
      CREATE TABLE test (
        a INT,
        b INT,
        c INT
      ) \g
       
      CREATE UNIQUE INDEX idx1 ON test (
              a
      ) \g

The following values are inserted into test:

      INSERT INTO test VALUES (1,1,1)\g
      INSERT INTO test VALUES (2,2,2)\g
      INSERT INTO test VALUES (3,3,3)\g

The table looks like this:

      mSQL > select * from test \g
       
      Query OK. 3 row(s) modified or retrieved.
       
       +----------+----------+----------+
       | a | b | c |
       +----------+----------+----------+
       | 1 | 1 | 1 |
       | 2 | 2 | 2 |
       | 3 | 3 | 3 |
       +----------+----------+----------+

The following gives an error (as you would expect):

      mSQL > insert into test values ( 1, 2, 2 ) \g
       
      ERROR : Non unique value for unique index

But the following does NOT give an error:

      mSQL > UPDATE test
          -> SET a = 1
          -> WHERE b = 2 \g
       
      Query OK. 1 row(s) modified or retrieved.

Now the table looks like this:

      mSQL > select * from test \g
       
      Query OK. 3 row(s) modified or retrieved.
       
       +----------+----------+----------+
       | a | b | c |
       +----------+----------+----------+
       | 1 | 1 | 1 |
       | 1 | 2 | 2 |
       | 3 | 3 | 3 |
       +----------+----------+----------+

Being that I'm not much the database expert, I was wondering if this is
the expected behavior, or if this is a bug? It happens in both
msql-2.0-B1 and B2.

Thanks very much.

...alex... 

-- 
    Alex Tang   altitude@cic.net   http://petrified.cic.net/~altitude
CICNet:  Programmer/Webmaster, Network Services Group  http://www.cic.net
   Hey, it's one of my super powers...I'm super loyal.   -Road Rovers...
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com.  Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!