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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok so how to i create that field?
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)
)ASKER
the last line remains underlined in red saying table level constraint does not specify column list TblCurrentWords
Can you post a screenshot of that?
ASKER
from reading the ms site it looks in order
Capture.JPG
Capture.JPG
Did you try to run it anyway?
ASKER
no and yes it worked
Good.
SSMS sometimes like to play with us :)
Cheers
SSMS sometimes like to play with us :)
Cheers
ASKER