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
--

Creating referential integrity constraints

Two ways to do this... Creating the constraints when creating the data model OR using SQL to use the 'reference' constraint. Does 2005 provide any other automated method of creating the Primary Key - to Foreign key constraints without writing the SQL to do this?

Thx

What do you mean by automated method? Using DDL is not automated enough? You could use SMO to create the constraints but you have to write VB.NET or C# code. You can also use the GUI but I wouldn't recommend that because it can mess up your tables depending on what you are doing. For example, it can drop and recreate tables depending on the changes you made in the diagram designer. And this may or may not be the right thing to do. Often using SQL DDL is the most direct and optimized way to make schema changes.

Another option is to use a modelling tool like Visio or ErWin or Power Designer. Those have capabilities to generate scripts to create the schema and/or make changes. Note that the features supported by the tools may differ from the database engine. For example, using included columns or indexing computed columns will be a problem. Similarly, the tools might allow defining cascading foreign key relationships even if there are cycles for example but if you try to apply those changes in SQL Server it will fail because the database engine lacks supports for those. On the other hand, if you are trying the same against Oracle it will succeed. So if you have complex schema relationships then you will have to use a hybrid approach. Or if you support different database engines you will have to tailor the scripts to the specific engine. Visio for example allows you to define additional scripts in the model that can be executed againt the database engine.

|||

Thank you for your answer, I didn't know that the GUI would do the things that you described. That was what I was referring to. I guess for me, the automated method would be that when you create a primary key, the create the foreign key in the 'create table' GUI, the foreign key constraint would be automatically created if the column names are the same. Does this make sense? Maybe coffee is the answer.

As you mentioned above, there are products that do provide this automation, I just wondered if the 2005 engine had included this feature.

Thx

|||

katgreen777 wrote:

the create the foreign key in the 'create table' GUI, the foreign key constraint would be automatically created if the column names are the same. Does this make sense?

It makes sense to some extent. But you can't just go by column names. You could have "Description" column in multiple tables which have no relation to each other except for the fact that they represent some sort of description of an entity. This rule might however work for key attributes.

I don't know if SSMS diagram designer or VS designer infers these things from the model. You have to ask in the Tools or Visual Studio forum. Data modelling tools however does this for you depending on how you create the model. For example, if you drag and drop a key column from one table to another it will establish a relationship that will translate to referential constraint on the database. Of course, none of this comes free. You typically pay a lot for designer/modelling tools.

|||

One note on this... isn't it a rule of thumb not to include computed columns in a table?

thx

|||I don't know of any such rule. There are cases where using computed column is useful. But it is a proprietary feature anyway so for data modelling purpose probably you shouldn't worry about it. If you hit some design issues in SQL Server due to limitations you can use computed columns & indexes on computed columns.|||

The books I've been reading say that you shouldn't include computed columns in a table because of the extra space they take up, but instead, create a view where the computations take place. That was what I was referring to.

Kat

|||Wow! Which books are those? You need to throw them in the garbage. Computed column as the name implies is computed at run-time. There are ways to persist computed column values in SQL Server 2005 which will take up space or if you index computed columns. Otherwise, it is no different than defining a view with the same expression. Even with view the notion that computations take place within a view is incorrect. The view definition is parsed into the statement (SELECT or DML), compiled, optimized and executed.|||

Yes, you aren't the first person to say that about my books, any good ones you would recommend?

Kat