I was studying Absolute Database at
and according to one of the SQL Code samples, you can specify one of the fields (the primary key field) to auto increment:
Code:
CREATE TABLE new_table
(NewTableID AutoInc,
SomeOtherField VARCHAR(25)
SomeForeignKeyID INT);
NewTableID in this fictitious table is the Primary Key. I almost always name the Primary Key by the actual table name. By setting the NewTableID to AutoInc, you do not have to specify a value for the NewTableID whenever you execute an INSERT command:
Code:
INSERT INTO new_table
(SomeOtherField, SomeForeignKeyID) VALUES
('John Smith', 13);
Notice how the NewTableID is not specified. The database engine will automatically assign a new, unique, incremental value to the NewTableID for you. Depending on what you need to do, you may or may not need to specify the NewTableID when executing an UPDATE command. The following example does not use the NewTableID:
Code:
UPDATE new_table
SET SomeForeignKeyID = 27
WHERE SomeOtherField = 'John Smith';
In my opinion, the Primary Key of a table is only useful when JOINing tables. Otherwise, the Primary Key will rarely show up in any of your queries.
I don't know/understand exactly what your entire Client-Database program is supposed to do other than what you have written above. Perhaps it is possible to approach some of these tasks using what I will term as Record Qualifiers (yeah, stupid phrase, I know). The idea is to add fields to your table that state the status of individual records and use those fields to control the outcome of queries.
Imagine a video store Client-Database project. Amongst other tables, you would probably have a table containing the video inventory; a primary key, video name, and check-out status (CheckOutStatus). Neither the primary key nor the video name would change, but the check-out status would be updated every time the video was checked out (CheckedOutStatus = 'O') or returned (CheckedOutStatus = 'I') or on-hold (CheckedOutStatus = 'H') or whatever... If you wanted to know what was available for customers, you would look for CheckedOutStatus = 'I', if you wanted to know everything in inventory, you would ignore the CheckedOutStatus altogether.
Perhaps rather than moving around a pile of records from the main database to some other location and back, you could use an extra field to tell your other queries to ignore the TRIALNAME based on the status of the TRIALNAME.
Steve.
