August 19, 2005 at 9:32 am
We want to make sure a sequence number is uniqe on a given date. However our date is stored in the db as a DATETIME and has MS. So every date is different. When we remove the MS either with CONVERT or CAST, we
CREATE TABLE Shiznit (
jStart DATETIME DEFAULT (GetDate()),
iSeq INT DEFAULT (0)
)
When you add the constraint
ALTER TABLE Shiznit ADD
CONSTRAINT CK_ShiznitjStartISeqUnique UNIQUE (CAST (jStart AS INT), iBatch)
You get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Thanks
- John
August 19, 2005 at 9:37 am
use this as the default instead and remove the cast in the constraint
dateadd(d, 0, datediff(d, 0, getdate()))
Also you can convert jstart to smalldatetime
August 19, 2005 at 10:04 am
thanks remi
We need the MS in the date field, so we basicly have to add another field to hold that day. Which is ok i guess:
CREATE TABLE Shiznit (
jStart DATETIME DEFAULT (GetDate()),
j INT DEFAULT (CAST(GetDate() AS INT)),
iSeq INT DEFAULT (0)
)
ALTER TABLE Shiznit ADD
CONSTRAINT CK_ShiznitjStartISeqUnique UNIQUE (j, iSeq)
Earlier, we had tried a calculated field and you get a similar error:
CREATE TABLE Shiznit (
jStart DATETIME DEFAULT (GetDate()),
j AS (CAST(jStart AS INT)),
iSeq INT DEFAULT (0)
)
ALTER TABLE Shiznit ADD
CONSTRAINT CK_ShiznitjStartISeqUnique UNIQUE (j, iSeq)
Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'j' is non-deterministic or imprecise.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Anybody know a way to do it without adding a field?
oh, by the way, there was a typo in the first post....this is what our constraint should look like:
ALTER TABLE Shiznit ADD
CONSTRAINT CK_ShiznitjStartISeqUnique UNIQUE (CAST (jStart AS INT), iSeq)
iSeq, not iBatch!
August 19, 2005 at 11:43 am
CREATE TABLE dbo.Test (
jStart datetime NOT NULL CONSTRAINT DF_Test_jStart DEFAULT (getdate()),
j AS (convert(int,jStart,112)) ,
iSeq smallint NOT NULL , --no need for a default here as it's gonna change at every insert, smallint because I don't think you need 2.3 millions seq here
CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED --to have a PK
(
jStart
) ON [PRIMARY] ,
CONSTRAINT IX_Test UNIQUE CLUSTERED --your clustered index with only 1 seq/day
(
j,
iSeq
) ON [PRIMARY] ,
CONSTRAINT CK_Test_iSeq CHECK (iSeq > 0)
) ON PRIMARY
GO
August 19, 2005 at 11:54 am
Thanks 4500!
We tried this:
j AS (CAST(jStart AS INT))
And you get this:
Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'j' is non-deterministic or imprecise.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
instead of this:
j AS (convert(INT,jStart,112))
Which works. Go figure.
August 19, 2005 at 12:04 pm
tell me what day this is :
1981/03/03.
If you can't, that's because dates are confusing (non deterministics) with 2 format exceptions. While I don't remember the 2nd one. 112 is the iso standard which IS DETERMINISTIC, therefore can be converted to int without confusion regardless of the date settings on the server and or connection. The other twist of this design is that a computed column is always possibly null, hence it can't be used as the primary key, that's why I shifted that on the main date column, now you can use that as the clustered index too if that fits better your needs.
BTW my name is Remi, 4500 is the number of posts I made (more or less 500).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply