constraint?

Hi,
I was wondering how i could set up a constraint if i want to prevent
duplicates.
For example, in a table where i have 4 columns:
-id (identity and primary)
-contactid
-type
-period
One can have values like:
id | contactid | type | period
1 c123 1 11
2 c355 | 2 | 11
Now what i would like to do is if someone inserts a record like id 1 and
the period is 12, then the constraint should apply. If someone inserts a
record like id 2 and the period is 12 then it's ok.
Is this possible?Somethinmg like this for example:
ALTER TABLE your_table
ADD CONSTRAINT ak_constraint_name
UNIQUE (contactid, period) ;
David Portas
SQL Server MVP
--|||"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>
I'm still not quite sure what you are after here. You can create a UNIQUE
constraint/index across multiple columns in a table.
For your first example, a UNIQUE constraint across (id, contactid, type)
would do the trick.
What is the difference with record id 2? Is it because the type column has
pipe symbols in it? I'm not following.
If it is more complex than the simple UNIQUE constraint, then you can either
apply your rules in a stored procedure, or with a trigger.
Rick Sawtell
MCT, MCSD, MCDBA|||Create a unique index, duplicate inserts will fail.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Jason
What is about type column? What is the purpose of this column?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Rick Sawtell wrote:
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>
> I'm still not quite sure what you are after here. You can create a UNIQU
E
> constraint/index across multiple columns in a table.
> For your first example, a UNIQUE constraint across (id, contactid, type)
> would do the trick.
> What is the difference with record id 2? Is it because the type column h
as
> pipe symbols in it? I'm not following.
> If it is more complex than the simple UNIQUE constraint, then you can eith
er
> apply your rules in a stored procedure, or with a trigger.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hi Rick,
The pipes are nothing, it's a seperator.
The difference is, that a contact with type 1 may be inserted only once.
Type 2 can have several inserts.
So i only want to constrain contacts which has type 1.|||create table semi_unique(id int primary key, stype int, uq int, u1 as
case when stype=1 then null else id end)
-- uq must be unique for type 1 only
create unique index u1 on semi_unique(uq, u1)
insert into semi_unique(id, stype, uq) values(1,1,1)
-- should fail
insert into semi_unique(id, stype, uq) values(2,1,1)
-- should succeed
insert into semi_unique(id, stype, uq) values(3,2,1)
insert into semi_unique(id, stype, uq) values(4,2,1)
select * from semi_unique
drop table semi_unique|||Alexander Kuznetsov wrote:
> create table semi_unique(id int primary key, stype int, uq int, u1 as
> case when stype=1 then null else id end)
> -- uq must be unique for type 1 only
> create unique index u1 on semi_unique(uq, u1)
> insert into semi_unique(id, stype, uq) values(1,1,1)
> -- should fail
> insert into semi_unique(id, stype, uq) values(2,1,1)
> -- should succeed
> insert into semi_unique(id, stype, uq) values(3,2,1)
> insert into semi_unique(id, stype, uq) values(4,2,1)
> select * from semi_unique
> drop table semi_unique
>
Hi it worked, but what if i add another column to it.