I want to make a date constraint in my table (I use sql server). I want to make sure that the date in one of my columns is later than the current date and time (I know it sounds weird, but it's an assignment so I have no choice). I tried to do it this way:
ALTER TABLE sales ADD CONSTRAINT d CHECK (Date > CURRENT_TIMESTAMP);
but later when inserting DEFAULT into date column I get the following error:
The INSERT statement conflicted with the CHECK constraint "d".
The conflict occurred in database "Newsagents", table "dbo.Sales", column 'Date'.
This is the said table:
CREATE TABLE Sales (
ID INT IDENTITY(1,1) NOT NULL ,
ClientID INT REFERENCES Client(ClientID),
ProductNumber CHAR(10) REFERENCES Product(ProductNumber),
Quantity INT NOT NULL,
Price FLOAT NOT NULL ,
Date TIMESTAMP NOT NULL,
PRIMARY KEY ( ID )
and this how I insert my data into Sales column and get the constraint conflict:
DECLARE @counter INT
DECLARE @quantity int
DECLARE @prodNum varchar(20)
SET @counter = 0
WHILE @counter < 10
BEGIN
SET @quantity = (select FLOOR(RAND()*100))
SET @prodNum = (select TOP 1 ProductNumber from Product Order by NEWID())
insert into Sales (ClientID, ProductNumber, Quantity, Price, Date )
values(
(select TOP 1 ClientID from Client Order by NEWID()),
(select @prodNum),
(select @quantity),
((select @quantity)*(select TOP 1 Price from Product where ProductNumber = @prodNum)),
DEFAULT
)
SET @counter = @counter + 1
END
Is there a different way to do this? Or am I doing something wrong?
change the Date column to datetime