SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 24/07/2013, 09:31 PM   #21
Sinner
Gangsta
 
Sinner's Avatar
 
Join Date: Aug 2010
Location: On This Mortal Coil
Posts: 819
Reputation: 292
Default Re: [MySQL] Table structure and foreign keys

Quote:
Originally Posted by dusk View Post
Firstly, i know that i'm bumping a pretty old thread, but since it's a tutorial i think it's okay.

I just wanted to ask, is there like part 2 of this? It's really a great tutorial and i want to learn more about table structures and other cool MySQL stuff (like these relations).

The manual is pretty boring....This thread was way clearer for me.
This gives you a pretty good idea:
http://databases.about.com/od/specif...malization.htm
__________________
Please use the search feature before asking a question.
Sinner is offline   Reply With Quote
Old 24/07/2013, 11:20 PM   #22
DarrenReeder
Huge Clucker
 
DarrenReeder's Avatar
 
Join Date: Apr 2010
Posts: 494
Reputation: 5
Default Re: [MySQL] Table structure and foreign keys

Aha, nice tutorial.

I just came back after a few years and im impressed by how much the samp community is improving...

1 thing I didnt notice was that you didnt mention the relationship types? Which is the biggest thing about relational databases (1 to many, 1 to 1, many to many)... I know the tutorial is on foreign keys, but the type of relationship is important with this
DarrenReeder is offline   Reply With Quote
Old 25/07/2013, 10:04 AM   #23
Sinner
Gangsta
 
Sinner's Avatar
 
Join Date: Aug 2010
Location: On This Mortal Coil
Posts: 819
Reputation: 292
Default Re: [MySQL] Table structure and foreign keys

Quote:
Originally Posted by DarrenReeder View Post
I know the tutorial is on foreign keys, but the type of relationship is important with this
If you follow the normalization forms, separation of data into different tables is is automatically forced because there really is no other way of doing it.

Let's say you have a "users" and a "items" table. The logical reasoning is, "a user HAS items", or more importantly "1 user has many items". Or if you want to be absolutely correct, and this is very important while designing a database, "1 OR 0 users CAN HAVE 0 or more items". When you transorm this into a ERD and later into a logical and normalized database you'll automatically end up with a 1-to-many table. As in, 1 "user" can have many "items". It's pure logic, you end up with a certain relation type just because there is not other way of doing it.

Asking which relation you should use is not a fair question. Instead, ask yourself how your data should be organized and what relation they ave to each other.

Database Design is for most people here a quite advanced topic, but I'm glad it's being brought up because if you work with a relational database there really is nothing more important.
__________________
Please use the search feature before asking a question.
Sinner is offline   Reply With Quote
Old 15/04/2014, 03:42 PM   #24
Mellnik
Gangsta
 
Mellnik's Avatar
 
Join Date: Dec 2011
Location: Paradise Falls, CAℕADA
Posts: 794
Reputation: 369
Default AW: [MySQL] Table structure and foreign keys

I've tried your table design and connected several tables with the accounts table. But sometimes following error comes up:

Code:
[ERROR] CMySQLQuery::Execute[] - (error #1452) Cannot add or update a child row: a foreign key constraint fails (`nefserver`.`achievements`, CONSTRAINT `achievements_ibfk_1` FOREIGN KEY (`id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Code:
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL,
   other stuff here,
   PRIMARY KEY (`id`),
   UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45802 ;


CREATE TABLE IF NOT EXISTS `achievements` (
  `id` int(10) unsigned NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  `unlockdate` int(10) unsigned NOT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Constraints for table `achievements`
--
ALTER TABLE `achievements`
  ADD CONSTRAINT `achievements_ibfk_1` FOREIGN KEY (`id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Any idea, Vince? :/

Edit: Found the problem, the query tried to insert into `achievements` using a index key not available in `accounts`.

Last edited by Mellnik; 16/04/2014 at 02:34 PM.
Mellnik is offline   Reply With Quote
Old 19/04/2014, 10:09 AM   #25
gotwarzone
Banned
 
Join Date: Sep 2013
Posts: 514
Reputation: 22
Default Re: [MySQL] Table structure and foreign keys

Hi. Vince, Sorry for off topic, I also tried to pm you or wall you but it wasn't allowed. So, I saw your post regarding the efficient way of optimizing the script lines but the thread I made has been delete for unknown reason. Anyways, just a question the code you've posted is also like the same way of using Indentation Styles? Like..

Allman style
Code:
COMMAND:heal(playerid, params[])
{
    if(IsPlayerAdmin(playerid))
    {
        SetPlayerHealth(playerid, 100);
    }
    else
    {
        Kick(playerid);
    }
}
K&R style
Code:
COMMAND:heal(playerid, params[]) {
    if(IsPlayerAdmin(playerid)) {
        SetPlayerHealth(playerid, 100);
    } else {
        Kick(playerid);
    }
}
gotwarzone is offline   Reply With Quote
Old 26/08/2015, 09:58 AM   #26
Ralfie
Gangsta
 
Join Date: Nov 2013
Posts: 686
Reputation: 150
Default Re: [MySQL] Table structure and foreign keys

Hi,
I'm not so professional when it comes to MYSQL. Getting this:

Code:
SQL Error (1005): Can't create table 'SAMP_294.#sql-2524_6d64' (errno: 150) Foreign key constraint is incorrectly formed
Code:
CREATE TABLE `Table1` (
	`Field` INT(11) NOT NULL AUTO_INCREMENT,
	`Field1` VARCHAR(21) NOT NULL,
	`Field2` TINYINT(4) NOT NULL DEFAULT '0',
	`Field3` VARCHAR(130) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Table2` (
	`Field` INT(11) NOT NULL,
	`Field1` INT(5) NOT NULL
);

ALTER TABLE `Table1`  
ADD FOREIGN KEY (`Field`)  
REFERENCES `Table2` (`Field`)  
ON UPDATE CASCADE
ON DELETE CASCADE

Last edited by Ralfie; 19/10/2015 at 05:26 PM.
Ralfie is offline   Reply With Quote
Old 26/08/2015, 10:47 AM   #27
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,084
Reputation: 2647
Default Re: [MySQL] Table structure and foreign keys

You're adding the foreign key to the wrong table. Achievements is the child table, so that table should have the foreign key. Foreign key accountid references accounts id.
__________________
Vince is offline   Reply With Quote
Old 19/10/2015, 05:27 PM   #28
Ralfie
Gangsta
 
Join Date: Nov 2013
Posts: 686
Reputation: 150
Default Re: [MySQL] Table structure and foreign keys

Hi, sorry for the bump. Again facing problems with the query:

CREATE TABLE `Table1` (`Field` INT(11) NOT NULL, `Field1` VARCHAR(21) NOT NULL, PRIMARY KEY (`Field`));
CREATE TABLE `Table2` (`Field1` VARCHAR(21) NOT NULL, `Field` INT(11) NOT NULL DEFAULT '0');

ALTER TABLE `Table2`
ADD FOREIGN KEY (`Field1`)
REFERENCES `Table1` (`Field1`)
ON UPDATE CASCADE
ON DELETE CASCADE

SQL Error (1005): Can't create table 'SAMP_294.#sql-1673_41aa' (errno: 150) Foreign key constraint is incorrectly formed *

EDIT: Looks like my `Field1` in `Table1` should be a unique index - sadly this wont work as i already have Field as a primary key.

Last edited by Ralfie; 19/10/2015 at 07:25 PM.
Ralfie is offline   Reply With Quote
Old 22/10/2015, 08:34 AM   #29
nGen.SoNNy
Gangsta
 
nGen.SoNNy's Avatar
 
Join Date: Sep 2009
Location: Romania
Posts: 990
Reputation: 65
Default Re: [MySQL] Table structure and foreign keys

I will read again this to refresh my memory
__________________
If i've helped you, please click
nGen.SoNNy is offline   Reply With Quote
Old 10/12/2016, 09:46 AM   #30
knuckleduster5
Little Clucker
 
knuckleduster5's Avatar
 
Join Date: Oct 2015
Posts: 45
Reputation: 11
Default Re: [MySQL] Table structure and foreign keys

deldeleted
knuckleduster5 is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Table JaKe Elite Scripting Help 5 13/11/2012 07:53 AM
MySQL: Update all values in table from another table kurta999 Scripting Help 8 23/08/2012 08:55 PM
MYSQL Table thefatshizms Scripting Help 3 31/07/2012 02:06 AM
mysql table bug juraska Scripting Help 3 15/12/2011 07:34 AM


All times are GMT. The time now is 09:24 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.