SQL Compound Primary Keys
-
I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical. Here's the tables:
CREATE TABLE tblOperators
(OperatorId INT IDENTITY NOT NULL,
AccountNo VARCHAR(10) NULL,
[Name] VARCHAR(100) NOT NULL,
Address1 VARCHAR(100) NOT NULL,
Address2 VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
[State] VARCHAR(2) NOT NULL,
ZipCode VARCHAR(10) NOT NULL,
Phone VARCHAR(10) NOT NULL,
Email VARCHAR(100) NULL,
IsActive BIT NULL)CREATE TABLE tblFacilities
(FacilityId INT IDENTITY NOT NULL,
OperatorId INT NOT NULL,
FormOfPaymentId INT NULL,
SaleTypeId INT NULL,
FacilityName VARCHAR(100) NULL,
FacilityAddress1 VARCHAR(100) NULL,
FacilityAddress2 VARCHAR(100) NULL,
FacilityCity VARCHAR(100) NULL,
FacilityState VARCHAR(02) NULL,
FacilityZip VARCHAR(10) NULL,
FacilityPhone VARCHAR(10) NULL,
BankName VARCHAR(100) NULL,
ManagerName VARCHAR(100) NULL,
ManagerPhone VARCHAR(10) NULL,
ManagerEmail VARCHAR(100) NULL,
PickupDays VARCHAR(100) NULL,
IsActive BIT NULL)You can see the Operator and Facility Id's. They won't work once sent to the server. I'm not sure how to define compound keys. Can someone point me in the right direction? Thanks
If it's not broken, fix it until it is
-
I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical. Here's the tables:
CREATE TABLE tblOperators
(OperatorId INT IDENTITY NOT NULL,
AccountNo VARCHAR(10) NULL,
[Name] VARCHAR(100) NOT NULL,
Address1 VARCHAR(100) NOT NULL,
Address2 VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
[State] VARCHAR(2) NOT NULL,
ZipCode VARCHAR(10) NOT NULL,
Phone VARCHAR(10) NOT NULL,
Email VARCHAR(100) NULL,
IsActive BIT NULL)CREATE TABLE tblFacilities
(FacilityId INT IDENTITY NOT NULL,
OperatorId INT NOT NULL,
FormOfPaymentId INT NULL,
SaleTypeId INT NULL,
FacilityName VARCHAR(100) NULL,
FacilityAddress1 VARCHAR(100) NULL,
FacilityAddress2 VARCHAR(100) NULL,
FacilityCity VARCHAR(100) NULL,
FacilityState VARCHAR(02) NULL,
FacilityZip VARCHAR(10) NULL,
FacilityPhone VARCHAR(10) NULL,
BankName VARCHAR(100) NULL,
ManagerName VARCHAR(100) NULL,
ManagerPhone VARCHAR(10) NULL,
ManagerEmail VARCHAR(100) NULL,
PickupDays VARCHAR(100) NULL,
IsActive BIT NULL)You can see the Operator and Facility Id's. They won't work once sent to the server. I'm not sure how to define compound keys. Can someone point me in the right direction? Thanks
If it's not broken, fix it until it is
That is a big reason not to use integers as keys. You may need to rethink your whole application. I mainly wonder why the clients would be adding records to these tables. However, in cases like that each client should probably have its own ID and therefore you could make the key be both the ClientID and the other Id.
-
That is a big reason not to use integers as keys. You may need to rethink your whole application. I mainly wonder why the clients would be adding records to these tables. However, in cases like that each client should probably have its own ID and therefore you could make the key be both the ClientID and the other Id.
PIEBALDconsult wrote:
You may need to rethink your whole application.
I mainly wonder why the clients would be adding records to these tables.What if the client doesn't have a network connection? Where would the store the data. This application cannot have down time in the event of a lost connection. So the app will have a local data store and update the server periodically. So, since new records can be added in the client's DB, I need to make sure the PK's are unique. Thanks
If it's not broken, fix it until it is
-
I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical. Here's the tables:
CREATE TABLE tblOperators
(OperatorId INT IDENTITY NOT NULL,
AccountNo VARCHAR(10) NULL,
[Name] VARCHAR(100) NOT NULL,
Address1 VARCHAR(100) NOT NULL,
Address2 VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
[State] VARCHAR(2) NOT NULL,
ZipCode VARCHAR(10) NOT NULL,
Phone VARCHAR(10) NOT NULL,
Email VARCHAR(100) NULL,
IsActive BIT NULL)CREATE TABLE tblFacilities
(FacilityId INT IDENTITY NOT NULL,
OperatorId INT NOT NULL,
FormOfPaymentId INT NULL,
SaleTypeId INT NULL,
FacilityName VARCHAR(100) NULL,
FacilityAddress1 VARCHAR(100) NULL,
FacilityAddress2 VARCHAR(100) NULL,
FacilityCity VARCHAR(100) NULL,
FacilityState VARCHAR(02) NULL,
FacilityZip VARCHAR(10) NULL,
FacilityPhone VARCHAR(10) NULL,
BankName VARCHAR(100) NULL,
ManagerName VARCHAR(100) NULL,
ManagerPhone VARCHAR(10) NULL,
ManagerEmail VARCHAR(100) NULL,
PickupDays VARCHAR(100) NULL,
IsActive BIT NULL)You can see the Operator and Facility Id's. They won't work once sent to the server. I'm not sure how to define compound keys. Can someone point me in the right direction? Thanks
If it's not broken, fix it until it is
Kevin Marois wrote:
and each night will upload its data to the server
Is this a oneway communication where the data is only uploaded, or will it synchronise the data with the server? If it's just an upload you don't need to have global keys, you can add new keys at the server when uploading.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical. Here's the tables:
CREATE TABLE tblOperators
(OperatorId INT IDENTITY NOT NULL,
AccountNo VARCHAR(10) NULL,
[Name] VARCHAR(100) NOT NULL,
Address1 VARCHAR(100) NOT NULL,
Address2 VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
[State] VARCHAR(2) NOT NULL,
ZipCode VARCHAR(10) NOT NULL,
Phone VARCHAR(10) NOT NULL,
Email VARCHAR(100) NULL,
IsActive BIT NULL)CREATE TABLE tblFacilities
(FacilityId INT IDENTITY NOT NULL,
OperatorId INT NOT NULL,
FormOfPaymentId INT NULL,
SaleTypeId INT NULL,
FacilityName VARCHAR(100) NULL,
FacilityAddress1 VARCHAR(100) NULL,
FacilityAddress2 VARCHAR(100) NULL,
FacilityCity VARCHAR(100) NULL,
FacilityState VARCHAR(02) NULL,
FacilityZip VARCHAR(10) NULL,
FacilityPhone VARCHAR(10) NULL,
BankName VARCHAR(100) NULL,
ManagerName VARCHAR(100) NULL,
ManagerPhone VARCHAR(10) NULL,
ManagerEmail VARCHAR(100) NULL,
PickupDays VARCHAR(100) NULL,
IsActive BIT NULL)You can see the Operator and Facility Id's. They won't work once sent to the server. I'm not sure how to define compound keys. Can someone point me in the right direction? Thanks
If it's not broken, fix it until it is
I don't see a key, only an identity-field. If that identity is the key, and you need a quick solution, add a second column with a
GUIDand promote that to be the primary key. I'd recommend to have a "real" key; take those columns that uniquely identify a record, and use those as a key, and the identity merely as an alternative shortcut to that.Bastard Programmer from Hell :suss:
-
That is a big reason not to use integers as keys. You may need to rethink your whole application. I mainly wonder why the clients would be adding records to these tables. However, in cases like that each client should probably have its own ID and therefore you could make the key be both the ClientID and the other Id.
-
I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical. Here's the tables:
CREATE TABLE tblOperators
(OperatorId INT IDENTITY NOT NULL,
AccountNo VARCHAR(10) NULL,
[Name] VARCHAR(100) NOT NULL,
Address1 VARCHAR(100) NOT NULL,
Address2 VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
[State] VARCHAR(2) NOT NULL,
ZipCode VARCHAR(10) NOT NULL,
Phone VARCHAR(10) NOT NULL,
Email VARCHAR(100) NULL,
IsActive BIT NULL)CREATE TABLE tblFacilities
(FacilityId INT IDENTITY NOT NULL,
OperatorId INT NOT NULL,
FormOfPaymentId INT NULL,
SaleTypeId INT NULL,
FacilityName VARCHAR(100) NULL,
FacilityAddress1 VARCHAR(100) NULL,
FacilityAddress2 VARCHAR(100) NULL,
FacilityCity VARCHAR(100) NULL,
FacilityState VARCHAR(02) NULL,
FacilityZip VARCHAR(10) NULL,
FacilityPhone VARCHAR(10) NULL,
BankName VARCHAR(100) NULL,
ManagerName VARCHAR(100) NULL,
ManagerPhone VARCHAR(10) NULL,
ManagerEmail VARCHAR(100) NULL,
PickupDays VARCHAR(100) NULL,
IsActive BIT NULL)You can see the Operator and Facility Id's. They won't work once sent to the server. I'm not sure how to define compound keys. Can someone point me in the right direction? Thanks
If it's not broken, fix it until it is
You may have to change the way you are bringing data from the client DB to your server DB. Check this article out ... http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server[^] Remember to vote if this helps. :thumbsup:
-
PIEBALDconsult wrote:
You may need to rethink your whole application.
I mainly wonder why the clients would be adding records to these tables.What if the client doesn't have a network connection? Where would the store the data. This application cannot have down time in the event of a lost connection. So the app will have a local data store and update the server periodically. So, since new records can be added in the client's DB, I need to make sure the PK's are unique. Thanks
If it's not broken, fix it until it is
Yes, but why would the client be adding records to those tables?
Kevin Marois wrote:
PK's are unique
Globally unique?
-
Yes, but why would the client be adding records to those tables?
Kevin Marois wrote:
PK's are unique
Globally unique?
I don't understand why you're questioning this. The software is installed locally on a user's PC, along with a local copy of the DB. The software does CRUD operations on the local copy of the DB. Then, at night, or at least once per week, the software uploads all new/changed/deleted records to the server.
If it's not broken, fix it until it is
-
Kevin Marois wrote:
and each night will upload its data to the server
Is this a oneway communication where the data is only uploaded, or will it synchronise the data with the server? If it's just an upload you don't need to have global keys, you can add new keys at the server when uploading.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
There will be two-way communicaation
If it's not broken, fix it until it is