Create Table with a multiple column primary key 1

[MaltaC]

I am trying to create a table with a multiple column primary key. When I run the code below I receive the follwoing error message:

"Cannot add multiple PRIMARY KEY constraints to table 'Daily_Price_ImportT'"


What is the correct coding to create a table with a multiple column primary key?

Thanks,

Gerry




Sub CreateImportTable(cnxn As ADODB.Connection)

Dim TableStr As String

On Error GoTo Import_Daily_Price_Err


'Create Import tables

TableStr = "IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE TABLE_NAME = 'Daily_Price_ImportT') " & _
"Create Table Daily_Price_ImportT " & "( " & _
"Rec_Index Int IDENTITY(1,1) NOT NULL PRIMARY KEY, " & _
"Supplier nvarchar(100) NOT NULL PRIMARY KEY, " & _
"Terminal_Name nvarchar(50) NOT NULL PRIMARY KEY, " & _
"Terminal_Abbr nvarchar(20) Null, " & _
"Terminal_City nvarchar(50) Null, " & _
"Terminal_State nvarchar(5) Null, " & _
"Product_Name nvarchar(120) Null, " & _
"Brand_Type nvarchar(5) Null, " & _
"Effective_Date datetime Null, " & _
"Effective_Time datetime Null, " & _
"Price real Null, " & _
"Change real Null, " & _
")"

cnxn.Execute TableStr

 

[RoyVidar]

Gee - what kind of uniqueness are you after, I mean, one of the fields is identity, which would make it unique by itself, you wouldn't need including the rest, now, would you?

This syntax doesn't look much like Access, might I guess SQL server? If so, perhap look in a forum dedicated to that technology (forum183)? Or if you're working with ADP's, perhaps forum958

Roy-Vidar

 

[MaltaC]

WELL THANK YOU. YOUR RESPONSE IS EXTREMELY USEFUL!!!!!!!!!!!!!!!

 

[PHV]

TableStr = "IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE TABLE_NAME = 'Daily_Price_ImportT') " & _
"Create Table Daily_Price_ImportT ( " & _
"Rec_Index Int IDENTITY(1,1) NOT NULL, " & _
"Supplier nvarchar(100) NOT NULL, " & _
"Terminal_Name nvarchar(50) NOT NULL, " & _
"Terminal_Abbr nvarchar(20) Null, " & _
"Terminal_City nvarchar(50) Null, " & _
"Terminal_State nvarchar(5) Null, " & _
"Product_Name nvarchar(120) Null, " & _
"Brand_Type nvarchar(5) Null, " & _
"Effective_Date datetime Null, " & _
"Effective_Time datetime Null, " & _
"Price real Null, " & _
"Change real Null, " & _
"CONSTRAINT myPrimaryKey PRIMARY KEY (Rec_Index, Supplier, Terminal_Name)" & _
")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MaltaC]

PHV,

It worked. Thank you very much for your help.


Gerry