Is it possible to define table relations with multiple keys in MySQL?

Question

Say for example I have in table SITES a unique key set to include fields site, permission and I want to use a foreign key to keep the same reference keyed to match site, permission in table USERS. Is it possible to create a relation that uses multi-key indexes in mysql? I can resolve this programmatically also, however I would prefer to keep referential integrity constrained to the data layer if at all possible. Using single relations could potentially lead to a record being inserted with the wrong permission but the correct site, or vice versa.

Thanks in advance.

Well that makes my life a lot easier then. Do you know the syntax for this, or a link to where I can research it? — mopsyd
– mopsyd, Commented Jul 11, 2014 at 23:45

Barranka · Accepted Answer · 2014-07-11 23:51:29Z

1

As I told you in my comment: Yes, you can have constraints that use multiple columns.

You can check MySQL Reference Manual: Using FOREIGN KEY constraints.

Quoting from the reference, the syntax is:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

As you can see, you can have multiple columns in the constraint. Of course (more exactly, obviously), they must match, and they must be indexed in both tables.

answered Jul 11, 2014 at 23:51

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is it possible to define table relations with multiple keys in MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related