Creating table with Identity property

Hi, if I want to create a table with IDENTITY(1,1) property and do not
state any PK or simular constraints. Does that IDENTITY serve as a
unique identifier to that tabel or is it nessecary to implement some
constraints as well'
I'm new to all this with SQL and trying to learn about it!
Ex:
CREATE TABLE Employee
{
EMPID int NOT NULL IDENTITY(1,1),
EMPNAME varchar(8) NOT NULL,
EMPADDRESS varchar(30) NULL
}zekevarg skrev:

> Hi, if I want to create a table with IDENTITY(1,1) property and do not
> state any PK or simular constraints. Does that IDENTITY serve as a
> unique identifier to that tabel or is it nessecary to implement some
> constraints as well'
>
The IDENTITY property does not impose any constraints on its own, you
can do a search on Google for IDENTITY in this news group and you'll
get a lot to read :)
/impslayer, aka Birger Johansson|||zekevarg wrote:
> Hi, if I want to create a table with IDENTITY(1,1) property and do not
> state any PK or simular constraints. Does that IDENTITY serve as a
> unique identifier to that tabel or is it nessecary to implement some
> constraints as well'
> I'm new to all this with SQL and trying to learn about it!
> Ex:
> CREATE TABLE Employee
> {
> EMPID int NOT NULL IDENTITY(1,1),
> EMPNAME varchar(8) NOT NULL,
> EMPADDRESS varchar(30) NULL
> }
I answered this in the thread you started yesterday:
http://groups.google.co.uk/group/mi...372625141cc0196
You should always add a UNIQUE or PRIMARY KEY constraint to an IDENTITY
column if you want it to be unique. Typically IDENTITY will be declared
as the primary key and used as the referenced column in foreign key
constraints.
However, even where you do put a key constraint on an IDENTITY column
it shouldn't be the ONLY key of a table. That's because IDENTITY won't
prevent duplicate data in the table. Also it isn't a good idea to
expose IDENTITY columns to end users (one reason being that you can't
ever update the column) - therefore if IDENTITY is the only key your
users will not have a useable key at all.
That's my opinion. It's true that many people will have differing
opinions on this topic. I would urge you however to read and understand
relational design theory and principles such as normalization rather
than just listen to advice in newsgroups (including my own). You can
make better decisions and implement better solutions if you understand
the fundamentals rather than basing your knowledge only on examples and
tips.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> if I want to create a table with IDENTITY(1,1) property and do not state
any PK or simular constraints. <<
Then **by definition** it is not a table. A key must be made up of a
unique subset of the atributes of the entity, not the internal state of
a counter in the hardware that stores the data.
No, it is an exposed physical locator -- like a record number was in a
1950's mag tape file system. Ask yourself if the IDENTITY would be
different if you inserted the same data in a different order (yes!0,
then ask yourself if you part numbers would stay the same irregardless
of the order of insertion (yes, of course!). That is because a part
number is a key and an IDENTITY is not.
There is no "as well" on this. You will never use an IDENTITY in a
properly designed RDBMS.
It is a whole different model of data and it will take you awhile to
get it. Think abstract, no physical; think sets and not sequences;
think declarative, not procedural (i.e. whar, not how) and think
parallel, not sequential.|||FWIW, I thought you should know that "irregardless" is not a proper
word in the english language.
check out: http://en.wiktionary.org/wiki/irregardless
We all expect perfection from you, as you do from us.|||On 16 Mar 2006 07:25:20 -0800, "JeffB" <jeff.bolton@.citigatehudson.com>
wrote:
in <1142522719.973281.149580@.j52g2000cwj.googlegroups.com>

>FWIW, I thought you should know that "irregardless" is not a proper
>word in the english language.
>check out: http://en.wiktionary.org/wiki/irregardless
>We all expect perfection from you, as you do from us.
While I agree with your pedantic point, regretfully the word
irregardless has crept into the language by virtue of its use. Ack!
As you can see in the article you've pointed to, its status has already
risen from not-a-word to non-standard. Another 10-15 years and it
~will~ be a word. :-(
This posting is provided "AS IS" with no warranties, no guarantees, and no c
onferred rights.
Stefan Berglund|||In some parts of the country, "irregardless" is the word used when folks
want to show off using a 50 cent word.
that's scary.
-Paul
"Stefan Berglund" <sorry.no.koolaid@.for.me> wrote in message
news:i08j12te8qbvmc0i57jlhq90jh5jg05d7f@.
4ax.com...
> On 16 Mar 2006 07:25:20 -0800, "JeffB" <jeff.bolton@.citigatehudson.com>
> wrote:
> in <1142522719.973281.149580@.j52g2000cwj.googlegroups.com>
>
> While I agree with your pedantic point, regretfully the word
> irregardless has crept into the language by virtue of its use. Ack!
> As you can see in the article you've pointed to, its status has already
> risen from not-a-word to non-standard. Another 10-15 years and it
> ~will~ be a word. :-(
> --
> This posting is provided "AS IS" with no warranties, no guarantees, and no
> conferred rights.
> Stefan Berglund|||>> Think abstract, no physical
I like the white board as much as anyone, but just remember that abstract
has never held any actual data. Every data table that has actually had a
practical use has been, and will always be, a physical table. The relational
abstract at some point has to be converted into the physical SQL.
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142521746.581278.276740@.u72g2000cwu.googlegroups.com...
> Then **by definition** it is not a table. A key must be made up of a
> unique subset of the atributes of the entity, not the internal state of
> a counter in the hardware that stores the data.
>
> No, it is an exposed physical locator -- like a record number was in a
> 1950's mag tape file system. Ask yourself if the IDENTITY would be
> different if you inserted the same data in a different order (yes!0,
> then ask yourself if you part numbers would stay the same irregardless
> of the order of insertion (yes, of course!). That is because a part
> number is a key and an IDENTITY is not.
>
> There is no "as well" on this. You will never use an IDENTITY in a
> properly designed RDBMS.
>
> It is a whole different model of data and it will take you awhile to
> get it. Think abstract, no physical; think sets and not sequences;
> think declarative, not procedural (i.e. whar, not how) and think
> parallel, not sequential.
>|||Paul Nielsen (MVP) wrote:
> I like the white board as much as anyone, but just remember that abstract
> has never held any actual data. Every data table that has actually had a
> practical use has been, and will always be, a physical table. The relation
al
> abstract at some point has to be converted into the physical SQL.
A table is part of the logical data model, not the physical one. DATA
has to have a physical representation in storage of course, but the
principle of Physical Data Independence means that there is absolutely
no reason why a table itself has to have a direct physical counterpart.
The only requirement is that the DBMS can construct the results of
queries using persisted data and metadata.
SQL Server's data structures are such that (loosely speaking) you can
point to a set of pages in files and say "that's a table" but the same
isn't true in all other SQL databases (column-based storage is one
alternative for example).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--