usingtruncate tablewill help you.
thanks,
satish.
sql
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Friday, March 30, 2012
How can i reset the Primary key field numbers to zero before uploading my site to the prod
I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.DBCC CHECKIDENT (tableName, RESEED, 0)|||
How can i remove IDENTITY by ALTER TABLE?
Hello!
Can i remove IDENTITY property of the column by ALTER TABLE command?
Thanks!
Best regards, Konstantin KnyazevThis is the script that is generated when you do this with Enterprise
Manager
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_blah
(
oldid int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.blah)
EXEC('INSERT INTO dbo.Tmp_blah (oldid)
SELECT oldid FROM dbo.blah TABLOCKX')
GO
DROP TABLE dbo.blah
GO
EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
GO
COMMIT
As you can see it's not a one-liner
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Here's an example, basically you can't remove the property without an
intermediary column...
Note, you'll need to deal with the constraint / index if you created one,
just use ALTER TABLE DROP CONSTRAINT and add it again.
use tempdb
create table mytest (
id int not null identity,
myothercol int
)
insert mytest ( myothercol ) values( 1 )
go
begin tran
alter table mytest add id_new int null
exec ( 'update mytest set id_new = id' )
exec ( 'alter table mytest drop column id' )
exec sp_rename 'mytest.id_new', id
commit tran
go
select *
from mytest
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||Yes, i have tested this by EM, but i have too big table and it is very long
operation, about 40-50 min. I'm looking something quicker.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1146056271.030082.51260@.i40g2000cwc.googlegroups.com...
> This is the script that is generated when you do this with Enterprise
> Manager
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_blah
> (
> oldid int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.blah)
> EXEC('INSERT INTO dbo.Tmp_blah (oldid)
> SELECT oldid FROM dbo.blah TABLOCKX')
> GO
> DROP TABLE dbo.blah
> GO
> EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
> GO
> COMMIT
>
> As you can see it's not a one-liner
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||If your tables are so big and it will be PITA to do it then could you just
use
SET IDENTITY_INSERT table_name ON
do your thing
SET IDENTITY_INSERT table_name OFF
just a thought.
Grant
Who gives a {censored} if I am wrong.
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||This table should be a part of updatable distributed view, and using of
IDENTITY is restricted for it.
"Grant" <email@.nowhere.com> wrote in message
news:%23d8ZYKTaGHA.4248@.TK2MSFTNGP05.phx.gbl...
> If your tables are so big and it will be PITA to do it then could you just
> use
> SET IDENTITY_INSERT table_name ON
> do your thing
> SET IDENTITY_INSERT table_name OFF
> just a thought.
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
>
Can i remove IDENTITY property of the column by ALTER TABLE command?
Thanks!
Best regards, Konstantin KnyazevThis is the script that is generated when you do this with Enterprise
Manager
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_blah
(
oldid int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.blah)
EXEC('INSERT INTO dbo.Tmp_blah (oldid)
SELECT oldid FROM dbo.blah TABLOCKX')
GO
DROP TABLE dbo.blah
GO
EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
GO
COMMIT
As you can see it's not a one-liner
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Here's an example, basically you can't remove the property without an
intermediary column...
Note, you'll need to deal with the constraint / index if you created one,
just use ALTER TABLE DROP CONSTRAINT and add it again.
use tempdb
create table mytest (
id int not null identity,
myothercol int
)
insert mytest ( myothercol ) values( 1 )
go
begin tran
alter table mytest add id_new int null
exec ( 'update mytest set id_new = id' )
exec ( 'alter table mytest drop column id' )
exec sp_rename 'mytest.id_new', id
commit tran
go
select *
from mytest
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||Yes, i have tested this by EM, but i have too big table and it is very long
operation, about 40-50 min. I'm looking something quicker.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1146056271.030082.51260@.i40g2000cwc.googlegroups.com...
> This is the script that is generated when you do this with Enterprise
> Manager
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_blah
> (
> oldid int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.blah)
> EXEC('INSERT INTO dbo.Tmp_blah (oldid)
> SELECT oldid FROM dbo.blah TABLOCKX')
> GO
> DROP TABLE dbo.blah
> GO
> EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
> GO
> COMMIT
>
> As you can see it's not a one-liner
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||If your tables are so big and it will be PITA to do it then could you just
use
SET IDENTITY_INSERT table_name ON
do your thing
SET IDENTITY_INSERT table_name OFF
just a thought.
Grant
Who gives a {censored} if I am wrong.
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||This table should be a part of updatable distributed view, and using of
IDENTITY is restricted for it.
"Grant" <email@.nowhere.com> wrote in message
news:%23d8ZYKTaGHA.4248@.TK2MSFTNGP05.phx.gbl...
> If your tables are so big and it will be PITA to do it then could you just
> use
> SET IDENTITY_INSERT table_name ON
> do your thing
> SET IDENTITY_INSERT table_name OFF
> just a thought.
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
>
Monday, March 26, 2012
How Can I Programmatically Remove Identity from a Table?
I am copying a view to another location as a table. SQL 2000 is automaticall
y
setting the tables ID as an Indentity column. I do not want this to happen.
It may be because the original table from whence the view works off has
identity turned on. However the copy of the table I do not wish to have the
indentity turned on as I cannot do blanket inserts with this option on.
I already know what to do from the SQL Manager wizard however I require to
do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
kinds examples on how to create an indentity insert column but not one
example on how to remove the indentity insert from a table.I dont find remove ID property.
But I traced using Profiler, SQL Server copy a table, create a table remove
ID
,Insert data using INSERT SELECT and last sp_rename.
"Jamie Carper"?? ??? ??:
> I am copying a view to another location as a table. SQL 2000 is automatica
lly
> setting the tables ID as an Indentity column. I do not want this to happen
.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have th
e
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||You might consider changing your view to CAST the IDENTITY column so that
the IDENTITY property won't propagate to new tables:
ALTER VIEW View1
AS
SELECT
CAST(Col1 AS int) AS Col1
FROM MyTable
GO
You can't remove IDENTITY from an existing column or can add IDENTITY to an
existing one. If you don't want to recreate the table, you can use a script
like the one below to migrate data to a new non-identity column.
ALTER TABLE MyTable
ADD Col1_new int NOT NULL
--default constraint needed of NOT NULL
CONSTRAINT DF_MyTable_col1 DEFAULT 0
UPDATE MyTable
SET Col1_new = Col1
ALTER TABLE MyTable
DROP COLUMN Col1
EXEC sp_rename 'MyTable.Col1_new', 'Col1'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jamie Carper" <JamieCarper@.discussions.microsoft.com> wrote in message
news:196AC5D9-C450-4BB7-B057-E1F1C6B8628D@.microsoft.com...
>I am copying a view to another location as a table. SQL 2000 is
>automatically
> setting the tables ID as an Indentity column. I do not want this to
> happen.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have
> the
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||if I understood correctly, and you want to remove identity insert
then you should be using this switch.
SET ENABLE_IDENTITY_INSERT tbl_name,1
--don't remember the syntax exactly.
or if I have completely misunderstood the question, forgive me :)|||Hey Dan,
Thanks for the tip!
CASTing the Identity column did the trick.
"Dan Guzman" wrote:
> You might consider changing your view to CAST the IDENTITY column so that
> the IDENTITY property won't propagate to new tables:
>
Thanks again,
Jamie|||wrong syntax.. this is the one :)
SET IDENTITY_INSERT table1 ON
"Omnibuzz" wrote:
> if I understood correctly, and you want to remove identity insert
> then you should be using this switch.
> SET ENABLE_IDENTITY_INSERT tbl_name,1
> --don't remember the syntax exactly.
> or if I have completely misunderstood the question, forgive me :)
y
setting the tables ID as an Indentity column. I do not want this to happen.
It may be because the original table from whence the view works off has
identity turned on. However the copy of the table I do not wish to have the
indentity turned on as I cannot do blanket inserts with this option on.
I already know what to do from the SQL Manager wizard however I require to
do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
kinds examples on how to create an indentity insert column but not one
example on how to remove the indentity insert from a table.I dont find remove ID property.
But I traced using Profiler, SQL Server copy a table, create a table remove
ID
,Insert data using INSERT SELECT and last sp_rename.
"Jamie Carper"?? ??? ??:
> I am copying a view to another location as a table. SQL 2000 is automatica
lly
> setting the tables ID as an Indentity column. I do not want this to happen
.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have th
e
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||You might consider changing your view to CAST the IDENTITY column so that
the IDENTITY property won't propagate to new tables:
ALTER VIEW View1
AS
SELECT
CAST(Col1 AS int) AS Col1
FROM MyTable
GO
You can't remove IDENTITY from an existing column or can add IDENTITY to an
existing one. If you don't want to recreate the table, you can use a script
like the one below to migrate data to a new non-identity column.
ALTER TABLE MyTable
ADD Col1_new int NOT NULL
--default constraint needed of NOT NULL
CONSTRAINT DF_MyTable_col1 DEFAULT 0
UPDATE MyTable
SET Col1_new = Col1
ALTER TABLE MyTable
DROP COLUMN Col1
EXEC sp_rename 'MyTable.Col1_new', 'Col1'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jamie Carper" <JamieCarper@.discussions.microsoft.com> wrote in message
news:196AC5D9-C450-4BB7-B057-E1F1C6B8628D@.microsoft.com...
>I am copying a view to another location as a table. SQL 2000 is
>automatically
> setting the tables ID as an Indentity column. I do not want this to
> happen.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have
> the
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||if I understood correctly, and you want to remove identity insert
then you should be using this switch.
SET ENABLE_IDENTITY_INSERT tbl_name,1
--don't remember the syntax exactly.
or if I have completely misunderstood the question, forgive me :)|||Hey Dan,
Thanks for the tip!
CASTing the Identity column did the trick.
"Dan Guzman" wrote:
> You might consider changing your view to CAST the IDENTITY column so that
> the IDENTITY property won't propagate to new tables:
>
Thanks again,
Jamie|||wrong syntax.. this is the one :)
SET IDENTITY_INSERT table1 ON
"Omnibuzz" wrote:
> if I understood correctly, and you want to remove identity insert
> then you should be using this switch.
> SET ENABLE_IDENTITY_INSERT tbl_name,1
> --don't remember the syntax exactly.
> or if I have completely misunderstood the question, forgive me :)
Wednesday, March 21, 2012
How can I merge Identity tables?
Ok,
I have tables t1, t2, t3 and t4. I also have identical tables in a
CopyOfFirstDataBase, except this one contains older data that needs to
combine with the newer. It's like:
DATABASE1 --> DATABASE2
[1995 through 2003] [2004 through Present]
Both are structured identically. But, they both have Identity
fields. Each row in t1 through t4 is really one record relationally
and needs to remain so when merged.
Of course, there is already in the Identity columns records 1 through
whatever the last one is in both sets of the t1 - t4 1995 and records 1
through whatever in t1 - t4 2004 to present.
I don't care what the Identity numbers are, as long as I remain with
all records in tact. Any help is appreciated.
Thanks,
TrintI'd suggest you add a suitable negative offset to the identity values in
DATABASE1. Assuming your tables have fewer than 1000000000 rows,
set identity_insert t1 on
go
insert into DATABASE2..t1(identCol, dataCol1, dataCol2...)
select
-1000000000 + DATABASE1..t1.identCol,
dataCol1,
dataCol2,
...
go
set identity_insert t1 off
go
set identity_insert t2 on
go
-- repeat for t2, then t3, then t4
Steve Kass
Drew University
trint wrote:
>Ok,
>I have tables t1, t2, t3 and t4. I also have identical tables in a
>CopyOfFirstDataBase, except this one contains older data that needs to
>combine with the newer. It's like:
> DATABASE1 --> DATABASE2
>[1995 through 2003] [2004 through Present]
>Both are structured identically. But, they both have Identity
>fields. Each row in t1 through t4 is really one record relationally
>and needs to remain so when merged.
>Of course, there is already in the Identity columns records 1 through
>whatever the last one is in both sets of the t1 - t4 1995 and records 1
>through whatever in t1 - t4 2004 to present.
>I don't care what the Identity numbers are, as long as I remain with
>all records in tact. Any help is appreciated.
>Thanks,
>Trint
>
>|||You can use the proprietary IDENTITY_INSERT extension to preserve your
problem.
The real problems are that you do not know that rows are not records
and columns are not fields,and that IDENTITY cannot be a relational
key. You are still using the mindset and terminology of a sequential
file system -- a magnetic tape merge to be exact. The right answer is
to find a relational in the data and use it, so you do not keep
mimicing 1950's technology in SQL.|||> The real problems are that you do not know that rows are not records
> and columns are not fields,and that IDENTITY cannot be a relational
> key. You are still using the mindset and terminology of a sequential
> file system -- a magnetic tape merge to be exact. The right answer is
> to find a relational in the data and use it, so you do not keep
> mimicing 1950's technology in SQL.
Tilt that windmill Don Celko.
Thomas|||An identity column is an acceptable candidate for a primary key, especially
if the natural key is composed of several columns and thus very wide.
Another problem with natural keys is that things like last names, zip codes,
and even social security numbers tend are subject to change.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122296478.891875.17300@.g14g2000cwa.googlegroups.com...
> You can use the proprietary IDENTITY_INSERT extension to preserve your
> problem.
> The real problems are that you do not know that rows are not records
> and columns are not fields,and that IDENTITY cannot be a relational
> key. You are still using the mindset and terminology of a sequential
> file system -- a magnetic tape merge to be exact. The right answer is
> to find a relational in the data and use it, so you do not keep
> mimicing 1950's technology in SQL.
>|||>> An identity column is an acceptable candidate for a primary key, .. <<
Can you give me one authority for that statement? Damn that Dr. Codd!
Where did he and the last 30+ years of RDBMS definitions and theory
screw up? Now that you got it right, why not publish a paper with all
the formal proofs? By definition, a relational key is a subset of
attributes, not the internal state of the hardware in which the data is
stord.
And the answer to all questions is 42 when the numbrew get too large
for you to compute correctly? Has it ever occured to you that if you
have a (n) column natural key, then you HAVE to guarantee its
uniqueness? Well, only if you want to have data integrity, to avoid
redundant storage, and have validationa dn verification of your data.
But that would mean learning RDBMS, taking the time to do it right and
all those other anoiding things that professionals programmers do.
But, a "cowboy coder" jsut needs to pop IDENTITY on every table and
start coding.
LOL! Did yoiu notice that the problem in this thread was that IDENTITY
has complete duplicates for entiites in the **same schema**' Again,
look at the definition of a key. It must always refer to the same
entity in the same schema or (better) in the entire universe of
discourse (VIN) .
This non-key changes in the schema and no meaning in the Universe as
well as having no way to verify or validate it. What should have been
a simple INSERT INTO has to use proprietary kludges, special functions
and when the dat i smoved, it still will have no data integrity.
One of the advantages of an industry standard is that when it changes,
it changes everywhere, not just on one local machine. Have you looked
at the changes in retail with the UPC codes? Do you think that these
changes will destroy the Retail Industry? Do you think that Retail
would work better if every POS computer in the world used an IDENTITY?
You don't know what a key is, and you don't know what a surrogate key
is. Please do some reading before Ishow up at your job to try to save
a screwed up database at an insanely high per-day rate. Wait a minute,
what am I saying' :)
.|||Add a uniqueidentifier column populate all rows in all existing DB's with
newid()
Update t1
Set GUID = NEWID()
etc.
Once you've done that, peform an insert into your t1 and let SQL create new
identity values.
OK, here's the key to the system.
Now if you do the following SQL statements...
Select
a.Identity As OLDID,
b.Identity as NEWID
From
OldDB..t1 a
Join NewDB..t1 b on a.GUID = b.GUID
that way you can match the old ID to the new ID.
Then you can use this as part of a lookup so that when you perform the
insert for T2,3 and 4 you can substitute the new ID.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122312065.439046.68720@.g43g2000cwa.googlegroups.com...
> Can you give me one authority for that statement? Damn that Dr. Codd!
> Where did he and the last 30+ years of RDBMS definitions and theory
> screw up? Now that you got it right, why not publish a paper with all
> the formal proofs? By definition, a relational key is a subset of
> attributes, not the internal state of the hardware in which the data is
> stord.
>
> And the answer to all questions is 42 when the numbrew get too large
> for you to compute correctly? Has it ever occured to you that if you
> have a (n) column natural key, then you HAVE to guarantee its
> uniqueness? Well, only if you want to have data integrity, to avoid
> redundant storage, and have validationa dn verification of your data.
> But that would mean learning RDBMS, taking the time to do it right and
> all those other anoiding things that professionals programmers do.
> But, a "cowboy coder" jsut needs to pop IDENTITY on every table and
> start coding.
>
> LOL! Did yoiu notice that the problem in this thread was that IDENTITY
> has complete duplicates for entiites in the **same schema**' Again,
> look at the definition of a key. It must always refer to the same
> entity in the same schema or (better) in the entire universe of
> discourse (VIN) .
> This non-key changes in the schema and no meaning in the Universe as
> well as having no way to verify or validate it. What should have been
> a simple INSERT INTO has to use proprietary kludges, special functions
> and when the dat i smoved, it still will have no data integrity.
> One of the advantages of an industry standard is that when it changes,
> it changes everywhere, not just on one local machine. Have you looked
> at the changes in retail with the UPC codes? Do you think that these
> changes will destroy the Retail Industry? Do you think that Retail
> would work better if every POS computer in the world used an IDENTITY?
> You don't know what a key is, and you don't know what a surrogate key
> is. Please do some reading before Ishow up at your job to try to save
> a screwed up database at an insanely high per-day rate. Wait a minute,
> what am I saying' :)
> .
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:541372
Hi Joe,
Your message, like many of your other anti-IDENTITY messages, uses many
terribly bad arguments. And I think that's a shame. You know, you might
well have a very valid point, in saying that IDENTITY and other methods
for generating meaningless surrogate keys have no plcae in a relational
database - but the arguments you use to defend this case are so flawed
that they fail to convince me.
I've been a strong defender of natural keys for a long time. Lately,
I've seen some very convincing arguments from people in favor of
surrogate keys. This has caused a shift in opinion - I am now in favor
of judging each individual situation to decide whether or not to use a
surrogate key.
I'm open to new arguments that might have me return to the "natural keys
only" point of view. But they have to be better than what you'e writing
ion the groups lately. I'll illustrate this by showing how flawed the
arguments in this particular message are.
On 25 Jul 2005 10:21:05 -0700, --CELKO-- wrote:
> Can you give me one authority for that statement?
How about Dr. Codd? "..Database users may cause the system to generate
or delete a surrogate, but they have no control over its value, nor is
its value ever displayed to them ..." (CODD 1979, pp 409-410)
From: Codd, E. (1979), Extending the database relational model to
capture more meaning. ACM Transactions on Database Systems, 4(4). pp.
397-434.
> Damn that Dr. Codd!
> Where did he and the last 30+ years of RDBMS definitions and theory
>screw up?
Could you please tell me which of Codd's 12 rules prohibits the use of
IDENTITY as a primary key, and why? I fail to see a violation when I
check the rules, so please enlighten me.
> Now that you got it right, why not publish a paper with all
>the formal proofs?
Cynicism != arguments (or, in ANSI: cynicism <> arguments).
> By definition, a relational key is a subset of
>attributes, not the internal state of the hardware in which the data is
>stord.
A relational key, yes. But how does that definition preclude the use of
a surrogate key in addition to the natural key (which is, indeed, a
subset of attributes).
Oh and by the way - an IDENTITY is in no way dependent on the internal
state of the hardware. If you save a script with SQL commands that
create a table and populate it with data, then you can run it on as many
different machines as you wish, you'll always get the same identity
values. Not that it matters (since the value of a surrogate key is
irrelevant) - but these kind of flagrant misunderstandings about how
IDENTITY works seriously weaken your credibility, and as a result weaken
your other arguments as well.
>And the answer to all questions is 42 when the numbrew get too large
>for you to compute correctly?
I'm always in for references to Douglas Adams, but in this case I fail
to see how your remark relates to the quote directly above it.
> Has it ever occured to you that if you
>have a (n) column natural key, then you HAVE to guarantee its
>uniqueness? Well, only if you want to have data integrity, to avoid
>redundant storage, and have validationa dn verification of your data.
Has it ever occured to you that one single UNIQUE constraint *will*
guarantee the uniqueness of said (n) column natural key, even if the
table sports a surrogate primary key as well?
>But that would mean learning RDBMS, taking the time to do it right and
>all those other anoiding things that professionals programmers do.
Cynicism != arguments (or, in ANSI: cynicism <> arguments).
>But, a "cowboy coder" jsut needs to pop IDENTITY on every table and
>start coding.
Yup, you're right. Cowboy coders abuse the IDENTITY property, and later
they (or rather: their employers and customers) come to regret it. So
let's all try to teach these cowboys to use the tools properly, instead
of trying to keep other codes from actually using the tools for their
intended purpose.
There are many "cowboy drivers" who abuse their cars: they drive drunk,
neglect traffic lights and speed limits and run over little children. Do
you now think that everybody should be forbidden to use cars?
>LOL! Did yoiu notice that the problem in this thread was that IDENTITY
>has complete duplicates for entiites in the **same schema**'
Yes, the original post in this thread is a fine example of bad design.
It's a variation of the design flaw known as "attribute splitting".
Allthough there might be historical reasons why the situation has grown
to it's current situation, there's no denying that the current situation
is not something you or I would design.
The good news is that the original poster tries to fix it, instead of
using a kludge that would really destroy the database. The other good
news is that fixing this particular problems (two different entities
have the same surrogate key value) is not quite as hard as fixing a
similar problem I've seen with natural keys: two different entities
having the same *natural* key value!
> Again,
>look at the definition of a key. It must always refer to the same
>entity in the same schema or (better) in the entire universe of
>discourse (VIN) .
It seems that you keep forgetting that a surrogate key is a surrogate
for something - for the natural key. The natural key (that is included
in the main table, and constrained by a UNIQUE constraint) refers to the
same entity in the same schema; the surrogate key is used to refer to
that row from other places.
>One of the advantages of an industry standard is that when it changes,
>it changes everywhere, not just on one local machine. Have you looked
>at the changes in retail with the UPC codes? Do you think that these
>changes will destroy the Retail Industry? Do you think that Retail
>would work better if every POS computer in the world used an IDENTITY?
And one of the advantages of using a surrogate key is that changes to
the industry standard take far less downtime - none if things are
properly prepared.
>You don't know what a key is, and you don't know what a surrogate key
>is. Please do some reading before Ishow up at your job to try to save
>a screwed up database at an insanely high per-day rate. Wait a minute,
>what am I saying' :)
I don't know what the original poster does or doesn't know, so let's
forget the ad-hom. But please show me how a design like below would
require your insanely expensive intervention. How would the use of the
IDENTITY property screw up my integrity in this case?
CREATE TABLE Products (ProductID int NOT NULL IDENTITY PRIMARY KEY,
UPC char(10) NOT NULL UNIQUE,
-- other columns,
);
CREATE TABLE Orders (OrderNo int NOT NULL PRIMARY KEY,
-- other columns,
);
CREATE TABLE OrderProducts (ProductID int NOT NULL,
OrderNo int NOT NULL,
Amount int NOT NULL CHECK (Amount > 0),
-- other columns,
PRIMARY KEY (ProductID, OrderNo),
FOREIGN KEY (ProductID)
REFERENCES Products(ProductID),
FOREIGN KEY (OrderNum)
REFERENCES Orders(OrderNum),
);
I'm sorry for the long-winded reply. I've been saving this up for quite
some time now. Even back when I was still in the natural key only camp,
I often found myself grinding my teeth, asking myself why you did such a
lousy job of defending the Right Way To Do It. When I started getting
doubts, I found myself reading youyr messages, hoping to find some good
arguments not to change my mind - but I only saw the same flawed
arguments over and over again. Often, I started to write a message - and
just as often, I held myself back. Asking myself: "Why bother?"
This time, I won't hold back. I still think that you might be defending
a very good point. But you do need to supply better arguments. If you
can't - well, I guess that I'll than have to conclude that I was right
in changing my mind about surrogate keys...
Awaiting your reply!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:uotSpGTkFHA.3756@.TK2MSFTNGP15.phx.gbl...
> Tilt that windmill Don Celko.
>
> Thomas
>
Snipe that Celko, Thomas.
Jeremy|||Hugo Kornelis wrote:
> I don't know what the original poster does or doesn't know, so let's
> forget the ad-hom. But please show me how a design like below would
> require your insanely expensive intervention. How would the use of the
> IDENTITY property screw up my integrity in this case?
> CREATE TABLE Products (ProductID int NOT NULL IDENTITY PRIMARY KEY,
> UPC char(10) NOT NULL UNIQUE,
> -- other columns,
> );
> CREATE TABLE Orders (OrderNo int NOT NULL PRIMARY KEY,
> -- other columns,
> );
> CREATE TABLE OrderProducts (ProductID int NOT NULL,
> OrderNo int NOT NULL,
> Amount int NOT NULL CHECK (Amount > 0),
> -- other columns,
> PRIMARY KEY (ProductID, OrderNo),
> FOREIGN KEY (ProductID)
> REFERENCES Products(ProductID),
> FOREIGN KEY (OrderNum)
> REFERENCES Orders(OrderNum),
> );
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hugo,
Always enjoy your posts. How about this for your example - I'll use
natural keys instead of IDENTITY keys (I admit I use IDENTITY alot in my
DBs, though I don't set them as PKs. Set them as UNIQUE instead):
CREATE TABLE Products (UPC char(10) NOT NULL PRIMARY KEY,
-- other columns,
);
CREATE TABLE Orders (OrderNo int NOT NULL IDENTITY UNIUQE,
order_date DATETIME NOT NULL,
customer_id int NOT NULL,
-- other columns,
PRIMARY KEY (order_date, customer_id)
);
The PK in Orders assumes only 1 order per customer per date (just for
example's sake) ;-). OrderNo acts as a surrogate key (as I understand
surrogates).
CREATE TABLE OrderProducts (UPC char(10) NOT NULL
FOREIGN KEY REFERENCES Products (UPC)
ON DELETE CASCADE
ON UPDATE CASCADE,
OrderNo int NOT NULL
FOREIGN KEY REFERENCES Orders(OrderNo)
ON DELETE CASCADE
ON UPDATE CASCADE,
OrderAmount int NOT NULL CHECK (Amount > 0),
-- other columns,
PRIMARY KEY (UPC, OrderNo),
);
The reason I use OrderNo as a surrogate key is to avoid this:
CREATE TABLE OrderProducts (UPC char(10) NOT NULL
FOREIGN KEY REFERENCES Products (UPC)
ON DELETE CASCADE
ON UPDATE CASCADE,
order_date DATETIME NOT NULL
FOREIGN KEY REFERENCES Orders
ON DELETE CASCADE
ON UPDATE CASCADE,
customer_id int NOT NULL
FOREIGN KEY REFERENCES Orders
ON DELETE CASCADE
ON UPDATE CASCADE,
OrderAmount int NOT NULL CHECK (Amount > 0),
-- other columns,
PRIMARY KEY (UPC, order_date, customer_id),
);
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuVX1IechKqOuFEgEQJs2QCfVP9Cy7NKU6YR
T96fVlZ/uN71dSQAn2OL
KN52zWrCb83lQKGiTvlJuBq5
=voUL
--END PGP SIGNATURE--
I have tables t1, t2, t3 and t4. I also have identical tables in a
CopyOfFirstDataBase, except this one contains older data that needs to
combine with the newer. It's like:
DATABASE1 --> DATABASE2
[1995 through 2003] [2004 through Present]
Both are structured identically. But, they both have Identity
fields. Each row in t1 through t4 is really one record relationally
and needs to remain so when merged.
Of course, there is already in the Identity columns records 1 through
whatever the last one is in both sets of the t1 - t4 1995 and records 1
through whatever in t1 - t4 2004 to present.
I don't care what the Identity numbers are, as long as I remain with
all records in tact. Any help is appreciated.
Thanks,
TrintI'd suggest you add a suitable negative offset to the identity values in
DATABASE1. Assuming your tables have fewer than 1000000000 rows,
set identity_insert t1 on
go
insert into DATABASE2..t1(identCol, dataCol1, dataCol2...)
select
-1000000000 + DATABASE1..t1.identCol,
dataCol1,
dataCol2,
...
go
set identity_insert t1 off
go
set identity_insert t2 on
go
-- repeat for t2, then t3, then t4
Steve Kass
Drew University
trint wrote:
>Ok,
>I have tables t1, t2, t3 and t4. I also have identical tables in a
>CopyOfFirstDataBase, except this one contains older data that needs to
>combine with the newer. It's like:
> DATABASE1 --> DATABASE2
>[1995 through 2003] [2004 through Present]
>Both are structured identically. But, they both have Identity
>fields. Each row in t1 through t4 is really one record relationally
>and needs to remain so when merged.
>Of course, there is already in the Identity columns records 1 through
>whatever the last one is in both sets of the t1 - t4 1995 and records 1
>through whatever in t1 - t4 2004 to present.
>I don't care what the Identity numbers are, as long as I remain with
>all records in tact. Any help is appreciated.
>Thanks,
>Trint
>
>|||You can use the proprietary IDENTITY_INSERT extension to preserve your
problem.
The real problems are that you do not know that rows are not records
and columns are not fields,and that IDENTITY cannot be a relational
key. You are still using the mindset and terminology of a sequential
file system -- a magnetic tape merge to be exact. The right answer is
to find a relational in the data and use it, so you do not keep
mimicing 1950's technology in SQL.|||> The real problems are that you do not know that rows are not records
> and columns are not fields,and that IDENTITY cannot be a relational
> key. You are still using the mindset and terminology of a sequential
> file system -- a magnetic tape merge to be exact. The right answer is
> to find a relational in the data and use it, so you do not keep
> mimicing 1950's technology in SQL.
Tilt that windmill Don Celko.
Thomas|||An identity column is an acceptable candidate for a primary key, especially
if the natural key is composed of several columns and thus very wide.
Another problem with natural keys is that things like last names, zip codes,
and even social security numbers tend are subject to change.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122296478.891875.17300@.g14g2000cwa.googlegroups.com...
> You can use the proprietary IDENTITY_INSERT extension to preserve your
> problem.
> The real problems are that you do not know that rows are not records
> and columns are not fields,and that IDENTITY cannot be a relational
> key. You are still using the mindset and terminology of a sequential
> file system -- a magnetic tape merge to be exact. The right answer is
> to find a relational in the data and use it, so you do not keep
> mimicing 1950's technology in SQL.
>|||>> An identity column is an acceptable candidate for a primary key, .. <<
Can you give me one authority for that statement? Damn that Dr. Codd!
Where did he and the last 30+ years of RDBMS definitions and theory
screw up? Now that you got it right, why not publish a paper with all
the formal proofs? By definition, a relational key is a subset of
attributes, not the internal state of the hardware in which the data is
stord.
And the answer to all questions is 42 when the numbrew get too large
for you to compute correctly? Has it ever occured to you that if you
have a (n) column natural key, then you HAVE to guarantee its
uniqueness? Well, only if you want to have data integrity, to avoid
redundant storage, and have validationa dn verification of your data.
But that would mean learning RDBMS, taking the time to do it right and
all those other anoiding things that professionals programmers do.
But, a "cowboy coder" jsut needs to pop IDENTITY on every table and
start coding.
LOL! Did yoiu notice that the problem in this thread was that IDENTITY
has complete duplicates for entiites in the **same schema**' Again,
look at the definition of a key. It must always refer to the same
entity in the same schema or (better) in the entire universe of
discourse (VIN) .
This non-key changes in the schema and no meaning in the Universe as
well as having no way to verify or validate it. What should have been
a simple INSERT INTO has to use proprietary kludges, special functions
and when the dat i smoved, it still will have no data integrity.
One of the advantages of an industry standard is that when it changes,
it changes everywhere, not just on one local machine. Have you looked
at the changes in retail with the UPC codes? Do you think that these
changes will destroy the Retail Industry? Do you think that Retail
would work better if every POS computer in the world used an IDENTITY?
You don't know what a key is, and you don't know what a surrogate key
is. Please do some reading before Ishow up at your job to try to save
a screwed up database at an insanely high per-day rate. Wait a minute,
what am I saying' :)
.|||Add a uniqueidentifier column populate all rows in all existing DB's with
newid()
Update t1
Set GUID = NEWID()
etc.
Once you've done that, peform an insert into your t1 and let SQL create new
identity values.
OK, here's the key to the system.
Now if you do the following SQL statements...
Select
a.Identity As OLDID,
b.Identity as NEWID
From
OldDB..t1 a
Join NewDB..t1 b on a.GUID = b.GUID
that way you can match the old ID to the new ID.
Then you can use this as part of a lookup so that when you perform the
insert for T2,3 and 4 you can substitute the new ID.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122312065.439046.68720@.g43g2000cwa.googlegroups.com...
> Can you give me one authority for that statement? Damn that Dr. Codd!
> Where did he and the last 30+ years of RDBMS definitions and theory
> screw up? Now that you got it right, why not publish a paper with all
> the formal proofs? By definition, a relational key is a subset of
> attributes, not the internal state of the hardware in which the data is
> stord.
>
> And the answer to all questions is 42 when the numbrew get too large
> for you to compute correctly? Has it ever occured to you that if you
> have a (n) column natural key, then you HAVE to guarantee its
> uniqueness? Well, only if you want to have data integrity, to avoid
> redundant storage, and have validationa dn verification of your data.
> But that would mean learning RDBMS, taking the time to do it right and
> all those other anoiding things that professionals programmers do.
> But, a "cowboy coder" jsut needs to pop IDENTITY on every table and
> start coding.
>
> LOL! Did yoiu notice that the problem in this thread was that IDENTITY
> has complete duplicates for entiites in the **same schema**' Again,
> look at the definition of a key. It must always refer to the same
> entity in the same schema or (better) in the entire universe of
> discourse (VIN) .
> This non-key changes in the schema and no meaning in the Universe as
> well as having no way to verify or validate it. What should have been
> a simple INSERT INTO has to use proprietary kludges, special functions
> and when the dat i smoved, it still will have no data integrity.
> One of the advantages of an industry standard is that when it changes,
> it changes everywhere, not just on one local machine. Have you looked
> at the changes in retail with the UPC codes? Do you think that these
> changes will destroy the Retail Industry? Do you think that Retail
> would work better if every POS computer in the world used an IDENTITY?
> You don't know what a key is, and you don't know what a surrogate key
> is. Please do some reading before Ishow up at your job to try to save
> a screwed up database at an insanely high per-day rate. Wait a minute,
> what am I saying' :)
> .
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:541372
Hi Joe,
Your message, like many of your other anti-IDENTITY messages, uses many
terribly bad arguments. And I think that's a shame. You know, you might
well have a very valid point, in saying that IDENTITY and other methods
for generating meaningless surrogate keys have no plcae in a relational
database - but the arguments you use to defend this case are so flawed
that they fail to convince me.
I've been a strong defender of natural keys for a long time. Lately,
I've seen some very convincing arguments from people in favor of
surrogate keys. This has caused a shift in opinion - I am now in favor
of judging each individual situation to decide whether or not to use a
surrogate key.
I'm open to new arguments that might have me return to the "natural keys
only" point of view. But they have to be better than what you'e writing
ion the groups lately. I'll illustrate this by showing how flawed the
arguments in this particular message are.
On 25 Jul 2005 10:21:05 -0700, --CELKO-- wrote:
> Can you give me one authority for that statement?
How about Dr. Codd? "..Database users may cause the system to generate
or delete a surrogate, but they have no control over its value, nor is
its value ever displayed to them ..." (CODD 1979, pp 409-410)
From: Codd, E. (1979), Extending the database relational model to
capture more meaning. ACM Transactions on Database Systems, 4(4). pp.
397-434.
> Damn that Dr. Codd!
> Where did he and the last 30+ years of RDBMS definitions and theory
>screw up?
Could you please tell me which of Codd's 12 rules prohibits the use of
IDENTITY as a primary key, and why? I fail to see a violation when I
check the rules, so please enlighten me.
> Now that you got it right, why not publish a paper with all
>the formal proofs?
Cynicism != arguments (or, in ANSI: cynicism <> arguments).
> By definition, a relational key is a subset of
>attributes, not the internal state of the hardware in which the data is
>stord.
A relational key, yes. But how does that definition preclude the use of
a surrogate key in addition to the natural key (which is, indeed, a
subset of attributes).
Oh and by the way - an IDENTITY is in no way dependent on the internal
state of the hardware. If you save a script with SQL commands that
create a table and populate it with data, then you can run it on as many
different machines as you wish, you'll always get the same identity
values. Not that it matters (since the value of a surrogate key is
irrelevant) - but these kind of flagrant misunderstandings about how
IDENTITY works seriously weaken your credibility, and as a result weaken
your other arguments as well.
>And the answer to all questions is 42 when the numbrew get too large
>for you to compute correctly?
I'm always in for references to Douglas Adams, but in this case I fail
to see how your remark relates to the quote directly above it.
> Has it ever occured to you that if you
>have a (n) column natural key, then you HAVE to guarantee its
>uniqueness? Well, only if you want to have data integrity, to avoid
>redundant storage, and have validationa dn verification of your data.
Has it ever occured to you that one single UNIQUE constraint *will*
guarantee the uniqueness of said (n) column natural key, even if the
table sports a surrogate primary key as well?
>But that would mean learning RDBMS, taking the time to do it right and
>all those other anoiding things that professionals programmers do.
Cynicism != arguments (or, in ANSI: cynicism <> arguments).
>But, a "cowboy coder" jsut needs to pop IDENTITY on every table and
>start coding.
Yup, you're right. Cowboy coders abuse the IDENTITY property, and later
they (or rather: their employers and customers) come to regret it. So
let's all try to teach these cowboys to use the tools properly, instead
of trying to keep other codes from actually using the tools for their
intended purpose.
There are many "cowboy drivers" who abuse their cars: they drive drunk,
neglect traffic lights and speed limits and run over little children. Do
you now think that everybody should be forbidden to use cars?
>LOL! Did yoiu notice that the problem in this thread was that IDENTITY
>has complete duplicates for entiites in the **same schema**'
Yes, the original post in this thread is a fine example of bad design.
It's a variation of the design flaw known as "attribute splitting".
Allthough there might be historical reasons why the situation has grown
to it's current situation, there's no denying that the current situation
is not something you or I would design.
The good news is that the original poster tries to fix it, instead of
using a kludge that would really destroy the database. The other good
news is that fixing this particular problems (two different entities
have the same surrogate key value) is not quite as hard as fixing a
similar problem I've seen with natural keys: two different entities
having the same *natural* key value!
> Again,
>look at the definition of a key. It must always refer to the same
>entity in the same schema or (better) in the entire universe of
>discourse (VIN) .
It seems that you keep forgetting that a surrogate key is a surrogate
for something - for the natural key. The natural key (that is included
in the main table, and constrained by a UNIQUE constraint) refers to the
same entity in the same schema; the surrogate key is used to refer to
that row from other places.
>One of the advantages of an industry standard is that when it changes,
>it changes everywhere, not just on one local machine. Have you looked
>at the changes in retail with the UPC codes? Do you think that these
>changes will destroy the Retail Industry? Do you think that Retail
>would work better if every POS computer in the world used an IDENTITY?
And one of the advantages of using a surrogate key is that changes to
the industry standard take far less downtime - none if things are
properly prepared.
>You don't know what a key is, and you don't know what a surrogate key
>is. Please do some reading before Ishow up at your job to try to save
>a screwed up database at an insanely high per-day rate. Wait a minute,
>what am I saying' :)
I don't know what the original poster does or doesn't know, so let's
forget the ad-hom. But please show me how a design like below would
require your insanely expensive intervention. How would the use of the
IDENTITY property screw up my integrity in this case?
CREATE TABLE Products (ProductID int NOT NULL IDENTITY PRIMARY KEY,
UPC char(10) NOT NULL UNIQUE,
-- other columns,
);
CREATE TABLE Orders (OrderNo int NOT NULL PRIMARY KEY,
-- other columns,
);
CREATE TABLE OrderProducts (ProductID int NOT NULL,
OrderNo int NOT NULL,
Amount int NOT NULL CHECK (Amount > 0),
-- other columns,
PRIMARY KEY (ProductID, OrderNo),
FOREIGN KEY (ProductID)
REFERENCES Products(ProductID),
FOREIGN KEY (OrderNum)
REFERENCES Orders(OrderNum),
);
I'm sorry for the long-winded reply. I've been saving this up for quite
some time now. Even back when I was still in the natural key only camp,
I often found myself grinding my teeth, asking myself why you did such a
lousy job of defending the Right Way To Do It. When I started getting
doubts, I found myself reading youyr messages, hoping to find some good
arguments not to change my mind - but I only saw the same flawed
arguments over and over again. Often, I started to write a message - and
just as often, I held myself back. Asking myself: "Why bother?"
This time, I won't hold back. I still think that you might be defending
a very good point. But you do need to supply better arguments. If you
can't - well, I guess that I'll than have to conclude that I was right
in changing my mind about surrogate keys...
Awaiting your reply!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:uotSpGTkFHA.3756@.TK2MSFTNGP15.phx.gbl...
> Tilt that windmill Don Celko.
>
> Thomas
>
Snipe that Celko, Thomas.
Jeremy|||Hugo Kornelis wrote:
> I don't know what the original poster does or doesn't know, so let's
> forget the ad-hom. But please show me how a design like below would
> require your insanely expensive intervention. How would the use of the
> IDENTITY property screw up my integrity in this case?
> CREATE TABLE Products (ProductID int NOT NULL IDENTITY PRIMARY KEY,
> UPC char(10) NOT NULL UNIQUE,
> -- other columns,
> );
> CREATE TABLE Orders (OrderNo int NOT NULL PRIMARY KEY,
> -- other columns,
> );
> CREATE TABLE OrderProducts (ProductID int NOT NULL,
> OrderNo int NOT NULL,
> Amount int NOT NULL CHECK (Amount > 0),
> -- other columns,
> PRIMARY KEY (ProductID, OrderNo),
> FOREIGN KEY (ProductID)
> REFERENCES Products(ProductID),
> FOREIGN KEY (OrderNum)
> REFERENCES Orders(OrderNum),
> );
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hugo,
Always enjoy your posts. How about this for your example - I'll use
natural keys instead of IDENTITY keys (I admit I use IDENTITY alot in my
DBs, though I don't set them as PKs. Set them as UNIQUE instead):
CREATE TABLE Products (UPC char(10) NOT NULL PRIMARY KEY,
-- other columns,
);
CREATE TABLE Orders (OrderNo int NOT NULL IDENTITY UNIUQE,
order_date DATETIME NOT NULL,
customer_id int NOT NULL,
-- other columns,
PRIMARY KEY (order_date, customer_id)
);
The PK in Orders assumes only 1 order per customer per date (just for
example's sake) ;-). OrderNo acts as a surrogate key (as I understand
surrogates).
CREATE TABLE OrderProducts (UPC char(10) NOT NULL
FOREIGN KEY REFERENCES Products (UPC)
ON DELETE CASCADE
ON UPDATE CASCADE,
OrderNo int NOT NULL
FOREIGN KEY REFERENCES Orders(OrderNo)
ON DELETE CASCADE
ON UPDATE CASCADE,
OrderAmount int NOT NULL CHECK (Amount > 0),
-- other columns,
PRIMARY KEY (UPC, OrderNo),
);
The reason I use OrderNo as a surrogate key is to avoid this:
CREATE TABLE OrderProducts (UPC char(10) NOT NULL
FOREIGN KEY REFERENCES Products (UPC)
ON DELETE CASCADE
ON UPDATE CASCADE,
order_date DATETIME NOT NULL
FOREIGN KEY REFERENCES Orders
ON DELETE CASCADE
ON UPDATE CASCADE,
customer_id int NOT NULL
FOREIGN KEY REFERENCES Orders
ON DELETE CASCADE
ON UPDATE CASCADE,
OrderAmount int NOT NULL CHECK (Amount > 0),
-- other columns,
PRIMARY KEY (UPC, order_date, customer_id),
);
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuVX1IechKqOuFEgEQJs2QCfVP9Cy7NKU6YR
T96fVlZ/uN71dSQAn2OL
KN52zWrCb83lQKGiTvlJuBq5
=voUL
--END PGP SIGNATURE--
Monday, March 19, 2012
How can I made SQL modifications that I can do at EM ?
Hi all, this probably is a stupid question, but here I go.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.
My problem is that this table has a lot of foreign keys (other table reffer to this table and this table reffer to other tables) I have been trying to use profiler but I doesn't understand its results. I will try to see the users help.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.
Kind Reggards
Dirceu
IDENTITY
Specifies that the new column is an identity column. When a new row is added to the table, SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) column. Only one identity column can be created per table. The DEFAULT keyword and bound defaults cannot be used with an identity column. Either both the seed and increment must be specified, or neither. If neither are specified, the default is (1,1).
- Seed
- Is the value used for the first row loaded into the table.
- Increment
- Is the incremental value added to the identity value of the previous row loaded.
You can add an identity column by using
alter table <table name>
add <col_name> bigint IDENTITY (1, 1)
go
My problem is that this table has a lot of foreign keys (other table reffer to this table and this table reffer to other tables) I have been trying to use profiler but I doesn't understand its results. I will try to see the users help.
Thanks
Friday, March 9, 2012
How can I INSERT w/Constraints in other tables?
Three tables (all new, no data) will receive data from dBase and be
transposed into them...All three have auto generated IDENTITY columns
and pk and fk constraints. Can someone provide me with an
understandible sample?
Thanks,
TrintThis example may help. Assuming your tables look like this:
transposed into them...All three have auto generated IDENTITY columns
and pk and fk constraints. Can someone provide me with an
understandible sample?
Thanks,
TrintThis example may help. Assuming your tables look like this:
CREATE TABLE Foo (foo_id INTEGER IDENTITY PRIMARY KEY, foo_key
VARCHAR(10) NOT NULL UNIQUE)
CREATE TABLE Bar (bar_id INTEGER IDENTITY PRIMARY KEY, bar_key
VARCHAR(10) NOT NULL UNIQUE, foo_id INTEGER NOT NULL REFERENCES Foo
(foo_id))
And assuming you have a similar source structure from dBase, here are
the INSERTs:
INSERT INTO Foo (foo_key)
SELECT DISTINCT foo_key
FROM Foo_source
INSERT INTO Bar (bar_key, foo_id)
SELECT DISTINCT B.bar_key, F2.foo_id
FROM Bar_source AS B
JOIN Foo_source AS F
ON B.foo_id = F.foo_id
JOIN Foo AS F2
ON F.foo_key = F2.foo_key
For this to work you do of course need the alternate key ("foo_key" in
this case). That shouldn't be a problem. IDENTITY should never be the
only key of a table.
--
David Portas
SQL Server MVP
--|||David,
Here are the two tables...can you help in using the example with them?::
CREATE TABLE [tblTravelDetailMember] (
[TravelDetailUplineId] [int] NOT NULL ,
[TravelDetailId] [int] NOT NULL ,
[TravelDetailMemberTypeId] [int] NOT NULL ,
[memberId] [bigint] NOT NULL ,
[rankId] [int] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMember] PRIMARY KEY CLUSTERED
(
[TravelDetailUplineId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetail] FOREIGN KEY
(
[TravelDetailId]
) REFERENCES [tblTravelDetail] (
[TravelDetailId]
),
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetailMemberType ] FOREIGN
KEY
(
[TravelDetailMemberTypeId]
) REFERENCES [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId]
)
) ON [PRIMARY]
GO
------
CREATE TABLE [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId] [int] NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMemberType] PRIMARY KEY CLUSTERED
(
[TravelDetailMemberTypeId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I get this error with this code:
string strSQL2 = "INSERT INTO tblTravelDetailMember(memberId, " +
" TravelDetailUplineId, " +
" rankId, " +
" TravelDetailId, " +
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + insertString2 + "', " +
" '" + insertString3 + "', " +
" '" + insertString4 + "', " +
" '" + insertString5 + "', " +
" '" + insertString6 + "', " +
" '" + insertString7 + "')";
Thanks,
Trint
Net programmer
trinity.smith@.gmail.com
*** Sent via Developersdex http://www.developersdex.com ***|||You didn't specify what error message you are getting. You didn't
include the alternate keys or the IDENTITY properties that you said you
had. The only obvious problem with your INSERT is that you've missed
out one of the columns ("TravelDetailMemberTypeId").
Put the INSERT in a stored procedure rather than construct a SQL string
in your app. That way you can test and check the code more easily.
If you only want to insert rows one at a time then you should be able
to make use of the SCOPE_IDENTITY() function to do this. See Books
Online for details. I was assuming you would load all the data with one
INSERT per table.
--
David Portas
SQL Server MVP
--
how can I insert !
I have a following table :
CREATE TABLE [dbo].[defaultvalue] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[flag] AS ([id] + 1)
) ON [PRIMARY]
GO
I want to insert the data in it, how can I insert ?
Thanks
NOOR
Hi,
Use the DEFAULT VALUES along with insert statement.
insert into dbo.defaultvalue DEFAULT VALUES
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> I have a following table :
> CREATE TABLE [dbo].[defaultvalue] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [flag] AS ([id] + 1)
> ) ON [PRIMARY]
> GO
> I want to insert the data in it, how can I insert ?
> Thanks
> NOOR
>
|||Hi,
Use the DEFAULT VALUES along with insert statement.
insert into dbo.defaultvalue DEFAULT VALUES
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> I have a following table :
> CREATE TABLE [dbo].[defaultvalue] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [flag] AS ([id] + 1)
> ) ON [PRIMARY]
> GO
> I want to insert the data in it, how can I insert ?
> Thanks
> NOOR
>
|||Thanks Hari
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23zRneCcXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the DEFAULT VALUES along with insert statement.
> insert into dbo.defaultvalue DEFAULT VALUES
> --
> Thanks
> Hari
> MCDBA
> "Noor" <noor@.ngsol.com> wrote in message
> news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks Hari
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23zRneCcXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the DEFAULT VALUES along with insert statement.
> insert into dbo.defaultvalue DEFAULT VALUES
> --
> Thanks
> Hari
> MCDBA
> "Noor" <noor@.ngsol.com> wrote in message
> news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
>
CREATE TABLE [dbo].[defaultvalue] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[flag] AS ([id] + 1)
) ON [PRIMARY]
GO
I want to insert the data in it, how can I insert ?
Thanks
NOOR
Hi,
Use the DEFAULT VALUES along with insert statement.
insert into dbo.defaultvalue DEFAULT VALUES
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> I have a following table :
> CREATE TABLE [dbo].[defaultvalue] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [flag] AS ([id] + 1)
> ) ON [PRIMARY]
> GO
> I want to insert the data in it, how can I insert ?
> Thanks
> NOOR
>
|||Hi,
Use the DEFAULT VALUES along with insert statement.
insert into dbo.defaultvalue DEFAULT VALUES
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> I have a following table :
> CREATE TABLE [dbo].[defaultvalue] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [flag] AS ([id] + 1)
> ) ON [PRIMARY]
> GO
> I want to insert the data in it, how can I insert ?
> Thanks
> NOOR
>
|||Thanks Hari
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23zRneCcXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the DEFAULT VALUES along with insert statement.
> insert into dbo.defaultvalue DEFAULT VALUES
> --
> Thanks
> Hari
> MCDBA
> "Noor" <noor@.ngsol.com> wrote in message
> news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks Hari
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23zRneCcXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the DEFAULT VALUES along with insert statement.
> insert into dbo.defaultvalue DEFAULT VALUES
> --
> Thanks
> Hari
> MCDBA
> "Noor" <noor@.ngsol.com> wrote in message
> news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
>
How can I increase the range of an identity column
Hi guys
Im new at SQL Server and I have a problem with an identity column
the max value for the identity column has been reached I dunno if I can increased by an alter table or I need to recreate the table ?
the table has foreign keys
Thanks in advancedYou could go to the next larger datatype but I suspect your foreign keys will start to bomb out. What was the data type of your identity column and what was the original seed?
Consider the following:
create table Tmp(f1 int not null identity(2147483647,1), f2 varchar(5))
go
insert into Tmp (f2) values('ABC')
select * From Tmp
go
raiserror('Try to squeze one more row.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
raiserror(' ',0,1) with nowait
raiserror('change the seed.',0,1) with nowait
DBCC CHECKIDENT (Tmp, RESEED, -2147483648)
go
raiserror(' ',0,1) with nowait
raiserror('Insert more rows.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
insert into Tmp (f2) values('GHI')
select * From Tmp
go
sp_help Tmp
go
drop table Tmp
go
Im new at SQL Server and I have a problem with an identity column
the max value for the identity column has been reached I dunno if I can increased by an alter table or I need to recreate the table ?
the table has foreign keys
Thanks in advancedYou could go to the next larger datatype but I suspect your foreign keys will start to bomb out. What was the data type of your identity column and what was the original seed?
Consider the following:
create table Tmp(f1 int not null identity(2147483647,1), f2 varchar(5))
go
insert into Tmp (f2) values('ABC')
select * From Tmp
go
raiserror('Try to squeze one more row.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
raiserror(' ',0,1) with nowait
raiserror('change the seed.',0,1) with nowait
DBCC CHECKIDENT (Tmp, RESEED, -2147483648)
go
raiserror(' ',0,1) with nowait
raiserror('Insert more rows.',0,1) with nowait
insert into Tmp (f2) values('DEF')
select * From Tmp
go
insert into Tmp (f2) values('GHI')
select * From Tmp
go
sp_help Tmp
go
drop table Tmp
go
Subscribe to:
Posts (Atom)