MySQL Forums - Constraints

Why am I not able to name index differently from foreign constraint name (no replies)

Chun Hung Leng — Wed, 22 May 2013 06:39:25 +0000

Hi

my environment is as follow:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.27                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)


create table if not exists test1(
test1_id int,
test1_string varchar(10),
constraint pk_test1 primary key (test1_id) using hash comment 'primary key index for test1'
);

create table if not exists test1_child(
test1_child_id int,
test1_id int not null,
test1_child_string varchar(10),
constraint fk_test1_child_test_id foreign key fk_test1_child_test_id (test1_id) references test1 (test1_id)
);

next I decided to have different index name and foreign key constraint_name on test1_child, so I did the following:

from http://dev.mysql.com/doc/refman/5.5/en/alter-table.html


alter table test1_child drop foreign key fk_test1_child_test_id;

alter table test1_child drop index fk_test1_child_test_id;


alter table test1_child add constraint fk_test1_child_test_id foreign key fk_test1_child_test_id_index (test1_id) references test1(test1_id);

however results does not show what I expect



mysql> show index from test1_child;
+-------------+------------+------------------------+--------------+------------
-+-----------+-------------+----------+--------+------+------------+---------+--
-------------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name
 | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | I
ndex_comment |
+-------------+------------+------------------------+--------------+------------
-+-----------+-------------+----------+--------+------+------------+---------+--
-------------+
| test1_child |          1 | fk_test1_child_test_id |            1 | test1_id
 | A         |           0 |     NULL | NULL   |      | BTREE      |         |
             |
+-------------+------------+------------------------+--------------+------------
-+-----------+-------------+----------+--------+------+------------+---------+--

from the referential_constraints

mysql> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQ
UE_CONSTRAINT_NAME, REFERENCED_TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CO
NSTRAINTS where table_name='test1_child';
+-------------------+------------------------+--------------------------+-------
-----------------+-----------------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME        | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE
_CONSTRAINT_NAME | REFERENCED_TABLE_NAME |
+-------------------+------------------------+--------------------------+-------
-----------------+-----------------------+
| certify           | fk_test1_child_test_id | certify                  | PRIMAR
Y                | test1                 |
+-------------------+------------------------+--------------------------+-------
-----------------+-----------------------+
1 row in set (0.00 sec)

as you will have observe the foreign key constraint name is the same as the index_name, could anyone explain?

thanks a lot!

Why are I not able to name my primary key constraint (no replies)

Chun Hung Leng — Wed, 22 May 2013 04:45:39 +0000

Hi,

my environment is as follow:

mysql> show variables where variable_name like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.27                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

this is my create table statement

create table if not exists test1(
test1_id int,
test1_string varchar(10),
constraint pk_test1 primary key (test1_id) using hash comment 'primary key index for test1'
);

this is what i retrieved from show create table


mysql> show create table test1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------+
| Table | Create Table

                                                                          |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `test1_id` int(11) NOT NULL DEFAULT '0',
  `test1_string` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`test1_id`) USING HASH COMMENT 'primary key index for test1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

as you realize the name of the primary key constraint is not effective.

mysql> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, CONSTRAINT_TYPE
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='test1';
+-------------------+-----------------+--------------+-----------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | CONSTRAINT_TYPE |
+-------------------+-----------------+--------------+-----------------+
| certify           | PRIMARY         | certify      | PRIMARY KEY     |
+-------------------+-----------------+--------------+-----------------+
1 row in set (0.02 sec)

why is this so? from http://dev.mysql.com/doc/refman/5.5/en/create-table.html, I doubt that there's anything wrong with my syntax.


mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+----------------------
-------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
       |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+----------------------
-------+
| test1 |          0 | PRIMARY  |            1 | test1_id    | A         |
     0 |     NULL | NULL   |      | BTREE      |         | primary key index for
 test1 |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+----------------------
-------+
1 row in set (0.00 sec)



if you take a close look, you will realize that index type is BTREE and not HASH as desired.

ocl constraint {Xor} check (2 replies)

feres feres — Tue, 02 Apr 2013 15:26:42 +0000

hello everyone :)

during the preparation of my relational model, I'm stuck at the translation of a type constraint {XOR}.

here's an example: I have a file that is equivalent credit by an applicant (a user) and it is also validated by a validator (another user) but there is an exclusion constraint the applicant may not be the validator to the same folder.

how I can translate it at my table

Dossier_Equi (num_dosier, # applicant # validator ......)
Does anyone have the proposal???

Thank you in advance

Error: 1557 SQLSTATE: 23000 (ER_FOREIGN_DUPLICATE_KEY) How to fix this issue? (1 reply)

Sebastian Schwaiger — Thu, 14 Mar 2013 23:16:43 +0000

Dear MySQL professionals,

my db gives me the mentioned error message when I try to update a table.
I have no idea how to find out the table row that is actually leading to the "duplicate key" problem.

Can you give me a hint how to start?

Maybe the problem is caused when I fill the database with SET_FOREIGN_KEY_CHECKS=0 entering invalid data?

Cheers,

Sebastian

Multiply column values (1 reply)

Anish Philip — Mon, 10 Dec 2012 07:34:45 +0000

Hi guys

I am trying to create a table like below

mysql> CREATE TABLE orders
-> ( Salesman_code int NOT NULL,
-> Salesman_name char(20) NOT NULL,
-> Order_date date NOT NULL,
-> City char(20),
-> Rate int,
-> Quantity int,
-> Amount AS Rate * Quantity);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Rate * Quantity)' at line 8
mysql>

I want to assign the multiplied value of Rate & Quantity to Amount. I have seen a similar usage in another forum but in my case, its giving an error! I searched for "AS/AS command" at http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html but no hits! Am I looking at the right location for general sql syntax? Is it possible to get a detailed error output? The material that I am using to practice SQL queries contains lot of example programs but when I execute them, most of them end up with errors like above. Can someone recommend me a material to practice SQL queries on MySQL RDBMS? Any help would be much appreciated!

Regards,
Anish

Check constraint not working (6 replies)

Anish Philip — Thu, 08 Nov 2012 19:03:28 +0000

Hi guys

This is my first post in this forum. I am not sure if this is right place to post this question. I have a question regarding my check constraint. I created a table "item" with a CHECK constraint on columns ROL and QOH to check if ROL
mysql> CREATE TABLE items (
-> icode char(5) NOT NULL,
-> descp char(20) NOT NULL,
-> ROL integer,
-> QOH integer,
-> PRIMARY KEY (icode),
-> CHECK (ROL Query OK, 0 rows affected (0.10 sec)

mysql> DESCRIBE items;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| icode | char(5) | NO | PRI | NULL | |
| descp | char(20) | NO | | NULL | |
| ROL | int(11) | YES | | NULL | |
| QOH | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO items (icode, descp, ROL, QOH) VALUES ('ABC', 'New employee', 234, 500);
Query OK, 1 row affected (0.36 sec)

mysql> SELECT * FROM items;
+-------+--------------+------+------+
| icode | descp | ROL | QOH |
+-------+--------------+------+------+
| ABC | New employee | 234 | 500 |
+-------+--------------+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO items (icode, descp, ROL, QOH) VALUES ('DEF', 'New employee', 224, 100);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM items;
+-------+--------------+------+------+
| icode | descp | ROL | QOH |
+-------+--------------+------+------+
| ABC | New employee | 234 | 500 |
| DEF | New employee | 224 | 100 |
+-------+--------------+------+------+
2 rows in set (0.00 sec)

mysql>

Cardinality vs. distinct values (1 reply)

Nathan Aschoff — Wed, 31 Oct 2012 09:07:01 +0000

Dear all,

Can somebody explain to me the difference between cardinality and distinct(unique) values.

The reason I ask this is after I run an 'analyze table orders;' the 'show indexes from orders;' gives me a cardinality of 18 on my index created on the column order_id. But, when I run a 'select count(distinct order_id) from orders;' I receive a count of 47 distinct values.

I find this odd as I thought cardinality was indeed the count of unique values of a column.

mysterious DDL failure for PK (1 reply)

Foam Pile — Fri, 31 Aug 2012 12:04:26 +0000

i cannot figure out why i am unable to create the PK:

CREATE TABLE ts_hAccountingUnit
(
accUnitID char(6) not null,
subrgnID char(4) not null,
accUnitDesc text
);

ALTER TABLE ts_hAccountingUnit
ADD CONSTRAINT pk_ts_hAccountingUnit
PRIMARY KEY accUnitID;

***I am able to create a FK***

ALTER TABLE ts_hAccountingUnit
ADD CONSTRAINT fk1_ts_hAccountingUnit
FOREIGN KEY (subrgnID) REFERENCES ts_hSubregion(subrgnID);

meanwhile, another PK creation has no problem:

CREATE TABLE ts_hSubregion
(
subrgnID char(4) not null,
rgnID char(2) not null,
subrgnDesc text
);
ALTER TABLE ts_hSubregion
ADD CONSTRAINT pk_ts_hSubregion
PRIMARY KEY subrgnID;

although it follows identical syntax

Index Type created not same as mentioned in documentation. (1 reply)

Sachin Vyas — Tue, 09 Oct 2012 13:24:30 +0000

Hello,

We have installed the following cluster version on linux:

SELECT VERSION();
5.5.19-ndb-7.2.4-gpl

w.r.t the following link:
http://dev.mysql.com/doc/refman/5.5/en/create-index.html
for the section "index_type" :

I tried out the stuff mentioned for the description of "index_type" & found the following differences:
1) As per the documentation:

...Where multiple index types are listed, the first one is the default when no index type specifier is given.

Storage Engine Permissible Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE (see note in text)

So as per above I created a table & created an index on the table & expected the index type to be "HASH", but it is actually a "BTREE" index.

CREATE TABLE lookup
(id INT)
engine=ndb;

alter table lookup add index idx_simple (id);
[I even tried the CREATE INDEX, without specifying the "USING BTREE" option]

I refered the index type from TOAD UI, which showed it as "BTREE". I also refered the information_schema.statistics table
for the index name = "idx_simple", which showed as "BTREE" under the column "INDEX_TYPE".

2) As per the documentation:
Note
For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key.

But, I was able to create a normal index as mentioned below by using the "USING" clause.
CREATE INDEX id_index ON lookup (id) USING BTREE;

3) As per the documentation:
USING HASH prevents the creation of an implicit ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns.

How do I get information on the above two indexes created (in "mysql" or "information_schema" db).

Regards,
Sachin Vyas.

[solved] ERROR 1170: key length for index on textfield is specified but still this error (1 reply)

Sebastiaan Nieuwenhuis — Wed, 30 May 2012 07:50:13 +0000

Hello,

Ik have the following query:

CREATE TABLE `test_index_on_type_textfield` (
`id` smallint(5) unsigned NOT NULL,
`name` varchar(50) NOT NULL,
`content` tinytext,
PRIMARY KEY (`id`),
INDEX `filter_on_name` (`name`),
INDEX `filter_on_content` (`content`(1000))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The query is executed succesfully, the table is created. When I view the stored indexes, the index on column 'content' has the key length 1000.

But when I try to alter any column, I get the following error: "#1170 BLOB/TEXT column 'content' used in key specification without a key length".

What is wrong?

duplicate index which has name of foreign key constraint (no replies)

P P — Thu, 24 May 2012 00:00:05 +0000

I downloaded the Percona Toolkit which contains a program called pt-duplicate-key-checker. This analyzes all indexes in a database and spits out duplicates with a suggestion on how to remove the duplicate index.

One thing Im noticing over and over again are duplicate Foreign Key indexes when an existing index already existed, usually the primary key. For example:

CREATE TABLE ` ` (
`ID` bigint(20) NOT NULL,
`ASSIGNMENT_ID` varchar(255) DEFAULT NULL,
`TITLE` varchar(255) DEFAULT NULL,
`TEXT` text,
`RELEASE_DATE` datetime DEFAULT NULL,
`RETRACT_DATE` datetime DEFAULT NULL,
`HIDE` bit(1) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FK514CEE15935EEE07` (`ID`),
CONSTRAINT `FK514CEE15935EEE07` FOREIGN KEY (`ID`) REFERENCES `ASN_SUP_ITEM_T` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show indexes from ASN_AP_ITEM_T;
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ASN_AP_ITEM_T | 0 | PRIMARY | 1 | ID | A | 2 | NULL | NULL | | BTREE | |
| ASN_AP_ITEM_T | 1 | FK514CEE15935EEE07 | 1 | ID | A | 2 | NULL | NULL | | BTREE | |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Couldn't we not have the FK514CEE15935EEE07 KEY since ID is already indexed? If so what about this case?

CREATE TABLE `CM_OFFICIAL_INSTRUCTORS_T` (
`ENROLLMENT_SET_ID` bigint(20) NOT NULL,
`INSTRUCTOR_ID` varchar(255) DEFAULT NULL,
UNIQUE KEY `ENROLLMENT_SET_ID` (`ENROLLMENT_SET_ID`,`INSTRUCTOR_ID`),
KEY `FK470F8ACCC28CC1AD` (`ENROLLMENT_SET_ID`),
CONSTRAINT `FK470F8ACCC28CC1AD` FOREIGN KEY (`ENROLLMENT_SET_ID`) REFERENCES `CM_ENROLLMENT_SET_T` (`ENROLLMENT_SET_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In this case it's not primary key, but there is a unique key ENROLLMENT_SET_ID indexing ENROLLMENT_SET_ID and INSTRUCTOR_ID. Since ENROLLMENT_SET_ID is already being indexed isn%u2019t the key FK470F8ACCC28CC1AD a duplicate?

Is this just the case of MySQL being as default and adding an index when its not needed? Can we remove these which might increase update and insert speeds.

Alter PRIMARY KEY (1 reply)

Auke Middendorp — Thu, 10 May 2012 07:28:28 +0000

Adding a primary key can be done like this:
ALTER TABLE Persons ADD PRIMARY KEY (id)

and removing it like this:
ALTER TABLE Persons DROP PRIMARY KEY

But is it possible to change a PK in a single query? For example like this:
ALTER TABLE Persons DROP PRIMARY KEY, ADD PRIMARY KEY (name)

Or does ADD PRIMARY KEY override any existing one?

On Functional Dependencies. (no replies)

Juan Casanova — Wed, 25 Jan 2012 16:26:18 +0000

Hello, my first post in this forum (and probably one of the last too).

First, let me briefly introduce myself; I am a 20 year old Computer Science and Mathematics student and passionate who has been working at his first job in the sector for the past 6 months. The doubt and question I am going to ask has been inside my head since the moment I studied the formal relational database paradigm and at the same time the implementation of SQL for it.

I know this is not a forum for this kind of questions. This is a forum about MySQL, not about the SQL standard (and as far as I have been able to read, functional dependencies are not even a part of the SQL standard); and this subforum is about constraints within MySQL, not about functional dependencies, but it is what is most similar to functional dependencies, I guess.

Now onto the topic itself. I have always wondered, as I said, why in hell does SQL not offer the possibility to define functional dependencies and use them for all the different things they could be used?

As far as I see it, you could analyse it like this.

Advantages of having functional dependencies:

* Conceptual.
- Would allow for more clearly defined meanings and constraints of databases.

* Restrictional.
- Same as foreign keys allow you to make sure that the database data is consistent within the meaning of your universe, functional dependencies would further allow for this. It would be harder to "fuck up" with the data.

* Operational.
- Auto-normalization of the database would be doable, as there are well thought algorithms for doing this knowing the functional dependencies. In the cases where the amount of data is very high and normalization makes database treatment too slow, you could simply skip auto-normalization; or possibly add a more complex algorithm with parameters setting up "how much normalization" you wish to put on your database. Overall, having the possibility of auto-normalizing would never be bad and would for sure be good in certain cases (it would not be unused, for small databases at least it would be highly recommendable).

* Search and index efficiency.
- This is probably by long the most important advantage. Imagine a database table where you have a column named "Type" and a column named "Subtype". It is not far-fetched to think (and imagine in this particular case it is like that) that you have the functional dependency Subtype -> Type. That is, subtypes are actually sub - types of the types, you can't have the same subtype for two different types. Without functional dependencies, the database has no information about the relationship between these 2 columns. Now, you could make 2 indexes, one for Type and one for Subtype; or you could make a compound index of (Type, Subtype). Now imagine the functional dependencies are more complex, and you have yet another third column Class, with the functional dependency Subtype -> Class; and no relationship between Type and Class.

The result is that you can make 3 simple indexes, 2 compound-indexes or just leave one of the columns un-indexed.

Now, if you had functional dependencies, you could exclusively define the index on Subtype and still use it partially for searchs or joins on the Type or Class columns. Imagine you had Subtype indexed and you wanted to join the table with antoher table through the Type column. You could create a temporary table with the Subtype and Type of each register of your table ordered by subtype (this would be simply extending the index by adding the Type column, order 1.000.000); then proceed to do the linear search through Type on that table, and once you found a row with a matching Type, you know that all the rows with the same Subtype have therefore the same Type and can bulk-join them as your table is ordered by Subtype. Then simply continue to linearly look for more matching Types.

In the opposite case. B -> A, C -> A. You could have the index only in A; and the efficiency gain wouldn't be that big, but you could still, to search by B, do a linear search on B until you found an equivalence. Therefore, you know that all other ocurrences of that value b will have a specific value a on the A column (the one that matched). Therefore, for that row of the <> column you can restrict the search to the subset which has a on the A column, which you can separate because A is indexed, and later on remove the row from the <> table and continue the linear join. The gain here is significantly lower and probably deprecatable, but in the first case it isn't.

Disadvantages of functional dependencies:

- They are very easy to define and as defined over columns and no data needed to be "maintained" when adding/removing rows (it is a column constraint, as the way its defined); it wouldn't take much space to keep so it isn't a disadvantage on that sense.

- They can make operations slower when adding new rows, but since typically you will have an index on the left attribute of the dependency (A in A->B), it will not be that slow. You could even force this index the same way as an index is forced on a foreign key. In any case, this can be controlled by simply not overkilling functional dependencies and LETTING THE DATABASE DESIGNER THINK IF IT IS WORTH USING FUNCTIONAL DEPENDENCIES.

- You have to program the algorithms??? I don't think lazyness is a disadvantage if there are actual advantages to it.

I know that all of these things are well-known (for those that know them well) and etc... but I wanted to explain them to avoid discussing with other people afterwards about what I have already thought. Overall, I just don't understand the reasoning for simply not adding functional dependencies to SQL.

Thanks in advance for reading and for your comments.

Error 1062 duplicate entry (no replies)

zahid quadri — Thu, 29 Sep 2011 11:55:54 +0000

i am getting following error in mysql slave in replication machine.

Last_Errno: 1062
Last_Error: Error 'Duplicate entry '39f88d2b:132af843b99:-68c5' for key 1' on query.

can any one please provide me solution.

thanks in advance

A Database Error Occurred (no replies)

azamazim md noh — Thu, 15 Sep 2011 08:49:35 +0000

I'm getting error message as below when to view comments or read more at my blog area. Anyone can assist?

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY RAND() LIMIT 0, 5' at line 7

SELECT * FROM jem_content_articles WHERE (jem_content_articles.content_tags LIKE '%Blog%'AND content_type = '1' AND date_published <=1316076275 AND (category_id = '1' OR category_id > '6') AND jem_content_articles.status = '1' ORDER BY RAND() LIMIT 0, 5

MySQL Detail:
Server: Localhost via UNIX socket
Server version: 5.0.92-community
Protocol version: 10
User: myroshaz@localhost
MySQL charset: UTF-8 Unicode (utf8)
PHPmyAdmin Version information: 3.4.3.2

check constraint for fix value in a column (4 replies)

zahid quadri — Fri, 02 Sep 2011 12:43:19 +0000

hello,
can any one help me .
i need a constraint that should allow only 10 digit to insert into table not 11 and not 9 also. only fix 10 digit.
if any one know please reply me on urgent basis.

regards
zahid

Where clause for calculated column (no replies)

Howard Cary Morris — Tue, 16 Aug 2011 04:32:51 +0000

I have a Select statement that includes in the column list:
datediff(now(), date_added) as Waiting
I wanted to use "Waiting" in the where clause, but got a syntax error.
I ended up using the phrase "datediff(now(), date_added)" in the select statement and it took an unnecessarily long to read all 7 rows in the database.

I suppose I can work around this restraint by creating a view with "Waiting" defined inside it. Would this work, or is there some undocumented restraint about using that column in the where clause of the view?

Multiple column foreign key (no replies)

Shashwat Tripathi — Mon, 18 Jul 2011 10:54:21 +0000

I have two table named 'customers' and 'accounts' with following create definitions:

delimiter $$

CREATE TABLE `customers` (
`CID` int(10) unsigned NOT NULL,
`First_Name` varchar(30) NOT NULL,
`Last_Name` varchar(30) default NULL,
`Father_Name` varchar(45) default NULL,
`Address_Res` varchar(100) default NULL,
`Phone` varchar(60) default NULL,
`Mobile` varchar(60) default NULL,
`Comment` varchar(200) default NULL,
`Register_Date` datetime NOT NULL,
PRIMARY KEY (`CID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to store Customer informatios'$$

delimiter $$

CREATE TABLE `accounts` (
`CID` int(10) unsigned NOT NULL,
`ACN` int(10) unsigned NOT NULL,
`Loan_Amount` int(10) unsigned NOT NULL,
`Interest_Rate` decimal(10,0) unsigned NOT NULL,
`Opening_Date` date NOT NULL,
`Closing_Date` date NOT NULL,
`Comment` varchar(200) default NULL,
`DB_Save_Date` datetime NOT NULL,
KEY `INDEX_Accounts_ACN` (`ACN`),
KEY `FK_Accounts` (`CID`),
CONSTRAINT `FK_Accounts` FOREIGN KEY (`CID`) REFERENCES `customers` (`CID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

========================
Now I want to create another table 'Mortgages' with following definition

CREATE TABLE `bankpage_`.`Mortgages` (

`CID` INT UNSIGNED NOT NULL ,

`ACN` INT UNSIGNED NOT NULL ,

`Type` VARCHAR(45) NOT NULL ,

`Weight` DECIMAL UNSIGNED NOT NULL ,

`Mortgage_Date` DATE NOT NULL ,

`Release_Date` DATE NOT NULL ,

`IsSeized` TINYINT(1) NOT NULL ,

`Comments` VARCHAR(45) NULL ,

INDEX `FK_Mortgages` (`CID` ASC, `ACN` ASC) ,

CONSTRAINT `FK_Mortgages`

FOREIGN KEY (`CID` , `ACN` )

REFERENCES `bankpage_`.`accounts` (`CID` , `ACN` )

ON DELETE NO ACTION

ON UPDATE NO ACTION);

=================
On execution I am getting the following error:
ERROR 1005: Can't create table '.\bankpage_\mortgages.frm' (errno: 150)

================
Please help me
I want to reference two columns of mortages table to accounts table

Selecting records that don't match a criteria (1 reply)

skasab2s skasab2s — Mon, 18 Jul 2011 11:20:39 +0000

Hello,

already 3 hours I try to constrict a query to my problem. Given the database table with those 5 records:

PatientNumber | DrugType
-------------------------------------
1 ___________ | A
1 ___________ | A
2 ___________ | A
2 ___________ | B
2 ___________ | A

Which represents the types of the drugs that have been given to a patient.

How to consuct a MySQL query which will get me a record for a patient that have NEVER get drug from type B? In this particular case it should return the first and the second record (because patient 1 never got drug from type B).

Thanks and best regards,

Svetlomir Kasabov.

Inconsistent DB - Foreign key constraint fails (3 replies)

Jan Wedel — Tue, 31 May 2011 10:03:44 +0000

Hi all!

As the subject indicates, I am getting the famous "ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails" message.

But it's a bit strange since the schema didn't change for months and the system was running without problems. But two things happened that could have triggered the problem: 1) Our hosting provider restarted the server and 2.) I manually triggered the system processing data and killed the process once (application, not the db).

Now I'm getting this problem. I read some other posts here, but it didn't help. - - The tables are all lower case letters
- I'm using a terminal on a linux machine, no windows involved.
- The key that it is referencing to is definitely existing.
- I tried to left join the two tables to find out if there are any inconsistencies but there aren't any.
- I created these two tables (see below) from scratch in a different db, entered the values in the one table, performed the very same insert statement and DID NOT get the error!

Is it possible, that the database has entered any inconsitent state? If yes, is there any statement to check this?

Thanks a lot!

Here is some information:
----------------------------------------------------------------------

mysql  Ver 14.12 Distrib 5.0.67, for suse-linux-gnu (i686) using readline 5.2

Connection id:          3231
Current database:       sbahn
Current user:           sbahn@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.67 SUSE MySQL RPM
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 2 days 16 hours 24 min 30 sec

Threads: 1  Questions: 78  Slow queries: 0  Opens: 20  Flush tables: 1  Open tables: 14  Queries per second avg: 0.000
--------------

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.67    |
+-----------+



CREATE TABLE `t_betriebsstellen` (
  `kurzname` varchar(15) NOT NULL,
  PRIMARY KEY  (`kurzname`),
  UNIQUE KEY `UQ_t_betriebsstellen_kurzname` (`kurzname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 


 t_zug_report | CREATE TABLE `t_zug_report` (
  `ist_datum` date NOT NULL,
  `zug_nr` int(11) NOT NULL,
  `ist_zeit` time NOT NULL,
  `soll_datum` date default NULL,
  `soll_zeit` time default NULL,
  `fehler_code` smallint(6) NOT NULL,
  `betriebsstelle` varchar(15) NOT NULL,
  `soll_gleis` int(11) NOT NULL,
  `ist_gleis` int(11) NOT NULL,
  `soll_ip` varchar(15) NOT NULL,
  `ist_ip` varchar(15) default NULL,
  `soll_wlan_nr` tinyint(4) NOT NULL,
  `ist_wlan_nr` tinyint(4) default NULL,
  `feldstaerke` float default NULL,
  PRIMARY KEY  (`ist_datum`,`zug_nr`,`ist_zeit`),
  KEY `IDX_betriebsstelle` (`betriebsstelle`),
  KEY `IDX_ist_datum` (`ist_datum`),
  KEY `IDX_zug_nr` (`zug_nr`),
  CONSTRAINT `FK_t_zug_report_t_betriebsstellen` FOREIGN KEY (`betriebsstelle`) REFERENCES `t_betriebsstellen` (`kurzname`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

mysql> select * from t_betriebsstellen;
+----------+
| kurzname |
+----------+
| BGB      |
| BLST     |
| BPLA     |
| BSGR     |
| BSO      |
| BSTO     |
| BTHF     |
+----------+
7 rows in set (0.00 sec)

When I do the following statement, I get the error:

insert into t_zug_report values ('2011-05-16',41020,'04:34:47',null,null,1,'BGB',7230,7230,'0.0.0.0',null,1,null,null);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sbahn/t_zug_report`, CONSTRAINT `FK_t_zug_report_t_betriebsstellen` FOREIGN KEY (`betriebsstelle`) REFERENCES `t_betriebsstellen` (`kurzname`) ON UPDATE CASCADE)

Date and Symbol constraint (1 reply)

jj ww — Tue, 05 Apr 2011 20:12:06 +0000

I import data regularly form the following function:
load data local infile 'C:\\Documents and Settings\\J\\Desktop\\import.csv' into table data fields terminated by ',' enclosed by '''' lines terminated by '\n';

The table structure is:
CREATE TABLE `data` (
`Date` datetime DEFAULT NULL,
`Symbol` varchar(6) DEFAULT NULL,
`ClosingPrice` decimal(8,2) DEFAULT NULL,
KEY `Symbol` (`Symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

I cannot edit all of the raw data so what I want to so is prevent duplicates entering the database. A duplicate is a row that has the same same symbol and same date. So, 1 entry for AAA '2011-04-01' is allowed but not
AAA '2011-04-01' & AAA '2011-04-01'

What is the best way to put a constraint on this while importing?
Furthermore, I would like to allow the data be updated if the ClosingPrice has changed.

I thought a trigger might work:
DELIMITER $$
create trigger duplicate_check before insert on data for each row
begin
if new.Date = (SELECT Date FROM data WHERE Date=new.Date AND Symbol=new.Symbol) then
call fail(’Duplicate data’);
#is there a way just to abort that 1 row insert here?
#where is the else function to allow the insert?
end if;
end $$
DELIMITER ;

Where is the else function to allow the insert?
Secondly, as the imported data doesn't have column labels how does the insert know which column is which as at the moment it just imports them in column order I think.

Some sort of constraint needed (2 replies)

Eddie Lau — Fri, 18 Feb 2011 17:47:32 +0000

Hi All,

I have 3 tables set up similar to as follows:

CREATE TABLE AllProducts (ID INT, ProductType TINYINT(1), ProductID INT);

CREATE TABLE ProductA (ID INT, Product_Description VARCHAR(100));
CREATE TABLE ProductB (ID INT, Product_Description VARCHAR(100));

INSERT INTO TABLE ProductA VALUES (1,"This product is a ...");
INSERT INTO TABLE ProductB VALUES (1,"This product is a ...");
INSERT INTO TABLE AllProducts VALUES (1,1,1), (2,2,1);

Where in the AllProducts table, ProductType=1 refers to the ProductA table and ProductType=2 refers to ProductB table.

I would like to have some sort of foreign key constraint in AllProducts table, so that when a new row is inserted into it with ProductType=1, it would look up to see if that ProductID exists in the ProductA's ID column, and of course, a row cannot be deleted in ProductA if a reference exists in AllProducts.

Does anyone know how this could be done apart from writing triggers?

Cheers.

Unsigned, AutoIncrement (2 replies)

Winfried Palm — Mon, 21 Feb 2011 13:18:23 +0000

Hi,

I look for a posibility to read the value Unsigned and AutoIncrement form a field in the information_schema. In the manual I don't find any information.

I found information for key, not null and unique, but not the other constraints.

Can anybody help.

FOREIGN KEY ON UPDATE CASCADE does NOT work with SELF REFERENCE (1 reply)

Luca Esculapio — Wed, 08 Dec 2010 07:37:43 +0000

Hello to everyone,

I found a very similar issue here:
http://forums.mysql.com/read.php?136,300573,300573#msg-300573
but it appears to be closed without any reply, so before posting it as a bug I would inquire here whether anyone has any known solution to it or it is an actual bug.

I think it is an important issue in general.

Here we go:

PROBLEM: FOREIGN KEY ON UPDATE CASCADE does NOT work with SELF REFERENCE

PURPOSE: Ensuring that when updating the parent value, the related values in childs get updated accordingly.

SUMMARY: Once such foreing key relationship is set up within the same table, attempts to update the key field of a parent having childs, not only do not work, but generate an error as well.

REFERENCE:
MySQL Manual, in section:
"14.2.6.4. FOREIGN KEY Constraints"
it reads:
"Note that InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table."

HOW TO REPRODUCE IT:

- Done with:

Windows 7
XAMPP for Windows Version 1.7.3
MySQL: 5.1.41
PhpMyAdmin 3.2.4

- Create the table together with the foreign key reference pointing to itself:

CREATE TABLE `table`
(
id INT NOT NULL,
parent_id INT,
PRIMARY KEY (id),
FOREIGN KEY (parent_id)
REFERENCES `table`(id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE=INNODB

- Insert parent record:

INSERT INTO `table` (
`id` ,
`parent_id`
)
VALUES (
'0', NULL
);

- Insert child record:

INSERT INTO `test`.`table` (
`id` ,
`parent_id`
)
VALUES (
'1', '0'
);

- Attempt to update parent's related field:

UPDATE `table` SET `id` = 2 WHERE `table`.`id` = 0

- I'd expect both parent's and child's fields updated; instead, the above returns the following error (test done with PhpMyAdmin, within database named `test`):

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`table`, CONSTRAINT `table_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Why?
Perhaps did MySQL refuse to update the parent row because it considers that parent being a child to another parent (which does non exist, by the way)?
So I also tested it with such parent record with id = NULL in existence (and to do so I created the table as not having any primary key but a simple index unique replacing it). Does not work either: same update attempt, same error message as above.

Any clue? Is there a way out?
I do have to solve it, and I think many others may as well, so thanks a lot in advance for any help.

foreign-key / import (no replies)

Junior DBA — Tue, 26 Oct 2010 17:04:54 +0000

I am exporting data from MySQL 5.1.22-rc via the phpMyAdmin 2.11.7.1 interface and importing the data into MySQL 5.2.29CE. I was able to import all of the data into MySQL 5.2.29CE successfully, but I had to comment out foreign-key statements to do so.

Now I am trying to execute/apply the foreign-key statements on the tables in MySQL 5.2.29CE. I was able to successfully execute/apply the GROUP2 updates, but not GROUP1. The statements in GROUP1 generate the following error: Error Code: 1005Can't create table 'scs.#sql-68c_f(errno: 121).

Do you see something in the code (which was generated by phpMyAdmin to export the data) that would cause that error?

select @@foreign_key_checks;
SET foreign_key_checks = 0;

-- GROUP1: NOT executed successfully; 10.26.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET foreign_key_checks = 0;
USE scs;

ALTER TABLE `TBL_ShowNote`
ADD CONSTRAINT FK_TBL_ShowNote_TBL_Show FOREIGN KEY (Show_ID) REFERENCES TBL_Show (Show_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `TBL_ShowNote`
ADD CONSTRAINT FK_TBL_ShowNote_TBL_ShowNoteType FOREIGN KEY (ShowNoteType_ID) REFERENCES TBL_ShowNoteType (ShowNoteType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- GROUP2: executed successfully; 10.26.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET foreign_key_checks = 0;
USE scs;

ALTER TABLE `RETS_Log`
ADD CONSTRAINT RETS_Log_ibfk_1 FOREIGN KEY (listnum) REFERENCES PROPMAIN (listnum);

--
-- Constraints for table `TBL_Announce`
--
ALTER TABLE `TBL_Announce`
ADD CONSTRAINT FK_TBL_Announce_TBL_AnnounceType FOREIGN KEY (AnnounceType_ID) REFERENCES TBL_AnnounceType (AnnounceType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Association`
--
ALTER TABLE `TBL_Association`
ADD CONSTRAINT FK_TBL_UserAssociation_TBL_Company FOREIGN KEY (Company_ID) REFERENCES TBL_Company (Company_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_CallTagxRef`
--
ALTER TABLE `TBL_CallTagxRef`
ADD CONSTRAINT FK_TBL_CallTagxRef_TBL_CallTag FOREIGN KEY (CallTag_ID) REFERENCES TBL_CallTag (CallTag_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_CallTagxRef_TBL_Message FOREIGN KEY (Message_ID) REFERENCES TBL_Message (Message_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Company`
--
ALTER TABLE `TBL_Company`
ADD CONSTRAINT FK_TBL_Company_TBL_CompanyType FOREIGN KEY (CompanyType_ID) REFERENCES TBL_CompanyType (CompanyType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Company_TBL_State FOREIGN KEY (State_ID) REFERENCES TBL_State (State_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_CompanyServices`
--
ALTER TABLE `TBL_CompanyServices`
ADD CONSTRAINT FK_TBL_CompanyServices_TBL_Company FOREIGN KEY (Company_ID) REFERENCES TBL_Company (Company_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_CompanyServices_TBL_County FOREIGN KEY (County_ID) REFERENCES TBL_County (County_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_CompanyServices_TBL_Services FOREIGN KEY (Services_ID) REFERENCES TBL_Services (Services_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_ContactType`
--
ALTER TABLE `TBL_ContactType`
ADD CONSTRAINT FK_TBL_ContactType_TBL_ContactDevice FOREIGN KEY (ContactDevice_ID) REFERENCES TBL_ContactDevice (ContactDevice_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Coordinator`
--
ALTER TABLE `TBL_Coordinator`
ADD CONSTRAINT FK_TBL_Coordinator_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_County`
--
ALTER TABLE `TBL_County`
ADD CONSTRAINT FK_TBL_County_TBL_State FOREIGN KEY (State_ID) REFERENCES TBL_State (State_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Document`
--
ALTER TABLE `TBL_Document`
ADD CONSTRAINT FK_TBL_Document_TBL_Company FOREIGN KEY (Company_ID) REFERENCES TBL_Company (Company_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Document_TBL_DocumentFormat FOREIGN KEY (DocumentFormat_ID) REFERENCES TBL_DocumentFormat (DocumentFormat_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Document_TBL_DocumentType FOREIGN KEY (DocumentType_ID) REFERENCES TBL_DocumentType (DocumentType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Features`
--
ALTER TABLE `TBL_Features`
ADD CONSTRAINT FK_TBL_Features_TBL_PropMain FOREIGN KEY (propid) REFERENCES TBL_PropMain (propid) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Field`
--
ALTER TABLE `TBL_Field`
ADD CONSTRAINT FK_TBL_Field_TBL_Group FOREIGN KEY (groupid) REFERENCES TBL_Group (groupid) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Inbound`
--
ALTER TABLE `TBL_Inbound`
ADD CONSTRAINT FK_TBL_Inbound_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Location`
--
ALTER TABLE `TBL_Location`
ADD CONSTRAINT FK_TBL_Location_TBL_Company FOREIGN KEY (Company_ID) REFERENCES TBL_Company (Company_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Message`
--
ALTER TABLE `TBL_Message`
ADD CONSTRAINT FK_TBL_Message_TBL_Advertise FOREIGN KEY (Advertise_ID) REFERENCES TBL_Advertise (Advertise_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Message_TBL_MessageType FOREIGN KEY (MessageType_ID) REFERENCES TBL_MessageType (MessageType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Message_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Notify`
--
ALTER TABLE `TBL_Notify`
ADD CONSTRAINT FK_TBL_Notify_TBL_NotifyType FOREIGN KEY (NotifyType_ID) REFERENCES TBL_NotifyType (NotifyType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Notify_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Permission`
--
ALTER TABLE `TBL_Permission`
ADD CONSTRAINT FK_TBL_Permission_TBL_PermissionType FOREIGN KEY (PermissionType_ID) REFERENCES TBL_PermissionType (PermissionType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PermissionCompanyType`
--
ALTER TABLE `TBL_PermissionCompanyType`
ADD CONSTRAINT FK_TBL_PermissionCompanyType_TBL_Permission FOREIGN KEY (Permission_ID) REFERENCES TBL_Permission (Permission_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PollDesign`
--
ALTER TABLE `TBL_PollDesign`
ADD CONSTRAINT FK_TBL_PollDesign_TBL_PollName FOREIGN KEY (PollName_ID) REFERENCES TBL_PollName (PollName_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_PollDesign_TBL_PollQuestionType FOREIGN KEY (PollQuestionType_ID) REFERENCES TBL_PollQuestionType (PollQuestionType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PollName`
--
ALTER TABLE `TBL_PollName`
ADD CONSTRAINT FK_TBL_PollName_TBL_Company FOREIGN KEY (Company_ID) REFERENCES TBL_Company (Company_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PollRequest`
--
ALTER TABLE `TBL_PollRequest`
ADD CONSTRAINT FK_TBL_PollRequest_TBL_PollName FOREIGN KEY (PollName_ID) REFERENCES TBL_PollName (PollName_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_PollRequest_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PollValue`
--
ALTER TABLE `TBL_PollValue`
ADD CONSTRAINT FK_TBL_PollValue_TBL_PollRequest FOREIGN KEY (PollRequest_ID) REFERENCES TBL_PollRequest (PollRequest_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Promotion`
--
ALTER TABLE `TBL_Promotion`
ADD CONSTRAINT FK_TBL_Promotion_TBL_Company FOREIGN KEY (Company_ID) REFERENCES TBL_Company (Company_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Prop1`
--
ALTER TABLE `TBL_Prop1`
ADD CONSTRAINT FK_TBL_Prop1_TBL_PropMain FOREIGN KEY (propid) REFERENCES TBL_PropMain (propid) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PropMain`
--
ALTER TABLE `TBL_PropMain`
ADD CONSTRAINT FK_TBL_PropMain_TBL_Status FOREIGN KEY (`status`) REFERENCES TBL_Status (Status_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PropPromotion`
--
ALTER TABLE `TBL_PropPromotion`
ADD CONSTRAINT FK_TBL_PromotionProp_TBL_Promotion FOREIGN KEY (Promotion_ID) REFERENCES TBL_Promotion (Promotion_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PropRoom`
--
ALTER TABLE `TBL_PropRoom`
ADD CONSTRAINT FK_TBL_PropRoom_TBL_PropMain FOREIGN KEY (propid) REFERENCES TBL_PropMain (propid) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_PropRoom_TBL_Room FOREIGN KEY (room_id) REFERENCES TBL_Room (RoomID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_PropShow`
--
ALTER TABLE `TBL_PropShow`
ADD CONSTRAINT FK_TBL_PropShow_TBL_ShowReply FOREIGN KEY (ShowReply_ID) REFERENCES TBL_ShowReply (ShowReply_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_PropShow_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_Show`
--
ALTER TABLE `TBL_Show`
ADD CONSTRAINT FK_TBL_Show_TBL_AppointmentType FOREIGN KEY (AppointmentType_ID) REFERENCES TBL_AppointmentType (AppointmentType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Show_TBL_ShowReply FOREIGN KEY (ShowReply_ID) REFERENCES TBL_ShowReply (ShowReply_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_Show_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT TBL_Show_ibfk_1 FOREIGN KEY (ShowStatus_ID) REFERENCES TBL_ShowStatus (ShowStatus_ID);

--
-- Constraints for table `TBL_State`
--
ALTER TABLE `TBL_State`
ADD CONSTRAINT FK_TBL_State_TBL_Country FOREIGN KEY (Country_ID) REFERENCES TBL_Country (Country_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_User`
--
ALTER TABLE `TBL_User`
ADD CONSTRAINT FK_TBL_User_TBL_Challenge FOREIGN KEY (Challenge_ID) REFERENCES TBL_Challenge (Challenge_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_User_TBL_Location FOREIGN KEY (Location_ID) REFERENCES TBL_Location (Location_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_UserContact`
--
ALTER TABLE `TBL_UserContact`
ADD CONSTRAINT FK_TBL_UserContact_TBL_ContactType FOREIGN KEY (ContactType_ID) REFERENCES TBL_ContactType (ContactType_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_UserContact_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `TBL_UserPermission`
--
ALTER TABLE `TBL_UserPermission`
ADD CONSTRAINT FK_TBL_UserPermission_TBL_Permission FOREIGN KEY (Permission_ID) REFERENCES TBL_Permission (Permission_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT FK_TBL_UserPermission_TBL_User FOREIGN KEY (User_ID) REFERENCES TBL_User (User_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

Thanks!

JDBA

Unique constraint on case insensitive varchar column? (4 replies)

Lifeng Wang — Fri, 15 Oct 2010 09:35:03 +0000

Hi All,

I'm trying to create a table with a case insensitive varchar column as unique constraint. I'm using utf8 char-set and utf8-generic-ci collaction. So the search is case insensitive. But I still can insert another records with same string but different case. I tried to use unique(upper(columnname)), but MySQL complain syntax error. Anybody know a way to do it?

Thanks

Lifeng

Default value is not used (4 replies)

Kimberly Rowe — Fri, 17 Sep 2010 14:47:00 +0000

Is anyone aware of a reason that a table with a default constraint on one of the fields would ignore the default value when creating a new row?

We have an application written in Delphi that has been inserting new rows in this table for many months with the default value. Just a few days ago, the new rows began to be inserted with the field value of 0 instead of the default value 1. The field type is tinyint, unsigned. The application code has not changed, nor am I aware of any MySQL patches to the system.

Obviously something has changed, but I'm not sure what else to look for.

Conditional Constraint (no replies)

Susan Hurst — Mon, 05 Jul 2010 02:40:46 +0000

I have several association tables that have a primary key based upon, for example, entity id, entity type and address id. An entity can be a person (prsn_id), an organization (org_id) or a location (loc_id).

How do I create (a) foreign key(s) that kicks in depending upon the entity_type? I cannot just create these foreign keys...
ALTER TABLE entm_ent_eml_assn ADD FOREIGN KEY fk_ent_id_prsn REFERENCES prsn_prsn(prsn_id);
ALTER TABLE entm_ent_eml_assn ADD FOREIGN KEY fk_ent_id_org REFERENCES org_org(org_id);
ALTER TABLE entm_ent_eml_assn ADD FOREIGN KEY fk_ent_id_loc REFERENCES loc_loc(loc_id);
ALTER TABLE entm_ent_eml_assn ADD FOREIGN KEY fk_ent_typ_ldat_id REFERENCES ldat_lkup_data(ldat_id);
ALTER TABLE entm_ent_eml_assn ADD FOREIGN KEY fk_eml_typ_ldat_id REFERENCES ldat_lkup_data(ldat_id);

... because the ent_id and ent_typ_id are not tied together. It is possible to have the same ent_id but different ent_typ_ids. I don't want to disallow a valid ent_id if it's a prsn when the same ent_id might exist as an organization, for example. Even it I do a UNIQUE KEY constraint instead, I have the same issue.

What is the best way to manage this kind of conditional constraint?

Thanks for your help!

Sue

date constraint (3 replies)

Katja Fink — Mon, 05 Jul 2010 12:08:45 +0000

Dear all,

can I define a date defintion intervall beginning from a start date?
Tthe date column should be not smaller than project date and not greater than sysdate - all other dates before or behind would be false data and need to be shown as a warning. How can I realise this?

Example:

Project Start - 01.01.2010
wrong value 15.12.2009 (before project date)
wrong value 15.12.2010 (greate than today)

Greetings form Germany,

Cathy

How to create a table that distinguishes greeks 'ο' from 'ό' on INSERT but considers both identical on SELECT (3 replies)

Ni Angel — Wed, 14 Apr 2010 11:12:43 +0000

Hi there
I am in the process of building a database.
One of the tables would be the SURNAMES table.

eg. table SURNAMES
SurnameID BIGINT AUTOINCREMENT
Surname VARCHAR(100) UNIQUE

I need to declare the table in a manner where

1) given that the name 'Κόντου' exists
the surname 'Κοντού' would be allowed to be added in the table
while an attempt to insert the identical to the existing one 'Κόντου' would fail.

BUT

when I run the query
SELECT * FROM Surnames where Surname LIKE CONCAT ('Κον', '%')
would return both records.
Κόντου
Κοντού

So far I have tried the greek_bin collation which works fine restricting the insertion of identical surnames but allowing the insertion of both of the above. The SELECT query though does NOT return both of them.

On the other hand the greek_default or the greek_general DOES allow identical insertions!!! despite the UNIQUE declaration of the Surname field.

I would value your expert opinion
Thanx a million