Foreign key reference to a two-column primary key

Question

I am creating a database that should work on MySQL, PostgreSQL and SQLite. One of my tables has a primary key with two columns:

CREATE TABLE tournament (
    state CHAR(2) NOT NULL, 
    year INT NOT NULL,
    etc..., 
    PRIMARY KEY(state, year)
);

I want a link to a table tournamentfrom another table, but I want this link to be null. . Here's how I can do it, imagining that the winner does not necessarily have a tournament:

CREATE TABLE winner (
    name VARCHAR NOT NULL,
    state CHAR(2) NULL,
    year INT NULL
);

If stateit is null, but yearnot, or vice versa, the table will be inconsistent. I believe the following restriction FOREIGN KEYfixes it:

ALTER TABLE winner ADD CONSTRAINT FOREIGN KEY fk (name, state) REFERENCES tournament (name, state);

Is this the right way to ensure consistency? Is this circuit properly normalized?

Answer 1

№1: ,

, ON DELETE MATCH. Pg SQL, CREATE TABLE.

:

, , () . refcolumn , reftable . . , .

, () . : MATCH FULL, MATCH PARTIAL MATCH SIMPLE, . MATCH FULL null, . MATCH SIMPLE , . MATCH PARTIAL .

, , . ON DELETE . , ON UPDATE , , ​​ . , , . , ACTION , . :

, MS SQL, (MATCH SIMPLE MATCH PARTIAL) ( MATCH FULL). , MATCH FULL , NULL .