Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Trudye
Posting Yak Master
169 Posts |
 Posted - 2009-08-24 : 17:15:05
|
| CREATE table My_Seq_Audit (Seq_Number int primary key clustered, VendorName varchar(20), Run_Date datetime default getdate())I'm having a problem adding VendorName as a secondary key. Any ideas? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-24 : 17:42:55
|
Can you clarify what you mean by "secondary key"? Do you mean as the second column of the primary key? If so, you must declare that column NOT NULL, and declare the constraint at the table level:CREATE table My_Seq_Audit (Seq_Number int NOT NULL, VendorName varchar(20) NOT NULL, Run_Date datetime default getdate(), CONSTRAINT PK_My_Seq_Audit PRIMARY KEY CLUSTERED(Seq_Number, VendorName)) If you mean something else, please explain. |
 |
|
|
Trudye
Posting Yak Master
169 Posts |
Posted - 2009-08-24 : 18:17:15
|
| Thank you so much Robvolk for responding so quickly. I should have been more specific, sorry about that. I meant a secondary key to the primary. Dupe Seq_Numbers may exist but not with the same VendorName. |
|
|
|
IncisiveOne
Starting Member
36 Posts |
Posted - 2009-08-25 : 09:42:37
|
| What ?Since you've declared Seq_Number int primary key clustered, then no, duplicate Seq_Numbers will not exist.But if we take "Dupe Seq_Numbers may exist but not with the same VendorName" as a separate description, then Rob's response is in that direction. Then "dupe Seq_Numbers may exist", although I wouldn't put it that way. The statements appear to contradict each other.Or do you mean you just want another separate key, a second key (not a 'secondary' key):[code]CREATE TABLE My_Seq_Audit ( Seq_Number int NOT NULL, VendorName VARCHAR(20) NOT NULL, Run_Date datetime DEFAULT GETDATE() CONSTRAINT UC_PK PRIMARY KEY CLUSTERED(Seq_Number) CONSTRAINT U_Second UNIQUE KEY (Seq_Number, VendorName) )In this case, duplicate Seq_Numbers will not exist and duplicate Seq_Number+VendorName will not exist.CheersAnything worth doing, is worth doing right. |
|
|
|
|
|
|
|
|
Seondary Key