create table with dynamic constraint

I am trying to create a table with the type of constraint I don't see in any
of the help resources.
Say the basic table structure for table t1 is (colType int, colDesc
varchar(10), colMiscellaneous varchar(100))
I want to limit the combination colType-colDesc thusly:
If the combination is new, it's okay.
If the combination is exactly the same as one previously used, it's okay.
If the colDesc is the same as one previously entered, but the colType is
different, the constraint is violated and the insert or update operation
aborts.
Is this even doable? I've used multi-column constraints before, but not in
this way.
Thanks in advance,
DaveIn t-SQL, you cannot have a query expression in a CHECK constraint, so
multi-row checks are not easy to implement declaratively. You can have a
scalar UDF in certain cases, but it might fail for UPDATE operations. So one
option is to use a trigger like:
CREATE TRIGGER trg ON t1 FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT <> 0 RETURN
IF EXISTS ( SELECT * FROM inserted i
WHERE EXISTS ( SELECT * FROM t1
WHERE t1.type = i.type
AND t1.descr <> i.descr )
) ROLLBACK
... -- add any error messages if needed.
Anith|||>> IF @.@.ROWCOUNT <> 0 RETURN
should be = 0 to see if there are any rows affected
Anith|||Dave,
I think an easier solution here would be to maintain two tables:
create table cols (
colType int not null primary key,
colDesc varchar(10)
)
create table colMisc (
colType int not null references cols(colType),
colMiscellaneous varchar(100)
)
This enforces the data integrity you want:
A single colType cannot have more than one description
A colMiscellaneous value must be associated with a colType and colDesc
You could preserve an interface like you have by creating a view to match
your current table, on which there is an INSTEAD OF trigger to perform
the one or two insert statements needed for each addition of a
colMiscellaneous
value. It may not seem like less work to do this, but it avoids what you're
awkwardly doing now, which is storing facts like "the description of
column #N
is blahblah" once for every colMiscellaneous value there happens to be
for that
column.
In the long run, what you're doing will likely get you into trouble that
you have to solve with more awkwardness, like by adding DISTINCT
to queries that shouldn't need it.
Steve Kass
Drew University
Dave wrote:

>I am trying to create a table with the type of constraint I don't see in an
y
>of the help resources.
>Say the basic table structure for table t1 is (colType int, colDesc
>varchar(10), colMiscellaneous varchar(100))
>I want to limit the combination colType-colDesc thusly:
>If the combination is new, it's okay.
>If the combination is exactly the same as one previously used, it's okay.
>If the colDesc is the same as one previously entered, but the colType is
>different, the constraint is violated and the insert or update operation
>aborts.
>Is this even doable? I've used multi-column constraints before, but not in
>this way.
>Thanks in advance,
>Dave
>
>|||That is an excellent point, and one that I had considered. However, there
really are only three columns, this is just an ancillary table of about 50
rows that will not get many hits, and there will be only one routine for
each of the operations (SELECT, INSERT, UPDATE, & DELETE). I was also just
curious how I would accomplish such a task.
I do know enough about normalization to recognize your solution is
theoretically better; in this case I think the fewer tables factor will
outweigh the drawbacks you point out.
Thanks,
Dave
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23S36LBWbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> Dave,
> I think an easier solution here would be to maintain two tables:
> create table cols (
> colType int not null primary key,
> colDesc varchar(10)
> )
> create table colMisc (
> colType int not null references cols(colType),
> colMiscellaneous varchar(100)
> )
> This enforces the data integrity you want:
> A single colType cannot have more than one description
> A colMiscellaneous value must be associated with a colType and colDesc
> You could preserve an interface like you have by creating a view to match
> your current table, on which there is an INSTEAD OF trigger to perform
> the one or two insert statements needed for each addition of a
> colMiscellaneous
> value. It may not seem like less work to do this, but it avoids what
you're
> awkwardly doing now, which is storing facts like "the description of
> column #N
> is blahblah" once for every colMiscellaneous value there happens to be
> for that
> column.
> In the long run, what you're doing will likely get you into trouble that
> you have to solve with more awkwardness, like by adding DISTINCT
> to queries that shouldn't need it.
> Steve Kass
> Drew University
> Dave wrote:
>
any

Create Table Question

Hi

Im wondering if someone could help me out with how to write sql for the following type of query.

I have 3 known strings of characters and three associated sql queries. The queries will always return an integer.

I want a table so that column 1 is the list of known strings, and column 2 is the results of the three queries.

Thank youcreate table mytable
( string varchar(100)
, result integer
)|||Or maybe something like this:
create table mytable
( string varchar(100)
, result integer)
AS
select string, SUM(result) from (
select string, result from query1
UNION ALL
select string, result from query2
UNION ALL
select string, result from query3)
group by string;
:D

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
Ganesh
Hi
If I understand
http://msdn.microsoft.com/library/de.../sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:
> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/d...r />
k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefiniti
on. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements o
f
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE RULE for a Default Type

Hi all,
Using: SQL Server 2000 SP3A Enterprise Edition
I have setup a table that holds application information. One of the fields
holds the Applications Version Information.
I have created a default type called Version of nvarchar and length 43.
Version information is made up of 2, 3 or 4 parts, Major, Minor, Build and
Revision (Major.Minor[.Build[.Revision]]). Each part can hold up to 10 digits
up to 2,147,483,647 (int without comas). That makes 4 blocks of 10 plus up t
o
3 seperators (being the .) makes 43 the max length.
What I want to do is create a rule that will only allow a valid version
number to be stored in the field. I had something like this:
@.value LIKE '[0-9].[0-9]' OR @.value LIKE '[0-9].[0-9].[0-9]' OR @.value LIKE
'[0-9].[0-9].[0-9].[0-9]'
This will not allow 1.10.8903.56 as [0-9] specifies single characters only.
Is there quick way to do the validation as a rule without having to type
loads of LIKE statements for every possibility?Use a CHECK constraint rather than a RULE. Rules and user-defined types
are supported for backwards compatibility. Constraints are more much
easier to maintain and code.
In this case I think you'll find it easier to exclude the values you
don't want:
CREATE TABLE YourTable
(... , version VARCHAR(43) NOT NULL
CHECK (version NOT LIKE '[^.0-9]'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Oops. That should be:
CREATE TABLE YourTable
(version NVARCHAR(43) NOT NULL
CHECK (version NOT LIKE '%[^.0-9]%'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Hi,
Thanks for quick response to my question!
All working okay now!
Just 1 other question! Why use a Check instead of a Rule? I was using the
rule on the default type to save me typing the Check for every field as I
have many tables that contain this Version type field. Your code works both
as a Check and Rule.
Cheers
Paul|||Yes it will work as a Check and a Rule. User-defined types, defaults
and rules are designated as backwards compatibility features so they
won't necessarily be fully supported in future versions of SQL Server.
Books Online recommends using the ANSI/ISO standard alternatives, CHECK
and DEFAULT constraints, instead.
User-defined types are difficult to maintain because of the convoluted
syntax and binding - you have to remove all references and unbind
before you can make a change - a big problem if your type is used in
many columns. Constraints are declarative, unbound and much more
flexible.
CHECK constraints can also be used by the optimizer (although that's
unlikely to be useful with the constraint used here). I don't think the
optimizer can take advantage of Rules, although I confess I don't
recall where I've seen that documented so someone may correct me on
that point.
Finally, I suspect fewer SQL Server professionals will continue to use
and remember the old syntax in future so those who inherit your code
will probably be more productive if they don't have to cope with the
legacy stuff.
I think those are enough reasons not to use User-defined Types and
Rules. You want to save yourself some typing? Just cut-and-Paste the
CHECK constraint in Query Analyzer - that's no more work than pasting
the name of a user-defined type.
David Portas
SQL Server MVP
--|||Hi,
Thanks again for your information, very useful.
I have updated to use Check instead of Rule, was just trying to do the easy
way but as you pointed out sometimes the easy way can become problamatic in
the future.
Cheers again for your help.
Paul|||If you use a datamodeling tool (I use ERwin) you probably can do much the
same thing in the model, but generating them out as CHECK constraints. They
have domains that you can use in the model but only generate them as CHECKS.
Not sure if other tools have this, but it is a really feature of ERwin.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:667A4E1E-64D2-4030-A25E-399B07E3C9B8@.microsoft.com...
> Hi,
> Thanks again for your information, very useful.
> I have updated to use Check instead of Rule, was just trying to do the
> easy
> way but as you pointed out sometimes the easy way can become problamatic
> in
> the future.
> Cheers again for your help.
>
> Paul

Create device in emulator from Management Studio

Hi,

i know i have the chance to access my mobile device (physical or emulator) from Management Studio.
I went to Connect Object Explorer, server type Sql Server Mobile but i can′t connect to my emulator and i can′t see any way to connect to a physical device either. How can i do this?

The only thing i can do is create a sql server mobile database (.sdf).

Thanks
SP

Moving to Sql Server Compact Edition forum where it has got better chance of being answered.

-Thanks,

Mohit

|||

You can do this only if your device (or emulator) is connected through active sync.

Object Explorer:

Connect->

Database file-> Select 'Browse for more' and then select 'Mobile Device' from the file browser.

If you want to create a new database on the device, then just select 'New database' instead of 'Browse'

Thanks

Pragya