aspose file tools*
The moose likes JDBC and the fly likes defining multi-column primary key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "defining multi-column primary key" Watch "defining multi-column primary key" New topic
Author

defining multi-column primary key

Peter Lyons
Ranch Hand

Joined: Sep 12, 2000
Posts: 202
I am trying to structure a CREATE TABLE statement such that three columns together will serve as the primary key. in SQL - The Complete Reference, p. 315 states:
"A PRIMARY KEY constraint can appear as a column constraint or a table constraint. If the primary key consists of a single column, the column constraint may be more convenient. If it consists of multiple columns, it should be specified as a table constraint."
I do not know how to define the table constraint in my CREATE.
Here is the SQL:
CREATE TABLE [dbo].[ContractInformation] (
[OwnerID] [int] NOT NULL,
[ClaimNumber] [varchar] (20) NOT NULL,
[ContractNetworkCode] [varchar] (3) NOT NULL,
[DateStamp] [datetime] NOT NULL ,
[SeqNum] [int] NULL ,
[NetworkCode] [varchar] (3) NULL ,
[Used] [varchar] (1) NULL ,
[DateSent] [datetime] NULL ,
[DateReceived] [datetime] NULL ,
[RepricedStatus] [int] NULL ,
[PPOBilledDate] [datetime] NULL ,
[PayerBilledDate] [datetime] NULL ,
[ReadyToBill] [varchar] (1) NULL ,
[ParticipatingPPO] [varchar] (1) NULL
) ON [PRIMARY]
GO
adding a single primary key is easy:
CREATE TABLE [dbo].[ContractInformation] (
[OwnerID] [int] NOT NULL PRIMARY KEY,
[ClaimNumber] [varchar] (20) NOT NULL,
[ContractNetworkCode] [varchar] (3) NOT NULL,
......
if I just add "PRIMARY KEY" in the same way to each of those first 3 columns, it fails, saying I can't create more than one primary key.
Anybody out there know how to write this? I'm hoping it will work in both SQL-Server and Oracle.
Peter Lyons
Ranch Hand

Joined: Sep 12, 2000
Posts: 202
Well, since I wasn't just sitting around waiting, I did find one way of accomplishing this. In case you're interested: http://www.1tehnicka.hr/estuff/books/0-672/0-672-30832-0/ch15.htm#E69E205
I found I can define a multi-column UNIQUE key by executing the following statement:
CREATE TABLE [dbo].[ContractInformation] (
[OwnerID] [int] NOT NULL,
[ClaimNumber] [varchar] (20) NOT NULL,
[ContractNetworkCode] [varchar] (3) NOT NULL,
[DateStamp] [datetime] NOT NULL ,
[SeqNum] [int] NULL ,
[NetworkCode] [varchar] (3) NULL ,
[Used] [varchar] (1) NULL ,
[DateSent] [datetime] NULL ,
[DateReceived] [datetime] NULL ,
[RepricedStatus] [int] NULL ,
[PPOBilledDate] [datetime] NULL ,
[PayerBilledDate] [datetime] NULL ,
[ReadyToBill] [varchar] (1) NULL ,
[ParticipatingPPO] [varchar] (1) NULL,
CONSTRAINT UK_OwnerID_ClaimNumber_ContractNetworkCode UNIQUE (OwnerID, ClaimNumber, ContractNetworkCode)
) ON [PRIMARY]
GO
 
Consider Paul's rocket mass heater.
 
subject: defining multi-column primary key