Concatenated Key for SQL?

Question

I need to write a quick program to build some tables in SQL. But one of the tables has two primary keys. Here is what I have so far, but I have no idea how to build in the second primary key, can anyone help??

conDatabase.Execute "Create Table SalesDetails(CustomerNo Counter CONSTRAINT PK_SalesDetails PRIMARY KEY, SalespersonNo (this is second primary key which also relates to a table which I have created called Salesperson in which SalespersonNo is also the Primary Key), SalesAmount Currency NOT NULL);"

Answer 1

You can only have one primary key.

What you need to do is use the create INDEX command to create a secondary index.

You can have multiple secondary indexes.



I hope this helps.

Answer 2

I think you mean foreign key.

If so, then



conDatabase.Execute "Create Table SalesDetails(CustomerNo Counter CONSTRAINT PK_SalesDetails PRIMARY KEY, SalespersonNo int, SalesAmount Currency NOT NULL,

CONSTRAINT FK_Salesperson FOREIGN KEY(SalespersonNo)

REFERENCES Salesperson);"

Answer 3

A table may only have one primary key but a primary key may contain more than one column. Check the documentation of the RDBMS for the correct syntax to specify multiple columns in a primary key.

Answer 4

I think you mean a combined key - a primary key that is unique by combining two columns to make the primary key. What is the flavour of SQL you are using ? The method may be different depending upon what you are using.



In Firebird:



CREATE TABLE NEW_TABLE1 (

COLA SMALLINT NOT NULL,

COLB SMALLINT NOT NULL,

COLC SMALLINT);





ALTER TABLE NEW_TABLE1 ADD PRIMARY KEY (COLA,COLB);



In SQL Server:

CREATE TABLE [dbo].[Table_1](

[ColA] [bigint] NOT NULL,

[ColB] [bigint] NOT NULL,

[ColC] [bigint] NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

(

[ColA] ASC,

[ColB] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]



Sorry, I don't have any other databases on my Laptop to try it out on but this should give you an idea.