Menggunakan pencetus dan kekangan untuk mengehadkan bilangan rekod dalam MySQL

Question

Saya mempunyai jadual yang dipanggil BFF yang menyimpan ID pengguna dan ID pengguna kawan baik, dan saya mahu mengehadkan jadual kepada 3 kawan baik untuk setiap pengguna yang berbeza.

Maksud saya jika struktur jadual ialah:

BFFs(userID, userID)

Rekodnya ialah:

(3286, 1212)
(3286, 4545)
(3286, 7878)

Dalam kes ini, pengguna dengan ID 3286 tidak sepatutnya dibenarkan mempunyai rekod baharu, contohnya (3286, xyzt).

Saya menulis pencetus ini tetapi saya tidak pasti:

CREATE TRIGGER BFFControl
BEFORE INSERT ON BFFs
FOR EACH ROW
DECLARE
    numberOfBFFs INTEGER;
    max_BFFs INTEGER := 3;
BEGIN
    SELECT COUNT(*) INTO numberOfBFFs
    FROM BFFs
    WHERE sender =: NEW.sender

    IF :OLD.sender =: NEW.sender THEN
        RETURN;
    ELSE
        IF numberOfBFFs >= max_BFFs THEN
            RAISE_APPLICATION_ERROR(-20000, 'Users are allowed to have at most thre friends.');
        END IF;
    END IF;
END;
/

Bagaimanakah saya harus memperkayakannya semula pada jadual hubungan melalui penegasan atau pencetus?

Terima kasih

Answer 1

Sebagai tambahan kepada jawapan ypercubes (yang memanfaatkan DRI untuk menguatkuasakan peraturan anda), anda juga boleh melakukan LEFT JOIN menggunakan MIN untuk mendapatkan 1, 2 atau 3 seterusnya daripada tiga jadual setiap ID pengguna

Answer 2

Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa

Answer 3

Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa Sofa

Answer 4

Tambah satu lagi lajur, FriendNumber dan kekangan kunci asing pada jadual rujukan yang mengandungi tepat 3 baris:

CREATE TABLE Three
( friendNumber TINYINT NOT NULL 
, PRIMARY KEY (friendNumber)
) ;

INSERT INTO Three(friendNumber) 
  VALUES
    (1), (2), (3) ;

CREATE TABLE BFFs
( userID       INT NOT NULL 
, friendID     INT NOT NULL
, friendNumber TINYINT NOT NULL
, PRIMARY KEY (userID, friendID)
, UNIQUE (userID, friendNumber)
, FOREIGN KEY userID
    REFERENCES Person(userID)
, FOREIGN KEY friendID
    REFERENCES Person(userID) 

, FOREIGN KEY friendNumber           --- this ensures that a user can have
    REFERENCES Three(friendNumber)   --- max of 3 friends
) ;

Kemudian anda boleh menambah:

INSERT INTO BFFs 
  (userID, friendID, friendNumber)
VALUES
  (3286, 1212, 1) ,
  (3286, 4545, 2) ,
  (3286, 7878, 3) ;

---

Atau seperti yang dicadangkan @gbn, seperti ini (jadi lajur diisi secara automatik):

INSERT INTO BFFs 
    (userID, friendID, friendNumber)
  SELECT 
       3286 AS userID
     , 8989 AS friendID
     , COALESCE(
         ( SELECT MIN(Three.friendNumber)
           FROM Three 
             LEFT JOIN BFFs AS b  
               ON  b.friendNumber = Three.friendNumber
               AND b.userID = 3286
           WHERE b.friendNumber IS NULL
         ), 4
       ) AS friendNumber
  FROM dual

Answer 5

Asafa mematuhi undang-undang dan menghantar mesej Asafa mematuhi undang-undang dan menghantar mesej Asafa mematuhi undang-undang dan menghantar mesej Asafa mematuhi undang-undang dan menghantar mesej