Disable And Enable A Foreign Key In Mysql 5
i have created primary key / foreign keys...but then when I am trying to change
the data later, i am getting error, now i want to disable the foreign key,
change my data, and enable it again
but i am unable to find the correct syntax for this.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Disable MySQL Logging
I was a bit surprised, when I saw that mySQL create a huge logs, entierly useless for my testing server purpose and since the machine is relatively (a lot, VIA C is nowhere near fast) slow, I looking for ways how to completely disable these unnecessary logging It is a Win2k server with mySQL 4.1.15-nt :-) PS. how to convince the service to use as default latin2 coding? --default-character-set=latin2_czech_ci Is mentioned into the guide, but the server did not accept it at all in properties and trying to set it into the ini file was undocumented and sometimes cause mySQL not to start + it still not working and use the damn utf8 crap, that kill the czech characters :(((
MySQL 3 - How To Enable Tcp/ip?
I think that in my mySql named pipes are enabled by default. I need to enable tcp/ip now. Can somebody give me strict instructions how to do this?
Enable Mysql Logging
how common it is for production websites (sites that get used frequently) such as small online stores etc, to enable logging on the database end. Is it that common ? Seems like it might be a performance hit and maybe it might be a good idea only if one suspected something wasnt right (cyber attacks). Any php / mysql errors will be hopefully caught and logged by other means, so the only reason i see to log database action is for seeing just who is trying to get access.
Enable Unicode In MySQL 4.1.0-alpha
I have read that Unicode (UTF-8) was not enabled by default in the mysql-4.1.0-alpha binaries. How can I enable it? What should I change in the code to do so?
Disable Log
I can disable bin logs SET SQL_LOG_BIN=0; But How do I disable and then enable the slow and general query log?
Disable Replication
I've got a server that used to use replication but no longer has any slaves. To turn this off and stop generating the bin files, do I just need to comment out the bin-log command in the cnf file then restart?
Disable A Trigger
I have found a need to disable a trigger for a short period of time while executing a query. Rather than delete the trigger and re-create it later, I wanna disable trigger for the tabe and after the execution enable the trigger.
Disable Bell
how to disable that annoying warning bell in the command line version of mysql. I am using mysql 5 on windows at the moment. I really don't appreciate the world knowing that mysql returned an error.
Disable A Single Database
Hi everyone I was wondering if there was a way to disable a single database with having to shutdown mysql or rename tables?
Disable -- User Option
I would like to use unix user names to login to mysql. If I create a mysql user xxadmin and leave the password empty other unix users can use the xxadmin with the --user option . How can I make sure that only the unix user xxadmin can login as mysql user xxadmin ?
Disable Privilege System
If I set skip-networking, what is the security risk if I also skip-grant-tables? Is there any performance benefit that might justify leaving the privilege system disabled? This is a dedicated database that runs on the same machine that hosts the application that it supports.
How To Disable Binlog For Specific Table
For security reasons, we need a single table (containing sensible information) to be excluded from going to binlog. I have found out as much, that I can use SET SQL_LOG_BIN = {0|1} to switch logging on/off per connection, but I would prefer if I could simply exclude the table all together. The table is a ENGINE=MEMORY type table btw.
How To Disable Unused Table Types
I only use MyISAM for all my web applications, and that should suffice for years to come, so I'm wondering how to disable all other data types from the server, so it starts as only capable of dealing with MyISAM tables. What other benefits there are in doing so, apart from perhaps a greater memory saving? Sometime ago I thought of using/experiment with InnoDB type but it's initial 10MB footprint and all the changes in the existing code to make it transactional are too much perhaps. I also read somewhere InnoDB requires alot more memory than MyISAM, server only has 1GB.
Disable Remote Root Login
I would like to keep root non password protected locally. And disable root login via phpmyadmin and other remote mamagement tools. For remote login I would have seperate users set up.
How To Disable Auto Truncation Of Char Binary Column
Why is a single space truncated in the following example? Leif alter table CURRENCY change column THOUSANDS_SEP THOUSANDS_SEP char(1) binary not null , type=MyISAM UPDATE CURRENCY SET THOUSANDS_SEP = ' ' WHERE NAME = 'SEK' SELECT LENGTH(THOUSANDS_SEP) AS L FROM CURRENCY WHERE NAME = 'SEK' Result: L = 0
Unchecking The Enable TCP/IP Option (was "Mysql Problem")
I have had a problem with Mysql so I downloaded it again but after configuring it would not start so reconfigured and unchecked the enable TCP/IP box in the networking options and Mysql worked fine. But can’t get PHP to connect so I think the enable TCP/IP box should be ticked. So I thought I would do better with a complete re start. I have removed and reloaded several times but the same problem occurs every time.
How Do I Enable Logging?
I need to enable MySQL logging. I looked in the my.ini file and did not see any logging options. Where do I turn on logging? I am using Windows.
Enable Warnings
If I try to insert a value such as 123456 into a tinyint field, it will just insert the largest value that that field can contain. I'm wondering if there is a setting in MySQL that will trigger an error/warning when this occurs instead. Something like ANSI_WARNINGS in MsSql.
Enable SSL Feature
I'd like to launch a mysql server with SSL protocol enabled. I'm working on windows XP OS. I didn't find clear information about how to do that. In the user manual I can find in the 5.6.7.2 section that I have to launch an "@command{configure}" command with some options. The problem is : I can't find a "configure" command. The only thing I'm able to find is a shell script called "conf.sh" but I'm working on windows. Do I have to install cygwin or that kind of stuff ? I've read somewhere else that I had to compile mysql to be able to use it in SSL mode. I'm a bit lost with those informations.
Enable Pooling On My Sql
How do i enable pooling on mysql connection? this is my string. DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=dfdfd;" + "DATABASE=dbname;" + "UID=" + UserId +";" + "PASSWORD=ff;" + "OPTION=3
Enable InoDB Tables
I am running SuSE 9.0. I have installed, MySql 4.0.15. Apparantly versions 4.0 and above are supposed to come with InnoDB tables already enabled, this is not the case with mine, the have_innodb variable is at NO. I edit the my.cnf file to include the following as per mysql manual innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 However when I try and start mysqld up again I get error messages saying that all the variables are not recognised. These variables are mentioned again and again in help files to enable innodb tables in MySql.
How To Enable Mime Type
Sorry for another post but how do I enable mime types for my mysql installation. I have looked in the config file and I cannot find it.
Way To Enable Csv Engine In Mysql5.0.27
I want to find if there is any way to enable csv engine in mysql5.0.27.The manual says while configuring mysql use " --with-csv-storage-engine " but i'm not getting any idea from this.
How Do I Enable Binary Logging
I would like to enable binary logging on my database.Am using winXP The information i have talks of adding some lines to my.cnf file then restarting mysql.
Enable Remote Connections
When we connect to the database server on the same PC, we give the server as "localhost" When I tried to connect to the database on another PC by giving the hostname as server it gives the error as Access denied for user 'root'@'hostnmae' Can someone tell me how do I make sure remote connections are open for this mysql database and how can I identify it from another PC?
Failed To Enable Constraints
I have a desktop application in Visual Basic .NET working with a MySQL database. I got to populate the information from my database into the text fields on the form. I can navigate through the records (next, first, previous, last) BUT.. I can't get it to update the database. I am getting the following message: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints" This is not a big table, it only has 8 fields (Name, lastname, address, city, zip, state, phonenumber) and the only non-null field that I am NOT INPUTTING manually on the form is the customerID(PK), which is supposed to be assigned automatically for mySql.
How To Enable Prefix Compression On MyISAM Indexes?
Anyone know? The manual suggests prefix compression (packing) is the default for all character type indexes, but "Packed" is always "NULL" in show indexes results... mysql> show indexes from _1_log; +--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | _1_log | 0 | PRIMARY | 1 | ID | A | 27456 | NULL | NULL | | BTREE | | | _1_log | 1 | ip | 1 | ip | A | 3432 | NULL | NULL | YES | BTREE | | | _1_log | 1 | countryCode | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | countryCode | 2 | countryCode | A | 188 | NULL | NULL | YES | BTREE | | | _1_log | 1 | language | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | language | 2 | language | A | 85 | NULL | NULL | YES | BTREE | | | _1_log | 1 | browserFamily | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | browserFamily | 2 | browserFamily | A | 42 | NULL | NULL | YES | BTREE | | | _1_log | 1 | browser | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | browser | 2 | browserFamily | A | 42 | NULL | NULL | YES | BTREE | | | _1_log | 1 | browser | 3 | browserVersion | A | 76 | NULL | NULL | YES | BTREE | | | _1_log | 1 | os | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | os | 2 | os | A | 25 | NULL | NULL | YES | BTREE | | | _1_log | 1 | resolution | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | resolution | 2 | resolution | A | 83 | NULL | NULL | YES | BTREE | | | _1_log | 1 | depth | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | depth | 2 | depth | A | 10 | NULL | NULL | YES | BTREE | | | _1_log | 1 | javascriptEnabled | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | javascriptEnabled | 2 | javascriptEnabled | A | 4 | NULL | NULL | YES | BTREE | | | _1_log | 1 | searchEngine | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | searchEngine | 2 | searchEngine | A | 13 | NULL | NULL | YES | BTREE | | | _1_log | 1 | uniqueVisit | 1 | uniqueVisit | A | 2 | NULL | NULL | YES | BTREE | | | _1_log | 1 | visitorId | 1 | visitorId | A | 5491 | NULL | NULL | YES | BTREE | | +--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ 23 rows in set (0.00 sec) This is MySQL 5.0.27.
Enable Root Access From Remote Machines?
I just setup MySQL5 on a Windows 2003 dedicated box and one of the last questions in the Instance Configuration Wizard had a check box labeled: "Enable Root Access From Remote Machines". I did some research but could not figure out what that meant. I log on to my box using Remote Desktop so if I don't check this will I able able to have root access? I am guessing this is a security issue. I want my box to be as safe as I can. Here is how I have the MySQL config file setup (from the Instance Configuration wizard): port=3306 basedir="C:/Program Files/MySQL/MySQL Server 5.0/" datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/" default-character-set=latin1 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=49M table_cache=256 tmp_table_size=52M thread_cache_size=8 myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=103M key_buffer_size=76M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K innodb_data_home_dir="C:/MySQL Datafiles/" #skip-innodb innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=1524K innodb_buffer_pool_size=148M innodb_log_file_size=30M innodb_thread_concurrency=8
Mysql Foreign Key
i was told that you can use the foreign key inside a table in the database to get the mysql tables/feilds communicating with one another. Could someone point me in the direction of a tutorial, or if someone could help me out it would be much appreciated, as i can't seem to find any information about getting tables to communicate with each other.
Foreign Keys In Mysql
I am working on my first database and I think I want to build a table with a foreign key in it. The O'Reilly book on MySQL says that MySQL does not support foreign keys, but it still talks about them in the section on database design. So, if if I want to use a foreign key, how do I do it?
Using A Foreign Character Set In MYSQL
I am working with MySQL 4.0. I have a requirement to create a data-driven Web page to display Chinese text from a MySQL table. I'm completely new to this, can someone tell me exactly what I need to do to make this happen? 1. For example, how do I insert the Chinese text from my source (a Word doc) into a MySQL table without corrupting it? When I try copying and pasting it into the mysql client command-line, the data gets trashed. 2. Once it's in, how do I get it back out into my application without corrupting it? I'm using PHP 4.3 for the Web site. 3. If I need to make changes to the data from the command-line client, how can I do it, especially if the query involves using a Chinese-language string? For example, "update langdata set menutitle='SOME_MENU_TITLE_IN_CHINESE' where menutitle='SOME_OLD_MENU_TITLE_IN_CHINESE'" Looked at the online manual but am sorry to say it didn't really help much. I tried starting the server with --character-set=big5 but it didn't seem to make much difference.
MySql Triggers And Foreign Keys
I would like to know if MySql supports triggers. I would also like to know if i can cascade updates and deletes on foreign keys. Please supply me with a sample code to create triggers if they exist and for cascading deletes and updates if any.
I Want To Leave A Foreign Key Null In MySQL Database
I need to leave a field null, but i also need that field have a foreign key. But even after creating this table with the field with not "not-null", if i add the foreign key, i can't leave it blank (NULL). Im using mysql 4.0.20a and all my tables as set to InnoDB.
I Want To Leave A Foreign Key Null In MySQL Database
I need to leave a field null, but i also need that field have a foreign key. But even after creating this table with the field with not "not-null", if i add the foreign key, i can't leave it blank (NULL). I'm using mysql 4.0.20a and all my tables as set to InnoDB.
MySQL Table Design Style With Foreign Keys
I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)... You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy. But let's say each rack has one default spice. So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"? In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter. And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?
Why Use A Foreign Key?
I am going to have a database that will have around 7 tables. Each table will have a UserID column that will be used as the Foreign Key (kind of). I will setup a primary key comprised of the UserID and an Auto Incremented field in each of the tables, and I plan on setting up indexing on the UserID field, all tables but 1 table can and will have duplicates of the UserID, it is how I am implemented my single to many infrastructure. I will be updating and inserting into all the tables at the same time. Some of my queries however may or may not use more than 1 table. Anyways my question is should I specifically setup a Foreign Key? As I stated above I plan on using the UserID field in each table as a Foreign Key....rather a linking identifier, but should I actualy create a Foreign Key?
Foreign Key
I add foreign key to my tables, but mysql seem to don't consider them. For exemple the table BIERE (beer in english) : CREATE TABLE BIERE ( NOM VARCHAR(20) NOT NULL, TYPEB VARCHAR(20) NOT NULL, ORIGINE VARCHAR(20), COTE VARCHAR(10) NOT NULL, PRIMARY KEY(NOM), FOREIGN KEY (TYPEB) REFERENCES TYPEBIERE (NOM) on delete cascade, FOREIGN KEY (ORIGINE) REFERENCES ORIGINE (NOM) on delete cascade, FOREIGN KEY (COTE) REFERENCES COTE (NOM) on delete cascade ); So the foreign key typeb come from the table TYPEBIERE (white...brown..etc) But if I do an insert of a beer and I type "Love" for a type of beer (Love isn't in TYPEBIERE) MySql don't show any error and add this beer to the table?
Foreign Key
I think I've finally figured out how to make a foeign key - you just copy some code into the "SQL query" box near the bottom of a table's Structure view page, right? I thought I also had the right code to paste in, but it isn't working. Here are the stats:
Foreign Key
I've got a strange foreign key constraint problem in that when I try to do an insert into a table with some foreign keys, even though those keys reference valid fields in valid tables, I get the "cannot add or update...." [1216] error message. Code:
Foreign Key
i am able to do good with single db or table with all the queries. I like to learn how to work with multiple tables/dbs, particularly i what to learn seting foreign key?
|
TRACKER
