Index from two fields

Hello,
How can I create an index that would make sure that the combination of given
two fields is unique (something like when primary key is assigned to two
fields)? Or, perhaps it's a constraint?
Thanks,
Pavils.Just create the primary key, Unique constraint or unique index over the two
columns (col1, col2).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I create an index that would make sure that the combination of giv
en
> two fields is unique (something like when primary key is assigned to two
> fields)? Or, perhaps it's a constraint?
> Thanks,
> Pavils.
>|||Pavlis
CREATE TABLE Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
PRIMARY KEY(col1,col2)
)
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I create an index that would make sure that the combination of
given
> two fields is unique (something like when primary key is assigned to two
> fields)? Or, perhaps it's a constraint?
> Thanks,
> Pavils.
>|||There's the thing that the primary key is already used, it's on one int type
field and serves as unique identifier for internal use. But, those two
fields I am talking about, they are of int and text type and due to the
nature of data their combination should not be equal to any other record in
the table.
Pavils
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Just create the primary key, Unique constraint or unique index over the
two columns (col1, col2).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
given[vbcol=seagreen]
>|||Pavlis
Perhasp you want to look at CHECK Constraints in the BOL. It does not make
for me any sense , what are trying to accomplish?
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> There's the thing that the primary key is already used, it's on one int
type
> field and serves as unique identifier for internal use. But, those two
> fields I am talking about, they are of int and text type and due to the
> nature of data their combination should not be equal to any other record
in
> the table.
> Pavils
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> two columns (col1, col2).
> given
two[vbcol=seagreen]
>|||Ok, here's the case:
The table contains agreement information. Besides the other fields,
agreement has the company id (there are about 5 companies), and agreement
number (which may contain digits, letters, and dashes). The style of
agreement number is different for every company, but, by coincidence,
sometimes there could be case that two entries have equal agreement numbers
but different company ids, and that's Ok. The agrement table also has a
primary key field of type int. Thus, the primary key is already taken.
Now, what I want to enforce, is that SQL server does not allow insertion of
new record, if the table already containd entry with matching company id and
agreement number.
So, perhaps I have to use constraints rather than index. But, what would be
syntax of such constraint?
Thanks,
Pavils.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eI6VG9$jEHA.484@.TK2MSFTNGP10.phx.gbl...
> Pavlis
> Perhasp you want to look at CHECK Constraints in the BOL. It does not make
> for me any sense , what are trying to accomplish?
>
> "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> type
> in
> in
the[vbcol=seagreen]
of[vbcol=seagreen]
> two
>|||What you have is a surrogate key (the current primary key), but you have not
enforced the uniqueness
of your natural key. Use a UNIQUE constraint for that. Something like:
ALTER TABLE tblname
ADD CONSTRAINT name_of_constraint UNIQUE(companyid, agreementnumber)
Unfortunately, this is called UNIQUE constraint. Id' be much happier if ANSI
decided to name it
ALTERNATE KEY constraint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23Z86xZAkEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Ok, here's the case:
> The table contains agreement information. Besides the other fields,
> agreement has the company id (there are about 5 companies), and agreement
> number (which may contain digits, letters, and dashes). The style of
> agreement number is different for every company, but, by coincidence,
> sometimes there could be case that two entries have equal agreement number
s
> but different company ids, and that's Ok. The agrement table also has a
> primary key field of type int. Thus, the primary key is already taken.
> Now, what I want to enforce, is that SQL server does not allow insertion o
f
> new record, if the table already containd entry with matching company id a
nd
> agreement number.
> So, perhaps I have to use constraints rather than index. But, what would b
e
> syntax of such constraint?
> Thanks,
> Pavils.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eI6VG9$jEHA.484@.TK2MSFTNGP10.phx.gbl...
> the
> of
>