Auto-generate, auto-increment primary key

Ask the community
,
1

Is there a way to create a database with a column defined as auto-generated, auto-incremented primary key?

1 Like
2

You can achieve an auto-generated, auto-incremented field with a few lines of code using a data hook. Most probably, you’ll want to use the beforeInsert hook.

You can learn more about data hooks by reading About Data Hooks , How to Use Data Hooks , and referring to the data hooks API .

3

how would I tackle foreign keys (FK). Say that I have 2 tables. Student and Student Additional Info. In Student, I have StudentID as PK. I then want to save it so that when I insert a row in Student Additional Info, I can update a column in that table with my saved StudentId which becomes the FK in this table.

4

This is a little hard to answer without knowing your exact situation. It depends on how you’re creating the new item in Student Additional Info.

If you’re using a form or any other means that uses a dataset you can use the dataset’s onBeforeSave() function to register an event handler that add the StudentID to the item before it is saved.

That would look something like this:

$w("#StuAddDataset").onBeforeSave( () => {
   let studentId = // get the studentID here
   $w("#StuAddDataset").setFieldValue('studentId', studentId); 
} );

This approach is used in the Reviews example. Check out the code in the Post a Review lightbox. (Note: I just checked and the page for this example is currently showing the wrong example. It should be fixed soon.)

If you’re using the Data API to add the new item to Student Additional Info, you can use the API to add the studentId to the item as well.

Let me know if this helps or if your situation is not one that I though of.

Also, you might want to think about adding a feature request (or two) regarding auto-generated primary keys and working with foreign keys.

2 Likes
5

I wrote the following hook (the code should be placed to data.js):

export async function MyTable_beforeInsert(item, context) {  
       var max = await getMax('MyTable', 'myField');
       if(max > 0){
              item.myField = max + 1;       
       }else{
              var count = await getCount('MyTable');
              item.myField = count + 1;
       }
       return item;
}

async function getCount(tableName){
       return await wixData.query(tableName).count();
}

async function getMax(tableName, fieldName) {
	var max = 0;
	var res = await wixData.aggregate(tableName).max(fieldName, 'fieldMax').run();
	if(res.items.length === 1){ max = res.items[0].fieldMax; }
	return max;	
}
6

Nice one @oshulyak

Any way to put a Alphabetical character in there? For example id like the code to be YFD1, YFD2, YFD3 and so on rather than just 1. 2, 3 and so on

Thanks

Dan

7

Where is data.js?