SQL foreign key
lazarus / 16-Databases

----------------------------------------------------------------------
flori · 2021-02-11 13:49

Hi!
I have two tables: 1. Years, 2. Farmers

I want to make the names of the farmers appear only once in a given
year, but may appear again the following year.
The name of the farmer should not appear twice a year, for example:
Year    Name
2021   Flori
2021   Flori
I want to set this:
Year    Name
2021   Flori
2021   Flori
2022   Flori

I use it foreign key with Years in Farmers table!
Thank you

----------------------------------------------------------------------
Zvoni · 2021-02-11 14:31

SELECT DISTINCT ?
Without seeing any more code (and no, without knowing the db-format,
i'm not going to download your file)

----------------------------------------------------------------------
flori · 2021-02-11 15:17

unfortunately the PK-primary key does not provide a solution to this
problem

----------------------------------------------------------------------
flori · 2021-02-11 15:19

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE FARMERS (    "YEAR"  INTEGER,    NAME    VARCHAR(20),    ID    
 SMALLINT);ALTER TABLE FARMERS ADD PRIMARY KEY (ID); ALTER TABLE
FARMERS ADD CONSTRAINT FK_FARMERS_1 FOREIGN KEY ("YEAR") REFERENCES
YEARS ("YEAR");

----------------------------------------------------------------------
dseligo · 2021-02-12 00:21

You could add unique index, that would prevent duplicate entry of
year/name pair:

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE
FARMERS        ADD UNIQUE INDEX YEAR_NAME ("YEAR", "NAME");

----------------------------------------------------------------------
flori · 2021-02-12 20:22

Thank you