Is the primary key manually entered? If it is, then I don't think it's a good idea. What if there are 1 million records already on the database and every time the user keys in the primary key, there's already an existing one on the database? I would suggest that you change the properties of your primary key such that its automatically generated on the database(GUID or autonumber). In some cases where your primary key needs to comply to a specific format, you can also create some code that automatically generates it. And then on that code, you include the validation of the key by selecting if the key exist on the database or not.
[Edit]
To answer your question, there's no autonumber data type but you can make an int to auto increment whenever there's a new record inserted. Its called an
Identity column[
^]. You can either follow the instructions on the link, or modify your column in SQL Server Management Studio. When you modify your table and click on a column, you can see the column properties, usually below. Expand the
Identity Specification
item under the Table Designer group and then set
(Is Identity)
to yes. Given that Identity Increment and Identity Seed are set to 1, you're all set after you save your changes. However, I don't think this will work if you have existing data on your table. I think you should recreate your table, with the identity column, and then transfer the data from your old table. Make sure you have a backup before doing this.