Hi,
I would like to add a unique index that consists of two fields in a
table.
e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination
must be Unique.
Can anyone tell me the actual sql syntax to create this index?
Thanks,
June.
3  27480 
Depending on whether you want a clustered or non-clustered index:
CREATE UNIQUE CLUSTERED INDEX index_name ON tbl_A (col1,col2)
or
CREATE UNIQUE NONCLUSTERED INDEX index_name ON tbl_A (col1,col2)
--
David Portas
------------
Please reply only to the newsgroup
--
You might consider specifying a unique constraint instead of a unique
index since uniqueness is a characteristic of you data. SQL Server will
create a unique index to support a unique constraint so these are
functionally identical in terms of performance.
Unique constraint syntax:
ALTER TABLE tbl_A
ADD CONSTRAINT UN_tbl_A_1
UNIQUE (field1,field2)
Unique index syntax:
CREATE UNIQUE INDEX Index_tbl_A_1
ON tbl_A (field1,field2)
Also, either of the above indexes can be created as clustered or
non-clustered. See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index....partmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq
-----------------------
"June Moore" <ju******@yahoo .com.au> wrote in message
news:e5******** *************** ***@posting.goo gle.com... Hi,
I would like to add a unique index that consists of two fields in a
table.
e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination
must be Unique.
Can anyone tell me the actual sql syntax to create this index?
Thanks,
June.
create unique index _ix on tb_name(col1,co l2)
--
-oj
Rac v2.2 & QALite! http://www.rac4sql.net
"June Moore" <ju******@yahoo .com.au> wrote in message
news:e5******** *************** ***@posting.goo gle.com... Hi,
I would like to add a unique index that consists of two fields in a
table.
e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination
must be Unique.
Can anyone tell me the actual sql syntax to create this index?
Thanks,
June. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Kamil |
last post by:
Hello
What should I use for better perfomance since
unique constraint always use index ?
Thanks
Kamil
|
by: Laurent |
last post by:
DB2 8.1
-------
db2 => create table test (COL1 VARCHAR(10))
db2 => insert into test values ('A')
db2 => insert into test values ('A ')
db2 => insert into test values ('B')
db2 => insert into test values ('B ')
db2 => insert into test values ('C')
db2 => select distinct COL1, LENGTH(COL1) from test
|
by: deko |
last post by:
I have a Make Table query that creates a fairly large table. The Make Table
query populates the new table with one AutoNumber field (which is taken form
another unrelated table as part of the query).
I want to make the AutoNumber field the Primary Key:
DoCmd.OpenQuery "qryXL_To" ' this is the make table query
DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)"
This seems to work okay, but it does not create a...
|
by: deko |
last post by:
I understand it's possible to make a composite Primary Key by holding down
the control key and selecting multiple fields, then right-clicking and
selecting Primary Key. But I've heard that's not a good idea. What I want
to do is use table constraints to ensure only unique transactions - defined
by a combination of amount, date, account and customer - are entered in the
table while leaving the AutoNumber Transaction_ID the sole field in...
|
by: Laurence |
last post by:
Hi there,
How to differentiate between unique constraint and unique index? These
are very similar but I cannot differentiate them?
Could someone give me a hand?
Thanks in advance
| | |
by: vj_dba |
last post by:
Hi Group,
I have a Primary key in my table. It's clear Primary key wont allow
duplicates, this primary key creates one index for retrival. Suppose if
my table is having a Unique index also. Then what is the exact
difference between the Primary key and the Unique index?
Also which on data retrival..internally it uses the Primary key index
|
by: p175 |
last post by:
What are the advantages / disadvantages of having primary keys in lieu
of ordinary indexes ? If there are no foreign relationships defined, is
it better to have a two column index allowing reverse scans or primary
key ? Any performance or other benefits / issues ?
Many thanks.
|
by: rAinDeEr |
last post by:
I have a Parts Table with the following structure..
I need to decide which makes the primary key of the table.
I cant have any sequences nor can I add any new fields in this Table.
Table Structure ::
Create table Parts
(Parts_desig char(10),
Sequence_no integer,
Effective_Date Date,
|
by: rdsandy |
last post by:
Hi,
I have a table Risk, a table Mitigation and a table RiskArchive. I am taking the RiskID, Criticality and MitigationPlan fields from Risk, and MitigationActionID from Mitigation and inserting them into RiskArchive, using a stored procedure. What happens is if there is no entry in RiskArchive for a specific RiskID, and the other fields relevant for it, then it needs to be archived, but also only if it is not duplicated.
I have made an...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
