About Primary Keys in SQL Server Express 2005 2

[Goondu]

Access 2000 MDB with SQL Server Express 2005.

I have upsized my Access database into SSE2005. I had search the net and they say SQL Server needs Primary Keys. Not much details were given and why.

I have a Function to delete/create new link tables in Access which is connected to SSE2005. But the linked tables were unable to insert new records. However, if I do a connection from a named DSN ODBC administrator, it asked for a unique identifier and it worked.

Before I go mucking around my tables in SSE2005. I like to know, do I really need to create a Primary Key – Data Type UniqueIdentifier – RowGuid Yes in every tables in my database.

Now, I know what is a primary/foreign key as I already have it in my Access database. In Access, I don’t need every table to have a Primary Key, just an Index will do.

Now, it seems that SSE2005 requires you to have a Primary Key in every Table. The table was unable to insert a new record if there wasn’t a Primary Key. I have existing Primary Key in my Main Table…fine, no problem. But the Child Tables (one to many) had a Field/Column Index – create the relationship, no luck. Until I created a new Primary Key in each Table, now I can add new records.

Second question, is there a way to code using ODBC connection to create a unique identifier? All I see on the net are codes creating linked tables without the unique identifier and there is no way to create one during the linking. You could create one after the linking but that wouldn’t work.

Anyone got a link or a solution or idea?

 

[dhookom]

IMO, every table should have a primary key. You may get by with a unique index. The field doesn't need to be any specific data type. You can specify a unique field when linking the table or even use a DDL query to create a unique index against the linked SQL table.

Duane
Hook'D on Access
MS Access MVP

 

[Goondu]

Thank, Duane.

Unless the table is empty but not with existing records.

I'm not sure if SSE2005 supports creating Foreign Keys. Guess I have more to read on. Help seems to say you can create it.

 

[dhookom]

SQL Server Express supports nearly everything SQL Server does.
I don't understand "Unless the table is empty but not with existing records".


Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

If you do not define some sort of primary key, SQL Server will bug out on you all over the place. The work around is that if you don't think you need a primary key, add an Auto Increment Field called RecordNo (if you are creating the table in Access, if you are creating it is SQL Server create a field called RecordNo and set it's Identity property to True), and set that as the primary key. The table will generate a unique record number for each record and essentially key itself any time a record is added.

 

[Goondu]

Duane,

Can't create a primary key with any datatype if there are records in the table except the one method I posted earlier. SSE2005 rejects due to nulls in the primary key.

Thanks for the input vbajock.

Help doesn't indicate why it is needed. Might as well go back to Access to create a primary key in each table and upsize it into SSE2005.

 

[dhookom]

Goondu,
A primary key suggests every record has unique values in this field or fields. Why would you expect or want to create a primary key on a field that could be null?

IMO, every table whether Access, SQL Server, Oracle, or whatever should have a primary key. If you can't identify a stored value, you can add an autonumber or identity column.

Duane
Hook'D on Access
MS Access MVP

 

[Goondu]

Duane,

Ahhh.....I am beginning to see your idea/concept. So you're saying create a unique index for the identity column first. Second, fill up the column with data than convert it to a primary key?

I wasn't expecting nulls from a primary key, not that I want it to be Null. It's just that you can't create a PK with nulls with existing records.

 

[vbajock]

No. If you are doing all of this from the Upsizing Wizard:

1) Create an empty table that has all the fields in it that the table up wish to upsize has

2) Add a field to this empty table structure called RecordNo, type Autonumber

3) Make the RecordNo field the Primary Key

3) Do an Append query, appending your source table's data to this empty table structure. Your Primary Key will update itself, do nothing to it

4) Upsize your new keyed table to your SQL Server db

But Duane is the one who is, technically speaking, the one who is really right here, your table should have some set of fields which make each record unique that should form the natural key to each record.

 

[vbajock]

Sorry for the typo, step 1 should read

1) Create an empty table structure that is a clone of the table you wish to upsize

 

[Goondu]

vbajock, Thanks for the guide.

Ok, will do that in Access. So this comes to a question.

For this new PK, do I need to create a relationship or just leave without?

 

[dhookom]

You might want to create a relationship using your primary key if it is related to a value in another table.

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Most of the time,for example, Autoincrement and Identity fields are used in a header table to generate something like an Invoice Number or a Work Order Number, which is then used as part of the Primary Key of a data table that contains detail records of the Invoice or WO, and this forms the basis for setting up the relationships among a set of tables. In this case, you have some sort of set of repeating data that really has none of those attributes as far as we know from the information you have given us, so I doubt you would use it in any relationship unless you intend to update other tables with this key value in order to establish a link.

 

[Goondu]

Duane and vbajock, Thank you for the additional input.

I only have two custom PK for two parent tables in a "One to Many" relationship. A couple of Tables where PK is not required but is "Index and Unique" and have relationship with Child Tables. The rest are stand alone Tables without any relationship.

One denormalized Table which at present is still subject to future changes.

For Tables with "Index and Unique" and without PK, I can convert them so that not a problem.

Only one Table cannot have a PK due to the repeated data as they require this same identity in the Table (this is another,not the previous one). I could do a split but since it does not interfere or cause any problems as it does not have any relationship in the Database. The identity a stand alone to identify a repeated record when the Users can see and choose to insert an item for dispatch. Normalization is not required in this case as there are less or between 10 to 30 records not more even in the future. I'll just put a PK in this Table. This Table was originally normalized but because of the unusual requirement in the daily operation environment and past practice. It was causing confusion to the Users and Finance Dept and that means that the Users have to recompile records into Excel manually as the Identity can no longer be the same as the old records.