Creating one specific value as unique

Hello, I have a question about SQL Serer.
I don't know how to make a "constrain" whitch protect one column in table
for specific value.
In example:
I would to have unique value '33' in column a
'id' , 'a'
1, 33
2, 45
3, 45
4, 45
6, 33 - I want to have exception i this momentUNIQUE
Create Table Test
(
ID INT,
Nombres INT UNIQUE
)
Insert into Test Values (1,1)
Insert into Test Values (1,1)
Look in BOL --Section CREATE TABLE
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
> Hello, I have a question about SQL Serer.
> I don't know how to make a "constrain" whitch protect one column in table
> for specific value.
> In example:
> I would to have unique value '33' in column a
> 'id' , 'a'
> 1, 33
> 2, 45
> 3, 45
> 4, 45
> 6, 33 - I want to have exception i this moment|||Jens
I think it is not exactly what he wanted
>I would to have unique value '33' in column a
'>id' , 'a'
>1, 33
>2, 45
>3, 45
>4, 45
> 6, 33 - I want to have exception i this moment
It will be thrown an error when you try to insert a value=45
Well ,the following solution in terms of performance amy be not ideal , so
you can re-write it as
a trigger to do the same thing.
CREATE TABLE TableA
(
col INT
)
INSERT INTO TableA VALUES (20)--WORKS
GO
CREATE FUNCTION dbo.fn_check_TableA()
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT *
FROM TableA
WHERE col = 20
GROUP BY col
HAVING COUNT(*)>1)
RETURN 0
RETURN 1
END
GO
ALTER TABLE TableA
ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
GO
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (20)--failed
DROP TABLE TableA
DROP FUNCTION dbo.fn_check_TableA
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
table
>|||Didnt he want a exception ?
"I want to have exception i this moment"
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Jens
> I think it is not exactly what he wanted
> '>id' , 'a'
> It will be thrown an error when you try to insert a value=45
> Well ,the following solution in terms of performance amy be not ideal , so
> you can re-write it as
> a trigger to do the same thing.
> CREATE TABLE TableA
> (
> col INT
> )
> INSERT INTO TableA VALUES (20)--WORKS
> GO
> CREATE FUNCTION dbo.fn_check_TableA()
> RETURNS bit
> AS
> BEGIN
> IF EXISTS(SELECT *
> FROM TableA
> WHERE col = 20
> GROUP BY col
> HAVING COUNT(*)>1)
> RETURN 0
> RETURN 1
> END
> GO
> ALTER TABLE TableA
> ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
> GO
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (20)--failed
> DROP TABLE TableA
> DROP FUNCTION dbo.fn_check_TableA
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> table
>|||Try this ...
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [IX_test] UNIQUE NONCLUSTERED
(
id
)|||Yep, but it still should be allowed to insert another value,45? Am I right?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Didnt he want a exception ?
> "I want to have exception i this moment"
> Jens.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
so
>|||Dead Jans thank you for yor reply, but you did not understand my question .
If i use yours script i will get all values as unique. But I would like to
get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other values
can duplicate.
"Jens Sü?meyer" wrote:

> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Sü?meyer.|||Thank Uri Dimanti, this answer solve part of my problem :)
But i have another question - how to solve this problem on sql server ce (on
sql server ce i can't create functions) ?
Mariusz Wilk|||Yeah, bit i think that ok, because in his example on exception is thown in
the fifth line.
So the exception or the Contraint should be just palin unique.
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uFQoxSOQFHA.2580@.TK2MSFTNGP10.phx.gbl...
> Yep, but it still should be allowed to insert another value,45? Am I
> right?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> so
>|||OK, understand.
Thanks for clearing up.
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C233C0DD-E834-4744-BFC6-FB2C5422F1E9@.microsoft.com...
> Dead Jans thank you for yor reply, but you did not understand my question
> .
> If i use yours script i will get all values as unique. But I would like to
> get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other
> values
> can duplicate.
>
> "Jens Smeyer" wrote:
>
>
>