Create Table with a multiple column primary key

[Gerry G.]

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 " & "( " & _
"TerminalNo Int 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

 

[Gerry G.]

thank you for reviewing my post. I received the solution on another forum see
below:

TableStr = "IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
& _
"WHERE TABLE_NAME = 'Daily_Price_ImportT') " & _
"Create Table Daily_Price_ImportT ( " & _
"TerminalNo Int 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 (TerminalNo, Supplier,
Terminal_Name)" & _
")"