Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

How do we ban the removal of daughter records foreign key?

J
Jolied last edited by

Hello, everyone. There are two tables (A-B) and they are connected fk(foreign key) to one field. How do the daughter key not be removed when the parent key is removed with the help of ON DELETE CASCADE, and the record stayed in the table?
Reply Quote 0
1 Reply Last reply
K
kalyn last edited by

In your case, it's fine. http://www.mysql.ru/docs/man/SEC451.html
When removed, the value will be determined NULL
If you just take it away ON DELETE CASCADE
There's a mistake in the removal.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

P

How do remove the key from the dictionary?
Software Programming • python python 3.x dict • • Pearlaqua

2

0
Votes

2
Posts

0
Views

C

Option 1:In [230]: products = {} In [231]: products['category', 'title:викенд'] = {} In [232]: del products[('category', f'title:викенд')] In [233]: products Out[233]: {} Option 2:In [234]: products = {} In [235]: products['category', 'title:викенд'] = {} In [236]: products Out[236]: {('category', 'title:викенд'): {}} In [237]: _ = products.pop(('category', f'title:викенд')) In [238]: products Out[238]: {} If you want to figure out why this is happening,In [239]: x = 'category', 'title:викенд' In [240]: type(x) Out[240]: tuple In [241]: print(x) ('category', 'title:викенд') I mean, if we write a few values through the comma without specifying the type and without the brackets, we'll get the crusts.tuple)
D

Saturation of foreign keys?
Software Programming • sql oracle databank • • derk

2

0
Votes

2
Posts

0
Views

E

Foreign Keys in the database are optional, you create them if you want to leave with the database server the guarantee of relational integrity; for example: the bank will not let you create a registry with a foreign key pointing to a non-existent record, nor will you let delete a record for which there are other related records via FK.When the database is used by a single application and the application itself will take care of these validations, the FKs in the bank can be dispensed.One should consider the cost of doing these relational integrity checks in the application or letting the bank do them - probably the bank will do with much more performance as in addition to being an expert on the subject, it is closer to the data and has features that go beyond simple SQL queries to do so.Therefore, if there is no reason not to create the FKs, the standard is to create them; after all, the guarantee of relational integrity is one of the major proposals of the relational database servers.The saturation of foreign keys, it should be noted first if the modeling is correct, but it can also happen that it is correct and the excess of FKs cause some problems.There are database servers (SGBDs) that have FKs limits for a table, and among them there are those that block the creation of a new FK for an already saturated table and there are others that allow creation but then may present problems when trying to delete or change records for which there are many FKs. This can be a problem in systems with thousands of tables and tables with many columns.To avoid these types of problems in large databases, the information must be grouped into smaller tables instead of drawing gigantic tables.Update: Either way the SGBDs are always improving and decreasing their limitations.For example, https://technet.microsoft.com/en-us/library/ms143432(v=sql.110).aspx . I saw tables with more than 300 FKs incoming working in SQL Server 2005 but it was impossible to modify or delete records on some servers. https://msdn.microsoft.com/en-us/library/ms143432.aspx#Engine .I did not find documentation of the FK limit per table in Oracle.
M

How do you get a request from the RR to the MySQL database?
Software Programming • php mysql sql • • magpie

2

0
Votes

2
Posts

0
Views

S

PDO is the PDO class for the database.Connection to OBDI need to write this code for mySQL. https://phpdelusions.net/pdo_examples/connect_to_mysql#example ),$host = '127.0.0.1'; $db = 'test'; $user = 'root'; $pass = ''; $charset = 'utf8mb4'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; try { $pdo = new PDO($dsn, $user, $pass, $options); } catch (\PDOException $e) { throw new \PDOException($e->getMessage(), (int)$e->getCode()); } Incorporate their own values for connecting parameters and file db.php♪After that, write in crypt include 'db.php'; And then it will be accessible to the variable $pdowhich will be used for all requests to the database.Compliance with requestThe withholding of SELECT includes 3 phases:The preparation of the request, with all variables placed on the spot. It creates a variable $stmt.Compliance with the request where all variables involved in the request are sent separately, as a set, to the method $stmt->execute()♪Receiving the requested data. Data can be obtained in three different ways one line in the form of a mass or object, $stmt->fetch() a set of lines, if the request can return more than one line, $stmt->fetchAll()♪ The resulting mass can then be removed through foreach()♪The only meaning is that if we're requested the only column of the only line, $stmt->fetchColumn()In very few cases, the receipt can be done in the fourth way, a sample of one line in the cycle, but we won't consider it here.This is a scheme for any request involving variables. In this case, the code will be:include 'db.php'; $stmt = $pdo->prepare('SELECT money FROM bot WHERE user_id=?'); // подготовка $stmt->execute([$id]); // выполнение $money = $stmt->fetchColumn(); // получение
O

Reading and recording (editing) of OBD
Software Programming • php html database • • Oba22

2

0
Votes

2
Posts

0
Views

T

Put the variables in value that return the content you need.
S

Designing database with Category and Tags for Posts - Seeking for guidance/advice
SQL, Database Testing • mysql sc languagehema • • simrah

2

0
Votes

2
Posts

0
Views

One way to understand what's going on with normalization is to find out what normalization buys you. In other words, what's going to happen if you depart from any given normal form? For normalization forms second through fifth normal forms, the answer is "update anomalies". That is, when you insert, update, or delete rows, can you end up leaving things in a bad way? One particular kind of bad way is a database that contradicts itself, that stores a given assertion somewhere in the database and stores a contradictory assertion somewhere else in the database. Normalization helps with this by obviating the problem. That is, it cuts down or eliminates the possibility of contradictory assertions by assuring that facts are stored once, and only once. The fact is either right or it's wrong, but it's not self contradictory. There are other ways to avoid self contradictory databases, but they involve careful, disciplined programming. There are situations where a non normalized approach gives you more benefits than a normalized approach. But this calls for judgement, and judgement comes with experience. With regard to EAV, one attraction to EAV is that it allows you to get started without coming up with a logical model for the structure of the data. The reason is that any logical model whatsoever can conform to EAV. So why doesn't everybody do EAV? Because sometimes, the data that results is relatively worthless compared to the expected value when the database was built. I'm not saying EAV is always wrong. I am saying that it often is. Again, this calls for judgement, and judgement comes with experience.
J

Cannot add foreign key constraint
Software Programming • mysql • • Jolied

2

0
Votes

2
Posts

0
Views

J

Every client is a person (It can be Physics or Legal);Everyone has a type (physical or legal);I created a modelling similar to yours but with some adjustments:Below the script for creating objects:CREATE TABLE IF NOT EXISTS `mydb`.`Cliente` ( `codigo` INT(11) NOT NULL AUTO_INCREMENT, `numero_contrato` INT(11) NOT NULL, `dtcadastro` DATETIME NOT NULL, `dtrescisao` DATETIME NULL DEFAULT NULL, `pessoa` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`codigo`), INDEX `fk_cliente_01_idx` (`pessoa` ASC), CONSTRAINT `fk_cliente_01` FOREIGN KEY (`pessoa`) REFERENCES `mydb`.`Pessoa` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE IF NOT EXISTS mydb.Pessoa ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, tipo INT(10) UNSIGNED NOT NULL, cpf_cnpj VARCHAR(14) NULL DEFAULT NULL, nome VARCHAR(60) NULL DEFAULT NULL, email VARCHAR(150) NULL DEFAULT NULL, cep INT(11) NULL DEFAULT NULL, logradouro VARCHAR(100) NULL DEFAULT NULL, numero VARCHAR(20) NULL DEFAULT NULL, complemento VARCHAR(30) NULL DEFAULT NULL, bairro VARCHAR(70) NULL DEFAULT NULL, cidade VARCHAR(70) NULL DEFAULT NULL, uf VARCHAR(2) NULL DEFAULT NULL, PRIMARY KEY (id), INDEX fk_pessoa_01_idx (tipo ASC), CONSTRAINT fk_pessoa_01 FOREIGN KEY (tipo) REFERENCES mydb.TipoPessoa (codigo) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE IF NOT EXISTS mydb.TipoPessoa ( codigo INT(10) UNSIGNED NOT NULL, nome VARCHAR(20) NULL DEFAULT NULL, PRIMARY KEY (codigo)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;Just a more research tip about MER (Model Entity and Relationship), and try to use tools for modeling, in the case of MySql you can use Workbench I don't know if you know: https://dev.mysql.com/downloads/workbench/ it will give you a better view of your project I hope that helps you!
R

Add the removal button next to the recording
Software Programming • javascript jquery lines • • Raziyah00

2

0
Votes

2
Posts

0
Views

G

For example http://jsfiddle.net/4o283yn3/1/ ♪First important change: add the button to the table:<tr><td><button role=delete>del</button></td><td>" + userName + "</td><td>" + userRole+ " </td></tr> The second major change is to create a click processor on this button:$("#tableUsers").on('click', 'button[role="delete"]', function(){ $(this).closest('tr').remove(); }); And I recommend that we read the templates for general development.
J

How to have multiple values in a sql consultation
Software Programming • mysql • • jonetta

2

0
Votes

2
Posts

0
Views

K

Although without further information it is difficult to give a concrete answer, as an example, in a table called cars, which has, among other possibles, the model fields, brand and price, a consultation could be:SELECT modelo, precio FROM coches WHERE marca NOT IN ('Opel', 'Seat', 'Mercedes') This query selects the model and price of all the records of the table that your brand is neither Opel nor Seat nor Mercedes.
R

Consultation inner join with AND does not bring results
Software Programming • mysql sql • • Raziyah00

2

0
Votes

2
Posts

0
Views

K

As they told you in comments, your problem is solved by making a INNER JOIN to the same table more than once, placing a different alias.The command AS that I added is totally optional, always goes before an alias, and it is a good practice to recognize them in plain sight not to be confused with the original name of the table.Also, if your alias consists of two separate words, you can indicate it by placing brackets [].SELECT t.num_guia_despacho ,t.fecha_transferencia ,s.servicio ,origen.nombre_bodega AS [Bodega Origen] ,destino.nombre_bodega AS [Bodega Destino] FROM transferencia_bodegas AS t INNER JOIN det_transferencia_bodegas AS d ON t.id = d.transferencia_bodega_id INNER JOIN bodegas AS destino ON destino.id = d.destino_bodega_id INNER JOIN bodegas AS origen ON origen.id = d.origen_bodega_id INNER JOIN servicios AS s ON s.id = t.servicio_id WHERE t.id = d.transferencia_bodega_id
M

Can I relate a foreign key to a table attribute with another foreign key to an attribute of another table?
Software Programming • database • • meriah

2

0
Votes

2
Posts

0
Views

A

Actually, the serious NI response. If both are foreign keys, they are definitely the main keys of a table. The idea of naming something as a foreign or primary key is just an idea to identify, if that key actually means that they are (related) data from another table, or if it is a proper key that unequivocally identifies a record of the table itself. Theoretically, no DB will let you relate a foreign key to another foreign key. However, that primary key may be in more than one board as a foreign key. Then nothing would prevent you from making a query to relate them.Let's say we have the country table with their respective primary key Pais CodPais PK int Pais varchar And we have the tablesPersonas CodPersona PK int Nombre varchar CodPais FK -> Pais Banderas CodBandera PK int Color varchar CodPais FK -> Pais Nothing would prevent the boards from joining people and flags, even if it had no sense. But we could show for every person, all the colors of flags they have if they are in the same country.
Massive import of excel file.CSV Notice: Undefined offset: 1
Software Programming • php mysql phpexc languageel • • Alberto

2

0
Votes

2
Posts

0
Views

R

The problem seems to be here: $datos[0],$datos[1],$datos[2]If any of these sub-indices are not initiated, this Notice comes out.Solutions there are several but it depends on the php version you use: for 5.x and above: you have to ask if the index is created and if not a default value is passed.isset($datos[0])?$datos[0]:'dato por defecto' For 7.x you can use the ?? (Null Coalesce Operator).$datos[0] ?? 'valor por defecto' You take the NOTICE off with this.
Removal from multiple tables by the same key
Software Programming • sql oracle • • carriann

2

0
Votes

2
Posts

0
Views

Impossible.If you need the integrity of the disposal, turn the removal from all the tables into one transaction, leaving the transaction entirely in the event of problems when removed from any table.
Y

Accent problems with PHP and MySQL
Software Programming • javascript php mysql • • yajahira

2

0
Votes

2
Posts

0
Views

P

Try this and see if it solves the problem:ALTER DATABASE `sua_base` CHARSET = UTF8 COLLATE = utf8_general_ci;
C

How do I do so I don't remove all the records through this storage procedure?
Software Programming • mysql sql stored procedure • • chanisef

2

0
Votes

2
Posts

0
Views

Do not use as the name of the input parameter the same name of the field, because ambiguity is created between the names. Actually, your query is comparing whenever the input parameter is equal to itself, condition that will meet all the rows of the table.You could solve it by qualifying the names, but it is very simple and easier to understand, as I said, to avoid collision between the names:CREATE PROCEDURE EliminarInstructor( in pIdInstructor INT ) BEGIN DELETE FROM instructor WHERE id_Instructor = pIdInstructor; END As I told you, you can qualify the name, like this:CREATE PROCEDURE EliminarInstructor( in Id_Instructor INT ) BEGIN DELETE FROM instructor WHERE instructor.id_Instructor = Id_Instructor; END
K

Query in MongoDB
Software Programming • database mongodb • • kalena

2

0
Votes

2
Posts

0
Views

N

You have to look at donatioFee's size greater than 0 with $size and $gt. You'd have something like that.db.collection.find({donationFee:{$size: {$gt: 0}} })

How do we ban the removal of daughter records foreign key?

Suggested Topics

How do remove the key from the dictionary? Software Programming • python python 3.x dict • • Pearlaqua

Saturation of foreign keys? Software Programming • sql oracle databank • • derk

How do you get a request from the RR to the MySQL database? Software Programming • php mysql sql • • magpie

Reading and recording (editing) of OBD Software Programming • php html database • • Oba22

Designing database with Category and Tags for Posts - Seeking for guidance/advice SQL, Database Testing • mysql sc languagehema • • simrah

Cannot add foreign key constraint Software Programming • mysql • • Jolied

Add the removal button next to the recording Software Programming • javascript jquery lines • • Raziyah00

How to have multiple values in a sql consultation Software Programming • mysql • • jonetta

Consultation inner join with AND does not bring results Software Programming • mysql sql • • Raziyah00

Can I relate a foreign key to a table attribute with another foreign key to an attribute of another table? Software Programming • database • • meriah

Massive import of excel file.CSV Notice: Undefined offset: 1 Software Programming • php mysql phpexc languageel • • Alberto

Removal from multiple tables by the same key Software Programming • sql oracle • • carriann

Accent problems with PHP and MySQL Software Programming • javascript php mysql • • yajahira

How do I do so I don't remove all the records through this storage procedure? Software Programming • mysql sql stored procedure • • chanisef

Query in MongoDB Software Programming • database mongodb • • kalena

How do remove the key from the dictionary?
Software Programming • python python 3.x dict • • Pearlaqua

Saturation of foreign keys?
Software Programming • sql oracle databank • • derk

How do you get a request from the RR to the MySQL database?
Software Programming • php mysql sql • • magpie

Reading and recording (editing) of OBD
Software Programming • php html database • • Oba22

Designing database with Category and Tags for Posts - Seeking for guidance/advice
SQL, Database Testing • mysql sc languagehema • • simrah

Cannot add foreign key constraint
Software Programming • mysql • • Jolied

Add the removal button next to the recording
Software Programming • javascript jquery lines • • Raziyah00

How to have multiple values in a sql consultation
Software Programming • mysql • • jonetta

Consultation inner join with AND does not bring results
Software Programming • mysql sql • • Raziyah00

Can I relate a foreign key to a table attribute with another foreign key to an attribute of another table?
Software Programming • database • • meriah

Massive import of excel file.CSV Notice: Undefined offset: 1
Software Programming • php mysql phpexc languageel • • Alberto

Removal from multiple tables by the same key
Software Programming • sql oracle • • carriann

Accent problems with PHP and MySQL
Software Programming • javascript php mysql • • yajahira

How do I do so I don't remove all the records through this storage procedure?
Software Programming • mysql sql stored procedure • • chanisef

Query in MongoDB
Software Programming • database mongodb • • kalena