Hello all
I have a few questions and was hoping I could find answers here:

1- I just created a database and added a table called computers with ComputerID as the primary key; this field is also an identity, with seed = 1, and increment = 1. My problem is that, when I insert a new computer record (Processor, SN, etc.) using a VB application I wrote, it allows me to have duplicate records, so I have 2 computers with the same values for all fields, including the primary key field, and it doesn't throw an exception or anything. From my little experience working with databases, I understand that a PK is unique automatically, is it not? How do I fix this?

2- This database I just created is supposed to work along side another database in the same server (the other database is the one used in Microsoft Small Business Accounting (SBA), if you care to know, but I am first testing all of this in one of the sample databases). In MY database, I created a View from a table in the sample database, and I am using this View to match Customers to Computers in a relation table. However, I realized that if I want to delete a customer, the computers for that customer won't be matched to any customer. Is there any way to fix this other than going into the sample database and modifying the PK referencing and deletion constraints for that Customers table?

3- This is my final question (for now anyway), after I am finished testing all of this on the sample database and have a solid database structure to work with along my company's database, I want to transfer MY database (the one with the computer table, the view and the relation table) to another server. I did some research and there is supposed to be some kind of wizard in SQL Server that helps you copy a database to another location, but I couldn't find it and, according to other forums, SQL Server 2005 doesn't come with this feature. How would I copy the database then?

Any help will be appreciated, and hopefully I didn't make this too long...I just wanted to be thorough.
Thanks

S.R.

Nevermind about number 1. I just went into my VB application and enabled and disabled certain buttons to disallow insertions...not relevant to this forum.
Any help on number 2 and 3? I figured for number 2 I would write a trigger to delete records in my DB after records in the sample DB have been deleted, just an idea.

Thanks again,

S.R.

It sounds like for #2 that you want to erase orphan records. What is the relationship between computers and customers? Should you have any computer records for a customer that doesn't exist? (I.e. does the computers table have a customer ID field?) Is so, then you could use a foreign key with cascade delete such that when you delete a customer, all computer records referenced by the foreign key to the customer ID also get erased.

SQL server most certainly has the capability to transfer data, copy a database, copy objects, etc. Usually this can be found in the management studio by selecting the database, right-click it, the look for "Export Data..." in the menus. From there you can copy the whole database or specify which objects (tables, procedures, view, etc) that you want to transfer. Also you could simply back up the database, then restore it onto the destination server.

Red-Gate makes some very nice SQL tools for schema comparison as well as data comparison that you can also use to transfer DB architecture and data.

-Peter

Peter
What we are doing is keeping track of our customers' hardware inventory, so the relationship between computers and customers is that one customer may have many computers, or none. Also, I'm guessing we shouldn't have records for a customer who doesn't exist in our DB because that would mean they are no longer our customers so why would we want to keep track of their inventory, right?
Moreover, I forgot to mention this, the SDK for SBA says that it is not recommended to change the DB schema (that is why I created a separate DB) so I don't want to modify the original Customer's table...what would I do then?
Finally, to make things clear this is kind of what my DB schema looks like:
- CustomerView (AccountID (PK), Name) --> This is a view created from another table in the company's SBA DB
- Customer_Computer_rel (AccountID, ComputerID (FK))
- Computer (ComputerID (PK), and some other fields like Serial #, etc)

Thanks for the help,

S.R.

Well, if you can't modify the database schema then you are pretty limited. With db architecture changes you'll have to rely on manually execution queries to handle the cleanup. This is easy enough with the right SQL query to check for records that use IDs that don't have a corresponding record in the appropriate table.

-Peter

Thanks for the help, I did figure this out yesterday just to have my supervisor tell me that we didn't want to delete computer records because Customers are never deleted, they are set as inactive.
Again, thanks for all the help.

S.R.