Question Check if a table already has a Primary Key

[Runescope]

So I'm fixing a table from someone else, and they never put primary keys in all of their tables. I've figured out how to put a primary key into the table in my program, but now I want to check if the primary key has been set already so I don't try to set it twice, resulting in an error.

I'm using VB.net 2010 if anyone can give me an idea how to do this, that would be appreciated.

 

[IanRyder]

Hi,

The one important thing you forgot to mention is what type of Database you are working with. If this is SQL Server then you can use the following:-

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
    CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TABLE_NAME = 'YourTableName' 
    AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[YourTableName] ADD CONSTRAINT [PK_YourIndexName] PRIMARY KEY CLUSTERED  ([YourPrimaryKeyField])
END

All you need to do now is replace the variables YourTableName, PK_YourIndexName and YourPrimaryKeyField with the correct names in your own Database.

Hope that helps.

Cheers,

Ian

 

[Runescope]

Ahhhh, I didn't think it would be necessary to say which type of database I was working with, as I was posting in the MS Access section. But anyways, I'm working with MS Access.

 

[IanRyder]

Ahhhh, I didn't think it would be necessary to say which type of database I was working with, as I was posting in the MS Access section.

ha, ha, and my apologies, I must still be half asleep, Doh!

Its been while now since I have done this type of thing in Access and I have certainly not done this through Visual Studio. In my VBA days however, I would have achieved this using the TableDefs collection to loop through the tables and checking for a Primary Key and then use Create Index to set a primary key where needed.

I cannot remember the specific code of the top of my head so you may well need to Google these topics to generate your final solution.

Hope that helps.

Cheers,

Ian

 

[Runescope]

I spent 4 hours today googling it with no luck lol Not sure if my google-fu was weak today or what, but I couldn't find a single thing that worked.

 

[JohnH]

Using OleDbDataAdapter.FillSchema method and checking DataTable.PrimaryKey property is an option.

 

[Runescope]

Hi JohnH,

I think your explanation went over my head a bit. I don't recognize that method at all. :ambivalence: I'll start looking for it on google to learn about it, but if you know of an example of how to use it, that would be great!

Edit: Some preliminary searching shows that this is for a bound database, mine is unbound.

 

[Runescope]

Allrighty! I found what was needed, but now I have a separate problem, should I post it into a different thread?

Here's the solution I found anyways.

I found this function:

Public Shared Function getKeyNames(tableName As [String], conn As DbConnection) As List(Of String)
    Dim returnList = New List(Of String)()


    Dim mySchema As DataTable = TryCast(conn, OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New [Object]() {Nothing, Nothing, tableName})


    ' following is a lengthy form of the number '3' :-)
    Dim columnOrdinalForName As Integer = mySchema.Columns("COLUMN_NAME").Ordinal

    For Each r As DataRow In mySchema.Rows
        returnList.Add(r.ItemArray(columnOrdinalForName).ToString())
    Next

    Return returnList
End Function

This successfully checks if the specified table 'RegTable' has a primary key, the function returns 0 if it doesn't and more than zero based on how many keys there are (more than one seems odd to me, but there must be a reason).

 

[JohnH]

Hi JohnH,

I think your explanation went over my head a bit. I don't recognize that method at all. :ambivalence: I'll start looking for it on google to learn about it, but if you know of an example of how to use it, that would be great!

Edit: Some preliminary searching shows that this is for a bound database, mine is unbound.

It has nothing to do with data-binding. You just create a OleDbDataAdapter object and call its FillSchema method to fill the schema information to an empty DataTable object, then get the columns from PrimaryKey property.

I have a separate problem, should I post it into a different thread?

Yes. I split that to new thread: http://www.vbdotnetforums.com/ms-access/56960-make-field-text-field.html

 

[Runescope]

Thanks JohnH,

Yeah, after reading up on it for a bit I realized how silly that was with the bound/unbound. Which lead me to the function I found, so all good! And thank for splitting that other problem into a new thread, I do appreciate it.

Edit: Hmmm, for some reason I can't edit this to say resolved. Can you?