• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

sql server primary key/table design

I am unsure how to create my table.

I have a table that exists already and stores client words.
the client data could change over the course of time, thus introducing new words. I thought I would create a new table where i could store the words once i had grouped them along with the datetime. so when the word client db changes with new data my grouped list of words would be different. then I would do some other action.

I have this
use Dictionary
CREATE TABLE TblCurrentWords
(
Word_ID nvarchar(MAX) NOT NULL PRIMARY KEY,
Word nvarchar(50),
Datetime * not sure how to make this field
)

is it permissible to make the primary key a composite of word+datetime, if so how, how do i use datetime in field datetime.

if this is possible then I will always have a list of the last list of words and can then identify the new words. if its a bad design then what would be better.
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 5
  • 5
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
is it permissible to make the primary key a composite of word+datetime, if so how, how do i use datetime in field datetime.
Yes if datetime would never be NULL.
0
 
PeterBaileyUkAuthor Commented:
is that even possible for datetime to be null?
0
 
PeterBaileyUkAuthor Commented:
ok so how to i create that field?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
is that even possible for datetime to be null?
If you update it with a NULL value or when you perform an insert if you do not a provide a value of course it can be NULL unless you define it as NOT NULL so an error will be thrown.

ok so how to i create that field?
CREATE TABLE TblCurrentWords
(
 Word_ID nvarchar(MAX) NOT NULL,
 Word nvarchar(50),
 MyDateTimeCol Datetime NOT NULL,
 CONSTRAINT pk_CurrentWords PRIMARY KEY (Word_Id, MyDateTimeCol)
)

Open in new window

1
 
PeterBaileyUkAuthor Commented:
the last line remains underlined in red saying table level constraint does not specify column list TblCurrentWords
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post a screenshot of that?
0
 
PeterBaileyUkAuthor Commented:
from reading the ms site it looks in order
Capture.JPG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try to run it anyway?
0
 
PeterBaileyUkAuthor Commented:
no and yes it worked
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good.
SSMS sometimes like to play with us :)
Cheers
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now