Coding at table with multiple primary key columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a talbe by coding in VB.Net and I know how to create a
table with one column as a primary key, but I need mulitple columns as the
key how can I do that? Right now I use:
SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
TEXT(10), [Game] INTEGER)".
To make one column the PK, but if I add a second or thrid constraint as as
PK in the same way it does not work. Can someone help me?
 
I am trying to create a talbe by coding in VB.Net and I know how to create a
table with one column as a primary key, but I need mulitple columns as the
key how can I do that? Right now I use:
SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
TEXT(10), [Game] INTEGER)".
To make one column the PK, but if I add a second or thrid constraint as as
PK in the same way it does not work. Can someone help me?
Click to expand...

out of the help file: Topic Microsoft Jet SQL reference

This example creates a new table called MyTable with two text fields,
a Date/Time field, and a unique index made up of all three fields.

Sub CreateTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with three fields and a unique
' index made up of all three fields.

dbs.Execute "CREATE TABLE MyTable " _
& "(FirstName CHAR, LastName CHAR, " _
& "DateOfBirth DATETIME, " _
& "CONSTRAINT MyTableConstraint UNIQUE " _
& "(FirstName, LastName, DateOfBirth));"
dbs.Close

End Sub
 
Hey Andi, this solution does not work I am getting a Constraint error. I am
using System.Data.OleDb. I dont know if that makes a difference. Also, will
the unique contraint do the same thing as the primary key. I dont think so.
All I need the key for is editing the table b/c if I dont have one and I try
to update the table I will get an error. Thanks

Andi Mayer said:
I am trying to create a talbe by coding in VB.Net and I know how to create a
table with one column as a primary key, but I need mulitple columns as the
key how can I do that? Right now I use:
SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
TEXT(10), [Game] INTEGER)".
To make one column the PK, but if I add a second or thrid constraint as as
PK in the same way it does not work. Can someone help me?
Click to expand...

out of the help file: Topic Microsoft Jet SQL reference

This example creates a new table called MyTable with two text fields,
a Date/Time field, and a unique index made up of all three fields.

Sub CreateTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with three fields and a unique
' index made up of all three fields.

dbs.Execute "CREATE TABLE MyTable " _
& "(FirstName CHAR, LastName CHAR, " _
& "DateOfBirth DATETIME, " _
& "CONSTRAINT MyTableConstraint UNIQUE " _
& "(FirstName, LastName, DateOfBirth));"
dbs.Close

End Sub
Click to expand...
 
I figured it out. You can use
CREATE TABLE NBA_games_today ([Away] TEXT(20), [Home] TEXT(20), [Away_Score]
INTEGER, [Home_Score] INTEGER, [Spread] SINGLE, [Home_Fav] BIT, [OverUnder]
SINGLE, [Date] TEXT(20), [Day] TEXT(10), [Game] INTEGER, PRIMARY KEY (Away,
Home, Date) ), and it will work

ryan said:
Hey Andi, this solution does not work I am getting a Constraint error. I am
using System.Data.OleDb. I dont know if that makes a difference. Also, will
the unique contraint do the same thing as the primary key. I dont think so.
All I need the key for is editing the table b/c if I dont have one and I try
to update the table I will get an error. Thanks

Andi Mayer said:
I am trying to create a talbe by coding in VB.Net and I know how to create a
table with one column as a primary key, but I need mulitple columns as the
key how can I do that? Right now I use:
SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
TEXT(10), [Game] INTEGER)".
To make one column the PK, but if I add a second or thrid constraint as as
PK in the same way it does not work. Can someone help me?
Click to expand...

out of the help file: Topic Microsoft Jet SQL reference

This example creates a new table called MyTable with two text fields,
a Date/Time field, and a unique index made up of all three fields.

Sub CreateTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with three fields and a unique
' index made up of all three fields.

dbs.Execute "CREATE TABLE MyTable " _
& "(FirstName CHAR, LastName CHAR, " _
& "DateOfBirth DATETIME, " _
& "CONSTRAINT MyTableConstraint UNIQUE " _
& "(FirstName, LastName, DateOfBirth));"
dbs.Close

End Sub
Click to expand...
Click to expand...
 
Hey Andi, this solution does not work I am getting a Constraint error. I am
using System.Data.OleDb. I dont know if that makes a difference. Also, will
the unique contraint do the same thing as the primary key. I dont think so.
All I need the key for is editing the table b/c if I dont have one and I try
to update the table I will get an error. Thanks
Click to expand...

oops have send you the wrong example

Sub test1()

Dim aType As String, SQL As String
aType = "Test1"
SQL = "CREATE TABLE " & aType & "_games_today (Away TEXT(20), " _
& " K_Away INTEGER, Home TEXT(20), Away_Score INTEGER," _
& " Home_Score INTEGER, Spread SINGLE, Home_Fav BIT," _
& " OverUnder SINGLE, aDate TEXT(20) , aDay " _
& " TEXT(10), Game INTEGER," _
& " CONSTRAINT MyPK PRIMARY KEY (Away,K_Away, Away_Score ))"

CurrentDb.Execute SQL

End Sub


I don't know vb.net, but this example is working in Access with DAO

Note: dont use date, day in Access, this are reserved words. With your
brackets the SQL will work but you can get afterwards a lot of
troubles

Key is unknow to Access, therefore I used INTEGER, because I asumed
you want a Long
 
Back
Top