user7350714 user7350714 - 1 year ago 55
SQL Question

Issue in mysql table creation

I need to create a table called benificiaries where I have three columns

customerid
accountno
bank


The condition should be one
customerid
can have only one unique
accountno
. But another
customerid
can have the same
accountno
and same unique (only once). So I cant give primary key to
accountno
. Even for
customerid
I can't give primary key, since one
customerid
can have multiple records with unique
accountno
.

How can we create table in this case? Any ideas?

Answer Source

You can use multiple-column unique index.

CREATE TABLE YOUR_TABLE (
    id         INT NOT NULL AUTO_INCREMENT,
    customerid         INT NOT NULL,
    accountno  INT NOT NULL,
    bank INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX name (customerid,accountno)
);

Documentation here.

https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download