Solved

sql server primary key/table design

Posted on 2016-09-06
10
29 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 45

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
 
LVL 45

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 45

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 45

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 45

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now