SQL Server 2005 identity column creation

[twiggy]

Hi all,

I am having a bit of an issue with the DDL generation for my database creation.

It creates the .sql file which creates all the tables with the primary and foreign keys but for the life of me I can not figure out how to get it to assign some of the columns the "identity"

CREATE TABLE HumanResources.Employees(

  Id int IDENTITY(1,1) NOT NULL,

  -- Other columns

Is this possible?

[«Midnight»]

Save the column with the appropriate check boxes set. [You don't actually have to set everything first, but things will be easier.]

Once the column has been saved, click the Column Properties button and you'll get the dialog you need.

PS: Make sure you've set your database correctly in advance of this procedure.

[twiggy]

Hi Midnight,

Thank you for your quick reply.  I am afraid I don't follow you.  I have created a test project.  The default DB is sql server2005.  I created a table called testtable and a column called id which was of type int.  I have looked at its properties over and over and I can not see anywhere that says identity.

I am using Version: 7.0.814 Corporate Edition.

Sorry if I am being stupid with this but it is going right over my head.  I am very new to EA but think it seems to be a great tool.

[«Midnight»]

Just to be sure, I checked the EA 814 help file.

In the help index, look up Column | Create in Data Modeling. You'll see a graphic there, with a disabled (i.e. faded out) button labeled Column Properties; it is just below the Unique check box.

As I said earlier, you must provide a name and an appropriate data type for your column, and then save the column, before this button is enabled. Once you've done so and clicked the button you will get the appropriate dialog.

[Note that this dialog does not appear to be in the help file, or at least is not accessible via the index or search.]

BTW, you should also click the Primary Key hot link on the above help page - it is near the end of the first list item under section 4 - and then scroll down to the bottom of the target page and click the link there. This will help you with additional properties of SQL Server primary keys.

[twiggy]

Hi Midnight,
 
I believe I have done as you suggested but I do not get the identity column in the SQL, rather I get a trigger.  Please see the screen dumps below and the .SQL generated.
 
USE DATABASE aipoker
;
DROP TRIGGER SET_Table1_id;
 
DROP TABLE Table1
;
CREATE TABLE Table1 (
 id int NOT NULL
)
 
;
CREATE TRIGGER Table1_id_INC
  NO CASCADE BEFORE INSERT ON Table1
  REFERENCING NEW AS tbl
  FOR EACH ROW
  SET (tbl.id) = (SELECT VALUE(MAX(id),0) + 1 FROM Table1)
 
;
 
ALTER TABLE Table1
 ADD CONSTRAINT UQ_Table1_id UNIQUE (id)
;

[«Midnight»]

Well you seem to have found what I was talking about, as well as all the other possible 'culprits.' Why this is not generating the DDL is a bit of a mystery.

Here's the only thing that comes to mind immediately. You seem to be generating the DDL for a single table only. [There are two options in EA.] Try clicking on your diagram background - to make sure no tables are selected - and then generating DDL for the entire package. Do this from the EA main menu via Project | Database Engineering | Generate Package DDL. [Yes, I know it seems the same, but having a table selected can make things work differently.]

Do you get the sequence now? [Note that the DDL for the sequence may be elsewhere in the file.]

David

[twiggy]

Hi Midnight,

OMG I wish this would work!! :p

Thank you for all your help and support.

I have tried generating it from the single table as shown in my pics and also from the main menu where you can select one, some and all the tables.  

Which ever way I do it, it creates a trigger instead of the identity.  If I untick the auto number it just says the unique field.

I have tried it with and without the primary key set.

As far as I can tell I have tried every way of doing it and it always creates triggers or just says unique.

I even tried creating the table in SQL Server 2005 with the identity column set and import that into EA and then create the SQL statement from that.  It also did not include anything that would set the identity.

I am so confused!!!

[«Midnight»]

Hi Midnight,

OMG I wish this would work!! :p

I have tried generating it from the single table as shown in my pics and also from the main menu where you can select one, some and all the tables.  
...
I am so confused!!!

Hi Twiggy,

I've had time to verify this, and it does work. My test produced the following (extracted from a larger file):

CREATE TABLE [Table3] (
      [id] int identity(1,1)  NOT NULL
)
;

Please reread my earlier post. You must have none of the tables selected on your diagram, and generate the DDL from the Project menu.

How's it working now?

David

[twiggy]

Hi David,

this is madness.  I have ensured (multiple times) that I do not have the table selected, I have tried generating the code from both right clicking and project menu at the top.

I have had a look through all the option menus and could not see anything there that suggested it was configured wrong.

I may try and find some quick screen video capture / or do a load more screen dumps tonight to show the whole process I am doing.  But I am so sure I am doing just as you have described.

Also my SQL output does not have the brackets around (not sure if thats just a setting you have thats different or something significant).

Can I confirm the following:

1) should the primary key be set to true or false?

2) NOT null should be set to true?

3) unique should be set to true?

4) the column properties box:
  ---4a) should the auto generate be set to true or false?
  ---4b) should there be values in the value and increment?

Sorry for sounding rather stupid (am not normally that crap), but I can not see where I am going so wrong with this.

Thank you again for your patience and help

Cheers

Jon

(will post video or screen dumps tonight, need access to my server to get the pics to work).

[«Midnight»]

Not a problem Jon, and you're not being slow on this. It is certainly possible to have DDL generation do some strange things - or completely fail to do what's expected and wanted - if you don't have everything set just right.

Send me a PM message with your address and we'll see if we can move this along faster, then post the (hopefully successful) end result.

In the meantime, here's how I had my table set:

• The table is using SQL Server 2005 (obviously, but let's be completely clear)
• There is a single field named id, of type int; for this field:
  • The Primary Key box is checked, the Not Null box was checked automatically and disabled by EA, and the Unique box is not checked
  • On the Column Properties dialog the Auto Number is checked, and both Start Value and Increment are set to 1
• These options are set on the Generate Package DDL dialog:
  • Comment level is set to none and the associated text boxes are cleared (this will make no difference in the case we're discussing here)
  • All the check boxes in the top group set are checked
  • The Use ... as SQL terminator is set to ";" (without quotes)
  • The Use ... and ... around names option is checked and the text boxes are set to "[" and "]"
  • The Generate Table Owner option is checked (which should not affect your DDL in this case)
  • The final two options are cleared (again, this should make no difference)
  • The File Generation option is set to Single File and a file name is specified (EA used to attempt to generate and get no results if you failed to set an output file; I don't know if this would still occur)
  • All the tables in my diagram are highlighted in the Select Objects to Generate list (which is the default when you invoke the dialog as we've discussed

Try setting up this way and let me know.

David

[twiggy]

When I followed what you said It checks the unique box automatically (don't seem to be able to uncheck it).

It produced this SQL

DROP TRIGGER SET_Table1_id;

DROP TABLE [Table1]
;



CREATE TABLE [Table1] (
      [id]  NOT NULL
)

;
CREATE TRIGGER Table1_id_INC
  NO CASCADE BEFORE INSERT ON Table1
  REFERENCING NEW AS tbl
  FOR EACH ROW
  SET (tbl.id) = (SELECT VALUE(MAX(id),0) + 1 FROM Table1)

;


ALTER TABLE [Table1] ADD CONSTRAINT [PK_Table1]
PRIMARY KEY ([id])

;

Will send a PM in a moment

Thanks