Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server primary key/table design

Posted on 2016-09-06
10
Medium Priority
?
57 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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 52

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 52

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 52

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 52

Expert Comment

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

926 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