2

I am using nodejs and mysql.
In my table event, assumed that there are currently 3 rows of data.

There should be exactly one featured Event.

GET /event

[
 {
    id: 1,
    content: "content 1",   
    isFeaturedEvent: false
 },
 {
    id: 2,
    content: "content 2",   
    isFeaturedEvent: true
 },
 {
    id: 3,
    content: "content 3",   
    isFeaturedEvent: false
 },
]

Is it possible to make sure there is always only/at most one isFeaturedEvent: true, in server side/database side?
For example, if the third event(id:3) change the value of isFeaturedEvent from false to true, it will then throw error.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
CCCC
  • 5,665
  • 4
  • 41
  • 88
  • 3
    Similar question has been answered here : https://stackoverflow.com/questions/4617914/how-to-create-a-unique-constraint-on-a-boolean-mysql-column – Vaibhaw Agrawal May 26 '21 at 08:56
  • Yes, TRUE / NULL would be the way to go, if you don't want anything more complex (another table). – Andrey Popov May 26 '21 at 09:12
  • @Vaibhaw Agrawal I tried to create new table `FeaturedEvent`, but I am not sure the schema for this table, should it only contain one column(id of table `event`)? – CCCC May 27 '21 at 09:40

2 Answers2

0

If you want to do this without a separate data item that's the id of the featured event, you could try this.

  1. Add an attribute to your model (a column to the table) with the DataTypes.DATE data type, including date and time. Let's call it featured.

  2. Put a unique index on that column, so no two rows can have the same value.

  3. To mark a row as featured, update the value of that attribute to the current timestamp.

  4. To find the featured item, retrieve the rows in descending order of your featured attribute and take the first one.

So, your featured item is the one with the most recent featured timestamp.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

Create a table FeaturedEvent with a constraint of having only one row that is having true value. So if you try to insert another row it would throw an error. To constraint the table to have only one row :

CREATE TABLE FeaturedEvent ( ID bool PRIMARY KEY NOT NULL DEFAULT TRUE , featuredEventID int, CHECK (ID!=false) );

Remove the isFeaturedEvent field from the main table

Refer to answer for creation of table with one row : How to allow only one row for a table?

Vaibhaw Agrawal
  • 1