Hi,
What would be possible advantages of having Identity column as primary key
than having another unique column as primary key.?http://www.aspfaq.com/2504
"mavrick_101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4648FD42-BB0A-4B09-B1C1-820F08FFA46E@.microsoft.com...
> Hi,
> What would be possible advantages of having Identity column as primary key
> than having another unique column as primary key.?
>
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Tuesday, March 20, 2012
Sunday, March 11, 2012
Advanced Identity Fields
Hi!
a) PROBLEM 1:
I have set up my main database like this (of course I'm showing an abbreviation):
company - int (identity)
name - nchar
I have several companies stored in the previous table. Now I have another table with messages:
company - int (related with the company of the previous table)
messageID - int (identity)
contents - nchar
company and messageID are the main key of this table. I want to set the messageID column to change automatically. Since I declared it as identity it is working fine, but I was looking to start it on 0 on every new company:
Company messageID
0 0
0 1
0 2
1 0 <- Here the company changed, so the messageID resets
1 1
1 2
1 3
2 0 <- Again
2 1
Any suggestions?
b) PROBLEM 2:
I have my database stored locally on my computer. When I finished working with the database it has a lot of data for testing. I want to upload the database to my hosting provider or to my customer's. But the identity columns keep incrementing since the last value of my tests, so it's kind of annoying to see values as: 1250, 223, etc. when I expect to see 0,1, 2 and so on. Also, for receipts this is a very important issue.
How can I reset the identity fields?
Thank you very much for your attention and help.
CT
DBCC CHECKIDENT (tblName, RESEED, 0)
so the next record will start from 1
HTH
#1 You have to create trigger on this table for insert which will automatically calculate your message ID based on number of messages for company for which you insert record.
# 2 I suppose that you would like to remove test data from your table so you will be safe using TRUNCATE TABLE to delete all records and reset identity to 1
|||I really appreciate if you can give a sample for this. I'm worried that the trigger procedure wont be able to handle concurrency.Sunday, February 19, 2012
ADO Schema Rowsets and "identity"
Hi there,
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
Hi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||
> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
Hi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||
> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
ADO Schema Rowsets and "identity"
Hi there,
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.comHi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
> > I don't know much about ADO schema rowsets
> > But using T-SQL like
> > SELECT o.name, c.name FROM syscolumns c, sysobjects o
> > WHERE c.id = o.id AND (c.status & 128) = 128
> >
> > or
> > SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> > you can easily return the info about IDENTITY existence.
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.comHi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
> > I don't know much about ADO schema rowsets
> > But using T-SQL like
> > SELECT o.name, c.name FROM syscolumns c, sysobjects o
> > WHERE c.id = o.id AND (c.status & 128) = 128
> >
> > or
> > SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> > you can easily return the info about IDENTITY existence.
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
ADO Schema Rowsets and "identity"
Hi there,
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.comHi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdenti
ty)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||
> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdenti
ty)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.comHi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdenti
ty)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||
> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdenti
ty)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com
Subscribe to:
Posts (Atom)