Avatar of PeterBaileyUk
PeterBaileyUk
 asked on

sql server primary key/table design

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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PeterBaileyUk

ASKER
is that even possible for datetime to be null?
PeterBaileyUk

ASKER
ok so how to i create that field?
Vitor Montalvão

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PeterBaileyUk

ASKER
the last line remains underlined in red saying table level constraint does not specify column list TblCurrentWords
Vitor Montalvão

Can you post a screenshot of that?
PeterBaileyUk

ASKER
from reading the ms site it looks in order
Capture.JPG
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

Did you try to run it anyway?
PeterBaileyUk

ASKER
no and yes it worked
Vitor Montalvão

Good.
SSMS sometimes like to play with us :)
Cheers
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck