A tough question about constraint

A tough question about constraint

Post by jy » Fri, 11 Oct 2002 03:28:02



Greetings,
Scenario:
create table T (a int, b char(4) )

Requirement:
Put an constraint on T so that the following is unique (a, left(b,2) )

Examples
a           b
=        ====
1        abcd
2        abcd
1        abef  (not allowed because violating the constraint, record 1
already has the combination of (1, ab) )

Any suggetsions will be highly appreciated! ( Triggers can do the work, for
sure)

Jeff

 
 
 

A tough question about constraint

Post by Tibor Karasz » Fri, 11 Oct 2002 03:40:07


How about creating an index with the expression and create a unique index over that?

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Greetings,
> Scenario:
> create table T (a int, b char(4) )

> Requirement:
> Put an constraint on T so that the following is unique (a, left(b,2) )

> Examples
> a           b
> =        ====
> 1        abcd
> 2        abcd
> 1        abef  (not allowed because violating the constraint, record 1
> already has the combination of (1, ab) )

> Any suggetsions will be highly appreciated! ( Triggers can do the work, for
> sure)

> Jeff


 
 
 

A tough question about constraint

Post by Troy » Fri, 11 Oct 2002 03:46:42


Jeff;

-----------TEST SCRIPT----------
create table T (
  a int,
  b char(4),
  ForUnique AS CAST(a as varchar(10)) + SUBSTRING(b, 1, 2),
  CONSTRAINT uq_ForUnique UNIQUE(ForUnique)
)

SET NOCOUNT ON
INSERT INTO T VALUES(1, 'abcd')
INSERT INTO T VALUES(2, 'abcd')
INSERT INTO T VALUES(1, 'abef')
SET NOCOUNT OFF
GO

SELECT * FROM T

DROP TABLE T
GO
-----------END TEST SCRIPT -----------

HTH,
TroyK, MCSD

Quote:>-----Original Message-----
>Greetings,
>Scenario:
>create table T (a int, b char(4) )

>Requirement:
>Put an constraint on T so that the following is unique
(a, left(b,2) )

>Examples
>a           b
>=        ====
>1        abcd
>2        abcd
>1        abef  (not allowed because violating the

constraint, record 1
Quote:>already has the combination of (1, ab) )

>Any suggetsions will be highly appreciated! ( Triggers

can do the work, for
Quote:>sure)

>Jeff

>.

 
 
 

A tough question about constraint

Post by jy » Fri, 11 Oct 2002 03:54:34


Hi Tibor,
"How about creating an index ...", do you mean creating "a column"? (
instead of of " an index" )
This probably is the only way I can think of !
Thx,
Jeff



Quote:> How about creating an index with the expression and create a unique index
over that?

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




Quote:> > Greetings,
> > Scenario:
> > create table T (a int, b char(4) )

> > Requirement:
> > Put an constraint on T so that the following is unique (a, left(b,2) )

> > Examples
> > a           b
> > =        ====
> > 1        abcd
> > 2        abcd
> > 1        abef  (not allowed because violating the constraint, record 1
> > already has the combination of (1, ab) )

> > Any suggetsions will be highly appreciated! ( Triggers can do the work,
for
> > sure)

> > Jeff

 
 
 

A tough question about constraint

Post by jy » Fri, 11 Oct 2002 03:56:51


Yes, TroyK, you are right, this is probably the only way we can do !
Kind regards,
Jeff

Quote:> Jeff;

> -----------TEST SCRIPT----------
> create table T (
>   a int,
>   b char(4),
>   ForUnique AS CAST(a as varchar(10)) + SUBSTRING(b, 1, 2),
>   CONSTRAINT uq_ForUnique UNIQUE(ForUnique)
> )

> SET NOCOUNT ON
> INSERT INTO T VALUES(1, 'abcd')
> INSERT INTO T VALUES(2, 'abcd')
> INSERT INTO T VALUES(1, 'abef')
> SET NOCOUNT OFF
> GO

> SELECT * FROM T

> DROP TABLE T
> GO
> -----------END TEST SCRIPT -----------

> HTH,
> TroyK, MCSD
> >-----Original Message-----
> >Greetings,
> >Scenario:
> >create table T (a int, b char(4) )

> >Requirement:
> >Put an constraint on T so that the following is unique
> (a, left(b,2) )

> >Examples
> >a           b
> >=        ====
> >1        abcd
> >2        abcd
> >1        abef  (not allowed because violating the
> constraint, record 1
> >already has the combination of (1, ab) )

> >Any suggetsions will be highly appreciated! ( Triggers
> can do the work, for
> >sure)

> >Jeff

> >.

 
 
 

A tough question about constraint

Post by Tibor Karasz » Fri, 11 Oct 2002 04:09:43


Jeff,

Quote:> "How about creating an index ...", do you mean creating "a column"? (
> instead of of " an index" )

No, I meant a view. This way, you don't have to litter the table with columns. Using Troy's DDL:

create table T (
  a int,
  b char(4),
)

create view v_uq WITH SCHEMABINDING
as
SELECT CAST(a as varchar(10)) + SUBSTRING(b, 1, 2) AS colname
FROM dbo.t

create unique clustered index u_i on v_uq(colname)

SET NOCOUNT ON
INSERT INTO T VALUES(1, 'abcd')
INSERT INTO T VALUES(2, 'abcd')
INSERT INTO T VALUES(1, 'abef')
SET NOCOUNT OFF
GO

SELECT * FROM T

DROP TABLE T
GO

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Hi Tibor,
> "How about creating an index ...", do you mean creating "a column"? (
> instead of of " an index" )
> This probably is the only way I can think of !
> Thx,
> Jeff



> > How about creating an index with the expression and create a unique index
> over that?

> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



> > > Greetings,
> > > Scenario:
> > > create table T (a int, b char(4) )

> > > Requirement:
> > > Put an constraint on T so that the following is unique (a, left(b,2) )

> > > Examples
> > > a           b
> > > =        ====
> > > 1        abcd
> > > 2        abcd
> > > 1        abef  (not allowed because violating the constraint, record 1
> > > already has the combination of (1, ab) )

> > > Any suggetsions will be highly appreciated! ( Triggers can do the work,
> for
> > > sure)

> > > Jeff

 
 
 

A tough question about constraint

Post by jy » Fri, 11 Oct 2002 04:21:49


Looks great !
and much appreciated for the help, Tibor !

Jeff



Quote:> Jeff,

> > "How about creating an index ...", do you mean creating "a column"? (
> > instead of of " an index" )

> No, I meant a view. This way, you don't have to litter the table with

columns. Using Troy's DDL:
Quote:

> create table T (
>   a int,
>   b char(4),
> )

> create view v_uq WITH SCHEMABINDING
> as
> SELECT CAST(a as varchar(10)) + SUBSTRING(b, 1, 2) AS colname
> FROM dbo.t

> create unique clustered index u_i on v_uq(colname)

> SET NOCOUNT ON
> INSERT INTO T VALUES(1, 'abcd')
> INSERT INTO T VALUES(2, 'abcd')
> INSERT INTO T VALUES(1, 'abef')
> SET NOCOUNT OFF
> GO

> SELECT * FROM T

> DROP TABLE T
> GO

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




Quote:> > Hi Tibor,
> > "How about creating an index ...", do you mean creating "a column"? (
> > instead of of " an index" )
> > This probably is the only way I can think of !
> > Thx,
> > Jeff

> > "Tibor Karaszi"



> > > How about creating an index with the expression and create a unique
index
> > over that?

> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

- Show quoted text -



> > > > Greetings,
> > > > Scenario:
> > > > create table T (a int, b char(4) )

> > > > Requirement:
> > > > Put an constraint on T so that the following is unique (a,
left(b,2) )

> > > > Examples
> > > > a           b
> > > > =        ====
> > > > 1        abcd
> > > > 2        abcd
> > > > 1        abef  (not allowed because violating the constraint, record
1
> > > > already has the combination of (1, ab) )

> > > > Any suggetsions will be highly appreciated! ( Triggers can do the
work,
> > for
> > > > sure)

> > > > Jeff

 
 
 

1. Tough security / login credentials question!

Hi,

How can I use the Windows NT Domain login credentials to log into a SQL
Server Database via IIS and IE (on the client)?

I'd like to use the credentials the user has from the login he used on
the workstation in the domain. This means IE should somehow transfer the
login information from the local system to IIS on the server which in
turn should use that for the SQL Server login. Any ideas? I'm totally
lost with this!

Here's what happens now:

1. Client connects via HTTP (IE 4.x) to the server.

2. The server opens a connection to the database. I can either specify
login credentials in the connect string (don't want that) or use trusted
connection. When using trusted connection, it logs in as the user IIS is
running as! How can I make IIS log into the database as the user sending
the request via IE?

Thanks!

- Balt

Sent via Deja.com http://www.deja.com/
Before you buy.

2. PRODUCT_USER_PROFILE table

3. Tough View Question (at least for me)

4. How do I set the DEFAULT textsize on the the SQL Server v6.5

5. Tough Question for SQL Server Programmers...

6. is there a function

7. Tough Create Table Question

8. calling an Oracle Package PLS-00201:identifier must be declared

9. Tough SQL question

10. Tough question about system tables.

11. Tough security / login credentials question!

12. Tough Index Question

13. Yet another tough MDX Question.