1
vote
1answer
19 views

How to put this particular constraint on this link table in MySQL? [duplicate]

I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table ...
0
votes
2answers
50 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
1answer
234 views

Cannot add foreign key constraint while creating table

My Error Code is 1215 which finds problem with the foreign key constraint. I know all fields are the same data types. I can't see where the problem is. Any help would be much appreciated! Thanks! ...
1
vote
1answer
100 views

How to insert data into tables which reference each other?(MySQL)

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet). I'm not going to give my «homework» because I want to learn what I'm doing but I will ...
1
vote
1answer
27 views

How to force MySQL Workbench to use constraints in my file?

I'm currently working on a project that requires I write my own constraints for my database (to show I know how they work). I'm attempting to test them in MySQL Workbench however the programs settings ...
0
votes
0answers
38 views

Two different unique constraints for 1 table?

I must enforce uniqueness within my table, But I cannot seem to find anywhere online(mysql manual,forums,stack overflow) talk about two unique constraints within one table. Why you might ask I need ...
0
votes
0answers
65 views

Error adding constraint foreign key to bridge table

Hi there I am fairly new to sql development. I am current following the head first into mysql book and is on chapter 6. On chapter 6 there is a table in third normal form but it does not contains the ...
2
votes
1answer
76 views

Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him. I want to insure that the host you assign to the domain belongs ...
1
vote
1answer
412 views

MySQL: update instead of delete if foreign key constraint?

I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints? For example: ...
2
votes
2answers
153 views

MySQL - unique constraints design

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community. Please consider the diagram: I would like to add a unique ...
2
votes
1answer
952 views

prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index. MySQL accepts this query: CREATE TABLE IF NOT EXISTS ...
1
vote
1answer
176 views

MySQL import LOCK doesn't prevent PRIMARY key issues

I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks). Some info; my tables are almost all ...
0
votes
0answers
98 views

Why does mysql show Primary key constraint on a View?

As far as I know views cannot have a primary key. However the result of SELECT FROM KEY_COLUMN_USAGE says differ. PRIMARY | S_MOBILE_ITEM_VIEW | ID | ...
0
votes
1answer
174 views

Creating a partial unique constraint for MySQL

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL. My ...
1
vote
1answer
520 views

ERROR: Error 1005: Can't create table 'progetto.museo' (errno: 121)

I've found on the web that: You will get this message if you're trying to add a constraint with a name that's already used somewhere else and If the table you're trying to create includes a ...
3
votes
1answer
231 views

Is it possible to check all constraints in a MySQL database?

MySQL offers the ability to temporarily disable the foreign key checks, thus allowing inconsistent data to enter the database. Is it possible to check an entire MySQL database, table by table, row by ...
3
votes
4answers
2k views

MySQL unique constraint across two columns

I have a table defining relationships Src smallint(5) unsigned NOT NULL, Dst smallint(5) unsigned NOT NULL, other fields I need to add a constraint that says if a given values is present in one of ...
3
votes
2answers
169 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
2
votes
1answer
1k views

MySQL: Unique constraint on large column

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column. MySQL appears to ...
0
votes
1answer
177 views

How to set unique columns combination

see this table: CREATE TABLE IF NOT EXISTS `default_messages_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `owner_user_id` int(11) NOT NULL, `to_user_id` ...
4
votes
1answer
3k views

MySQL get next unique value without auto increment

I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100. WorkorderID ...
1
vote
1answer
1k views

Why do my ON UPDATE/ON DELETE rules disappear?

I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working? I am ...
3
votes
2answers
5k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I ...
9
votes
1answer
3k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
1
vote
2answers
2k views

Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of CREATE TABLE column1 ( first_id int(11) NOT NULL AUTO_INCREMENT, ...
8
votes
3answers
6k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK ...
5
votes
4answers
813 views

Making a column immutable in MySQL

I want to make a column in a relation unmodifyable for consistency reasons. The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
6
votes
3answers
3k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...