FAQ Database Discussion Community


add new column to existing Composite Unique Key

mysql,unique-constraint,composite-key,alter,unique-index
I have table named "room" , and it has the following composite unique key room_acId_levelId_year_name_unique formed from FOUR columns : acId levelId year name And I want add column isLevel to that key without drop and recreate it The new key should like this: room_acId_levelId_year_name_isLevel_unique with the following columns :...

Time complexity of inserting into table with PRIMARY KEY on HASH index

mysql,hash,time-complexity,unique-index
I just found out that PRIMARY KEYs on HASH-indexed columns in MEMORY tables are themselves HASH indices, as shown by the following: mysql> CREATE TABLE `test_memory` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`), -> KEY `id` (`id`) USING HASH -> ) ENGINE=MEMORY DEFAULT CHARSET=latin1; Query OK,...

Conditional Unique index on h2 database

oracle,h2,unique-index
I have a SAMPLE_TABLE with a column BIZ_ID which should be unique when the column active is not equal to 0. On an oracle database the index looks like this: CREATE UNIQUE INDEX ACTIVE_ONLY_IDX ON SAMPLE_TABLE (CASE "ACTIVE" WHEN 0 THEN NULL ELSE "BIZ_ID" END ); How would this unique...

Decision to use KEY or UNIQUE KEY

mysql,indexing,unique-index
I understand that UNIQUE KEY is a unique index and KEY is a non-unique index. I have read that in case of unique index'es inserting data might result in some IO. If we don't have to rely on the DB for unique-ness and we still want fast lookup's using column...

Can not drop UNIQUE index from table

sql,sql-server,unique-index
When I run this query ALTER TABLE "dbo"."ROOM" DROP INDEX "UNIQUE"; I got this message: Error 1018: Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. The name...