CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
jaskamiin
jaskamiin

Reputation: 1

creating multiple foreign keys on arbitrary number of columns

I'm creating a table for a Customer, with a lot of columns representing possible Contracts they may have (at least one). The columns for possible contracts will have an integer which corresponds to the ID of a contract in another table.

CREATE TABLE "Customer"
(
    firstName    varchar(25),
    lastName     varchar(25),
    contract0    int NOT NULL,
    contract1    int,
       ...
    contract20   int
);

CREATE TABLE "Contracts"
(
     id          int NOT NULL PRIMARY KEY,
     svc_addr    varchar(50),
       ...
);

My questions are:

1 - What is the easiest way to create foreign keys on all of the columns, since they could all be potentially 'pointing to' a row in the contract table? I've run across this syntax in postgres:

...
contract16    integer references Contracts(id),
...

but it seems like that will take up a lot of time in typing, which is a problem I guess, when using so many columns, which brings me to my next question...

2 - Is there an easier way to create a table with n of these 'repetitive' columns (and then possibly put the keys on them all)? Outside of making a python script, or something.

thanks

Upvotes: 0

Views: 100

Answers (3)

alfreema
alfreema

Reputation: 1338

You really should consider normalizing the database structure more. In other words creating another table like this:

CREATE TABLE "Customer"
(
    customerId   bigserial primary key,
    firstName    varchar(25),
    lastName     varchar(25)
);

CREATE TABLE "CustomerContract"
(
    customerContractId bigserial primary key,
    customerId     int references Customer(customerId),
    contractId     int references Contracts(id),
    contractNumber int
);


CREATE TABLE "Contracts"
(
    id          int not null primary key,
    svc_addr    varchar(50),
   ...
);

First you create your customer.

Then when a contract is created, you insert into the contract row, and then tie it to a customer via the CustomerContract table.

This allows customers to have an unlimited number of contracts, without wasting space for nonexistent contracts. A side benefit is that given a contractId, you can quickly look up the customer associated with it, without having to index all of those unused contract columns in your Customer table.

Side note: I would consider changing the name of "Contracts" to be singular instead of plural (pretty much always make your table names singular). I would also change the "id" column of Contracts to contractId so that it will be clear what type of id it is when you reference it in other SQL statements.

Upvotes: 0

SlimsGhost
SlimsGhost

Reputation: 2909

Yes, there is an easier way.

If each contract is associated with only one customer, you need to put Customer_ID into your Contracts table and have the foreign key on that one column.

You already know one customer can have multiple contracts. If each contract can have multiple customers as well, then you have a many-to-many relationship and need a third table in the middle. For example, it could be called CustomerContracts, and have two columns, CustomerID and ContractID. Then you would have two foreign keys (one for each of those columns).

Either way, the "arbitrary" part goes away.

Upvotes: 1

RedGreenCode
RedGreenCode

Reputation: 2299

Rather than using multiple contractN columns, try adding an integer ContractType column. That would correspond to the 0..20 value in your example. You can add a foreign key to a ContractType table to ensure that the number represents a valid type.

Upvotes: 0

Related Questions