Create Table with a multiple column primary key

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

 

[MaltaC]

Thnak you for reviewing my problem. PHV supplied me with the needed information.

 

[MaltaC]

The solution from PHV is below:

PHV (MIS)


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)" & _
")"