Post reply

Check if a primary keys exists

  • June 4, 2008 at 9:19 am

    #192001

    Hello all.

    OK i have script which is to be run on several databases. Within this script there are commands to create a primary key on a specific table. Can anyone tell me if it is possible to check if a specific primary key exists on a table?

    Thanks people.

  • June 4, 2008 at 9:24 am

    #824262

    IF EXITS (SELECT *

    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '..... your table name ....'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ---- Your code here

    END


    * Noel

  • June 4, 2008 at 9:32 am

    #824269

    Hello there. Thanks very much for taking the time to reply.

    OK..........ive taken your code and modified it as such:

    IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])

    GO

    PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    END

    Hoever this gives me the following errors:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'SELECT'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near ')'.

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near ')'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'END'.

    Thanks again for your help.

  • June 4, 2008 at 9:37 am

    #824273

    gavin.duncan (6/4/2008)

    Hello there. Thanks very much for taking the time to reply.

    OK..........ive taken your code and modified it as such:

    IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])

    GO

    PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    END

    Hoever this gives me the following errors:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'SELECT'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near ')'.

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near ')'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'END'.

    Thanks again for your help.

    You must remove the "GO"s


    * Noel

  • June 4, 2008 at 9:40 am

    #824275

    Hi. Once again thanks for your swift response 🙂

    OK..........i removed the 'GO's and the code i am left with is:

    IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED [ID])

    PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    END

    However this still leaves me with the following:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'SELECT'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near ')'.

    Many thanks again.

  • June 4, 2008 at 9:50 am

    #824287

    gavin.duncan (6/4/2008)

    Hi. Once again thanks for your swift response 🙂

    OK..........i removed the 'GO's and the code i am left with is:

    IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED [ID])

    PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    END

    However this still leaves me with the following:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'SELECT'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near ')'.

    Many thanks again.

    Your last Line has two "BEGIN" which is confusing the parser!!

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    should probably be:

    IF @@TRANCOUNT=0

    BEGIN

    INSERT INTO #tmpErrors (Error) SELECT 1

    COMMIT TRANSACTION ---<<< I am NOT sure why are you doing this though

    END


    * Noel

  • June 4, 2008 at 9:54 am

    #824292

    Hello noel..........again many thanks. However it would appear the error is definately in the first few lines. I stripped it back to the following:

    IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    PRINT N'TABLE HAS NO PRIMARY KEY'

    END

    And im still getting the following error:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'SELECT'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near ')'.

    Thanks again.

  • June 4, 2008 at 9:58 am

    #824299

    hehe :

    IF NOT EXITS

    should be:

    IF NOT EXISTS


    * Noel

  • June 4, 2008 at 10:01 am

    #824304

    Aaaaaaarrrrrgghh!!! :blush:

    Thank you very much noel. Much appreciated!

  • June 4, 2008 at 10:06 am

    #824309

    No problem ... it was MY typo 😀


    * Noel

  • June 4, 2008 at 10:07 am

    #824313

    Noooooo the saga continues!

    OK........ive made the changes and have ended up with the following:

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])

    PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'

    END

    Now by my understanding (which may well be wrong) the above should only create the primary key if it does not currently have one. However when i run the above for a second time i get the following errors indicating that the primary key already exists (which is correct) but it still tries to create it again :crying: :

    Msg 1779, Level 16, State 0, Line 5

    Table 'tblReturnVoucher' already has a primary key defined on it.

    Msg 1750, Level 16, State 0, Line 5

    Could not create constraint. See previous errors.

    Thanks again dude.

  • June 4, 2008 at 11:14 am

    #824357

    gavin.duncan (6/4/2008)

    Noooooo the saga continues!

    OK........ive made the changes and have ended up with the following:

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = '[tblReturnVoucher]'

    AND TABLE_SCHEMA ='dbo' )

    BEGIN

    ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])

    PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'

    END

    Now by my understanding (which may well be wrong) the above should only create the primary key if it does not currently have one. However when i run the above for a second time i get the following errors indicating that the primary key already exists (which is correct) but it still tries to create it again :crying: :

    Msg 1779, Level 16, State 0, Line 5

    Table 'tblReturnVoucher' already has a primary key defined on it.

    Msg 1750, Level 16, State 0, Line 5

    Could not create constraint. See previous errors.

    Thanks again dude.

    Remove the square brakets on the exists .... like:

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND TABLE_NAME = 'tblReturnVoucher'

    AND TABLE_SCHEMA ='dbo' )

    ....


    * Noel

  • June 5, 2008 at 2:05 am

    #824670

    Brilliant.

    Thanks again Noel..........much appreciated.

  • October 13, 2010 at 11:10 pm

    #1236628

    Same thing but this checks all tables for missing PKs:

    SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_NAME NOT IN

    (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C WHERE CONSTRAINT_TYPE = 'PRIMARY KEY')

    dbgurus.com.au

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy