Solved

sql server primary key/table design

Posted on 2016-09-06
10
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 50

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
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.

 
LVL 50

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 50

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 50

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 50

Expert Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
store vs query adhoc - no show rows 4 37
how to resize column length with primary ket 4 34
t-sql left join 2 34
learning MS SSIS 13 22
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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