All Questions
29 questions
0
votes
0
answers
14
views
Composite Key MySQL Dual Direction Comparison [duplicate]
The question in the tile might not be clear for what I'm asking for so I'll add some examples below.
What I'm asking is for is when you insert a row in a table with composite keys, a single row is ...
- 738
0
votes
2
answers
89
views
Mysql: optimizing a query that uses one column or the other
The table contains chat data with from_user_id and to_user_id columns both individually indexed properly. My query is to get all chat msgs that a particular user participated in i.e.
SELECT * FROM ...
- 1
-1
votes
1
answer
120
views
how can i insert a composite component (menu of menus) in my database
I am implementing the Composite Design pattern and I need to insert a menu in the database but the menu may consist of other menus and menu items so when I tried to insert them recursively, I got an ...
0
votes
0
answers
561
views
Composite unique index & INSERT INTO ON DUPLICATE KEY UPDATE
My head is about to explode, I'm in need of the following scenario in which column a is a foreign key INDEX. Also, a UNIQUE index of (a,c) exists, and column c is either null or not
Here's my current ...
0
votes
0
answers
329
views
How to add NOT NULL constraint as composite in MySQL ?
I want to add NOT NULL constraint for some multiple columns. Individually those columns can be NULL. But altogether there should there should be some data. Is it possible with MySQL?
- 21
0
votes
2
answers
7k
views
creating composite field in mysql
I need to make column 'full_name' as composite column of columns 'first_name' and 'last_name' in MySQL. I can do the same in MS SQL using following syntax :
create table customer
(
first_name ...
5
votes
1
answer
6k
views
Usage of MySQL foreign key referencing multiple columns
I just stumbled across possibility of MySQL foreign key to reference multiple columns. I would like to know what is main purpose of multi-column foreign keys like shown bellow
ALTER TABLE `device`
...
- 788
0
votes
1
answer
65
views
Database design for an entity that might comprise from other same entities
I have some sport facilities that have fields that one can play 5x5 football in them. I am trying to make a simple reservation system for them.
My problem is that some fields combine and make bigger ...
- 848
3
votes
1
answer
205
views
Constraint to prevent violation of FK constraint in a third table
I can implement the constraints that I want in stored procs, but I'm wondering if I can define a set of foreign key constraints which will do the job.
I have several tables, with these key ...
- 6,155
0
votes
1
answer
1k
views
Duplicate entry error in 1 column of a composite key
I am trying to insert pseudo data into my db to get going, and in one particular table I have two columns which are FK's and PK's of the table; fk_product_manf_code and fk_content_id. To my ...
- 747
1
vote
1
answer
42
views
How can I get data from a specific field with a composite primary key?
I have three tables, sketched below. The showid and actorid from the Shows-Actors table comes from the id column in the Shows and Actors table. They combine to form a composite primary key - An actor ...
- 25
0
votes
4
answers
308
views
SQL multi-table composite key unique query
For starters, some background information. I am building a simple recipe book database and I'm trying to build a query that will show me what I can make based on the items in my pantry. The table ...
1
vote
0
answers
360
views
MySQL, InnoDB: two-column unique constraint with one column in parent table
In a child table, I need to enforce a unique constraint with a composite key where one of the two columns is in the parent table.
The situation is that in my application users can have multiple ...
- 21
-3
votes
1
answer
83
views
i want to create composite foreign key but it gives my sql error 1005
Table complaint_record with composite primary key
CREATE TABLE `complaint_record` (
`complaint_id` int(11) NOT NULL AUTO_INCREMENT,
`cat_id` int(10) unsigned NOT NULL,
`store_id` int(10) ...
- 1,141
4
votes
2
answers
5k
views
MySQL column order in composite key
I have a doubt, Here is my table:
mysql> show create table example\G
*************************** 1. row ***************************
Table: example
Create Table: CREATE TABLE `example` (
...
- 636
3
votes
2
answers
223
views
Mysql composite key order relevance
I have this mySql table:
create table associations
(email1 varchar(30),
email2 varchar(30),
primary key(email1, email2));
Now in this table if I insert two rows as follows:
insert into ...
- 998
1
vote
0
answers
373
views
Select with composite key match my sql
I have following problem. I want to select single record from my table. That table has composite primary key. This select is in stored procedure that accepts two int parameters.
This is my query:
...
- 159
1
vote
3
answers
783
views
Composite key - one a foreign key, the other auto-incrementing
I'm having trouble creating a table in MySQL. Essentially, I need a composite key using "client_id" and "problem_id". In the table "clients", "client_id" is the primary key so I want to keep this ...
- 1,583
4
votes
1
answer
3k
views
Composite Primary Key and Auto_Increment [duplicate]
I'm trying to do the composite key with one of them auto incrementing, but when I try to enter a new row it just continue the sequential.
Here's the example of what happens:
Item_1 | Item_2
1 | ...
- 61
1
vote
2
answers
183
views
How to find max from a composite set of rows?
I have a table such as :
Year | Month
------------
2011 10
2011 11
2012 5
2012 6
The query should return the latest "Month" for the latest "year".
Currently I'm doing ...
- 455
0
votes
1
answer
262
views
mysql composite if column from composite values?
I need to add an extra column to my query results,
The column needs to be called percent,
If col1 == 0 then percent should contain NIS else percent should contain ceil(col2 / col1 * 100)
So I ...
- 92.4k
4
votes
3
answers
22k
views
How to create composite key?
I am new to MySQL WB so I can create foreign key by adding relation between to dependen tables.
But how to create a composite key?
For example I have this table sql
CREATE TABLE TASKS
(
TEST_ID ...
- 2,773
1
vote
2
answers
765
views
composite primary key and unique key as a foreign key
Ok Here is the situation.
A university has more than one faculty.
Each faculty has more than one department.
I do not want any duplicated faculty or department. So I defined the three tables below.
...
- 41
0
votes
1
answer
101
views
Select statement across a join table
Have three tables that look like this:
PersonTable
Person.ID
Person.Name
PersonTypeCompositeTable
Person.ID
PersonType.ID
PersonTypeTable
PersonType.ID
PersonType.Category
For this example, say ...
- 3,659
6
votes
6
answers
2k
views
MySQL composite indexes and operator BETWEEN
I have a question about this query:
SELECT *
FROM runs
WHERE (NOW() BETWEEN began_at
AND finished_at)
Do you think it makes sense to create composite index for began_at and ...
- 1,069
30
votes
2
answers
28k
views
MySQL: how to index an "OR" clause
I'm executing the following query
SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 >= 1000 OR field3 >= 2000)
There is one index over field1 and another composited over field2&...
- 11.9k
0
votes
1
answer
101
views
mysql composite indeces should i be using them
I got question on index strategies for mysql - mainly when to use a composite index
I have a fairly common relational db scenario, heres my table set up:
Maintable - table consisting of "products" ...
- 2,586
1
vote
5
answers
900
views
MySql Primary Key > 900/1000 bytes?
I have a composite primary key, which together turns out to be rather large (~2000 bytes). I have no performance considerations, I just would like a primary key to enforce uniqueness.
MySql doesn't ...
- 230k
0
votes
1
answer
587
views
Scoped/composite surrogate keys in MySQL
Here's an excerpt of my current database (changed the table-names for an easier understanding):
Pet(ownerFK, id, name, age)
Owner(id, name)
Where id is always a surrogate key, created with ...
- 54.7k