| Author |
Topic |
|
Jim Beam
Posting Yak Master
137 Posts |
 Posted - 2011-02-05 : 06:58:31
|
| Hi all,I'm creating a new table and need to populate the Date column (PK) with the date of row insertion. What constraint would be a good choice?Cheers,Jim |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 07:22:41
|
How are records being inserted? trigger? proc? the PK is itself a unique constraint...are you sure a date is the best PK candidate? perhaps an ID column and a surrogate,included column with the date and a constraint?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-05 : 08:25:21
|
quote:
Originally posted by dataguru1971
How are records being inserted? trigger? proc? the PK is itself a unique constraint...are you sure a date is the best PK candidate? perhaps an ID column and a surrogate,included column with the date and a constraint?
Poor planning on your part does not constitute an emergency on my part.
The table is for my own personal DB which I'll be using to track my progress with weight-training, I'll insert a single row weekly (and for the time being manually until I add a front end that I can use remotely) and want to run any analysis from week to week. I also want the column to reflect the date and be unique. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 08:36:21
|
Ahh..well, The PK would enforce the uniqueness constraint..by default a PK is a constraint. I would suggest you insert the rows without the time element to avoid dupes..
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-05 : 09:02:59
|
| Ah ha, that reminds me of the part I forgot about - I want that Date column to be automatically populated with the date of the insertion, unless I specify otherwise in my Insert statements. I thought about GetDate, but I get the feeling that won't help... |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 09:12:37
|
Use a proc to insert the data and have the date as a one of the variable that you can pass.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-05 : 11:25:19
|
| A sproc has the advantage for me of being sorely-needed extra practice in DB design (no joke either!), but would it not be preferable/possible to hardcode the column to my needs? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-05 : 12:41:00
|
Make a default constraintcreate table stub(i int,dt date default(getdate()),z varchar(10)))
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-07 : 10:29:57
|
| Hi all, further below is the table in all it's glory gory, but it's throwing back a basic syntax error...I imagine it's a simple mistake, but I'm not sure what I've done wrong.Msg 102, Level 15, State 1, Line 6Incorrect syntax near 'Int'.And the table..Create Table Strength ( Date Date default(getdate()), BodyPartID Int REFERENCES Bodypart (BodypartID), MovementID Int REFERENCES Movement (MovementID, TargetWeight Int, CONSTRAINT PK_Date PRIMARY KEY CLUSTERED (Date) ) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-07 : 11:49:10
|
Where is the closing paranthesis after MovementID?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-07 : 12:18:21
|
quote:
Originally posted by Peso
Where is the closing paranthesis after MovementID?
N 56°04'39.26"E 12°55'05.63"
Damn SQL Server said "Line 6"...  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-07 : 14:50:15
|
Yes, the error occurs on line 6 because since there is no closing paranthesis after MovementID and insted find a comma, SQL Server thinks you foreign key references a composite key (multiple columns) on outer table. And after TargetWeight if finds the text "INT" where it expects another comma or a closing paranthesis.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-07 : 18:17:42
|
| Thanks for clearing that up...Skol !I'll take this now to the Design subsection, anyone who wants to follow along can do so at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=156268 |
|
|
|
|
Date column