Solved

sql server primary key/table design

Posted on 2016-09-06
10
38 Views
Last Modified: 2016-09-07
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
Comment
Question by:PeterBaileyUk
  • 5
  • 5
10 Comments
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41787324
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
 

Author Comment

by:PeterBaileyUk
ID: 41787340
is that even possible for datetime to be null?
0
 

Author Comment

by:PeterBaileyUk
ID: 41787342
ok so how to i create that field?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41787375
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
 

Author Comment

by:PeterBaileyUk
ID: 41787457
the last line remains underlined in red saying table level constraint does not specify column list TblCurrentWords
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41787491
Can you post a screenshot of that?
0
 

Author Comment

by:PeterBaileyUk
ID: 41787492
from reading the ms site it looks in order
Capture.JPG
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41787494
Did you try to run it anyway?
0
 

Author Comment

by:PeterBaileyUk
ID: 41787505
no and yes it worked
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41787508
Good.
SSMS sometimes like to play with us :)
Cheers
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question