Create auto increment primary key in Sql Server
Bill
every time a new records is inserted into that table?
Thanks for your help,
Bill
DFS
news:8da5f4f4.03040...@posting.google.com...
Bill,
Identify the PK column as an IDENTITY column (I think it has to be a number
type).
CREATE TABLE Table1
( Field1 int IDENTITY NOT NULL,
Field2 varchar(50) NOT NULL,
....
);
ALTER TABLE Table1
ADD CONSTRAINT PK_Table1 PRIMARY KEY
( Field1 );
Check the books online for more details.
BP Margolin
Perhaps a minor simplification ... one can add the PRIMARY KEY constraint within the CREATE TABLE command:
CREATE TABLE Table1
( Field1 int NOT NULL IDENTITY primary key,
Field2 varchar(50) NOT NULL,
...
)
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"DFS" <wo...@night.net> wrote in message news:v99aiu5...@corp.supernews.com...
William Zimmerman
That worked. Thank you.
Bill
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
DFS
Yes, I knew about that (though I wasn't sure of the syntax).
I like the separate ADD CONSTRAINT statement because I can name the PK
myself.
(side question - you're a SQL Server MVP, right? Do you make money here
doing online tech support?)
Thanks
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:3e94b...@news1.prserv.net...
BP Margolin
One can name a constraint within a CREATE TABLE, for example:
CREATE TABLE Table1
( Field1 int NOT NULL IDENTITY CONSTRAINT PK_Table1 PRIMARY KEY,
Field2 varchar(50) NOT NULL,
...
)
> you're a SQL Server MVP, right?
Yup, I'm a SQL Server MVP ... BTW, don't judge all SQL Server MVP's by me ... some of the SQL Server MVP's actually know what they're talking about :-)
> Do you make money here
> doing online tech support?)
Don't I wish
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"DFS" <wo...@night.net> wrote in message news:v99klbf...@corp.supernews.com...
DFS
One can name a constraint within a CREATE TABLE, for example:
CREATE TABLE Table1
( Field1 int NOT NULL IDENTITY CONSTRAINT PK_Table1 PRIMARY KEY,
Field2 varchar(50) NOT NULL,
)
*** good to know
Yup, I'm a SQL Server MVP ... BTW, don't judge all SQL Server MVP's by me
... some of the SQL Server MVP's actually know what they're talking about
:-)
*** You're very, very knowledgeable... you've helped me a few times.
> Do you make money here
> doing online tech support?)
Don't I wish
*** Thanks for the good job you're doing.
Dag Arne Matre
> "BP Margolin" <bpm...@attglobal.net> wrote in message
What about:
CREATE TABLE Table1
( Field1 int NOT NULL IDENTITY,
Field2 varchar(50) NOT NULL,
CONSTRAINT PK_Table1 PRIMARY KEY (Field1)
)
as a compromise and you guys can shake hands?
D Newton
Are there any drawbacks to use IDENTITY? There was some internal discussion in my group recently
about this, apparently it causes some problems when migrating data between the same table in
multiple databases (like going from development to production, and you want to preserve the
existing data's key values...)
"DFS" <wo...@night.net> wrote in message news:v99aiu5...@corp.supernews.com...
DFS
any. If you need to preserve the existing key values, you can use the SET
IDENTITY_INSERT tblName ON option when migrating data.
"D Newton" <nos...@aol.com> wrote in message
news:b7ee71$pnt$1...@ginger.mathworks.com...