August 19, 2004 at 11:40 am
I am building my 1st DB and using SQL Server 2000 MSDE. One of my tables looks like this:
CREATE TABLE DailyWork (
DailyWorkID INTEGER NOT NULL IDENTITY(1,1),
Date SMALLDATETIME NOT NULL,
Customer_CustNo INTEGER NOT NULL,
Description TEXT NULL,
CONSTRAINT pk_DailyWork PRIMARY KEY CLUSTERED (DailyWorkID),
CONSTRAINT fk_DailyWork_Customer FOREIGN KEY (Customer_CustNo) REFERENCES Customer (CustNo)
);
Nothing special but I would like to add a constraint to this table so that all information relevant to a specific Date and Customer_CustNo get linked to that record. In other words, Date doesnt have to be unique and Customer_CustNo doesnt have to be unique but I want the combination of the two to be unique. Is there a way to check for this on the server so that I dont have to do multiple connects with the client? (one to check for the existance of the Date/CustNo combo, then one to insert the relevant info). Would a stored procedure work for this?
Thanks in advance.
kmbarlow
August 19, 2004 at 12:46 pm
Define unique constraint at columns custno and date.
alter table DailyWork add constraint DailyWork_custno_date unique (custno, date)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply