Solved

sql server primary key/table design

Posted on 2016-09-06
10
42 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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 49

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 49

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 49

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 49

Expert Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 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