|
Because you have multiple FKs between the tables. It's a bad design, but it's possible - so long as only one of the FKs specifies a "cascade" rule for update or delete.
[A] table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement.
You're trying to use a foreign key for something that it wasn't designed for. If you can't change the table design, switch to issuing multiple queries, or using a trigger to push the changes from Product1 to Product2.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi
I would suggest you spend some time understanding Normalization of tables and reference integrity. If you have a good understanding of these concepts then it will be a lot easier for you designing the tables in the right way and fixing any errors.
|
|
|
|
|
Hi,
In MySQL, I have basket field call item_name in my order_basket table.
I want an SQL statement to take all item_name and INSERT it into a variable call param_details whch a TEXT type with a "\n" between them.
something like this
DECLARE param_details text;
INSERT CONTACT(item_name, "\n" INTO param_details FROM order_basket
How can I do this please?
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
DECLARE @var AS NVARCHAR
SELECT @var = field1 FROM table1 WHERE ...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
but this is the issue, the item_name is in separate rows and I want to make them in one variable with a line feed, they are something like this:
Vienna Cheesecake
Caramel Crunch
Wedding Package
Graduation Sprinkles Cake
Red Velvet Cupcake
Original Cookies
in separate rows
Technology News @ www.JassimRahma.com
|
|
|
|
|
DECLARE @var NVARCHAR(MAX)
SET @var = ''
SELECT @var = @var + '[LINE-BREAK]' + field1 + field2 + field3 FROM table1
SELECT @var
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
|
I think you're looking for GROUP_CONCAT()[^]. As far as I understand it, you don't need the grouping aspect of it but there doesn't seem to be an equivalent function without it. So you need to provide the GROUP BY clause with a column whose value is equal for all rows whose item_name you want to concatenate. Pay attention to the remark about the maximum string length returned by the function in the linked documentation.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I'm in my last year in college and creating an application for a company is a major requirement for my diploma.
I will be using C# (MS Visual Studio) to create a transaction processing system with its own database. However, after doing some research, it seems that I'm still unable to decide which would be the better choice for the system I'm planning to create.
Some say MySQL has more advantages, some say SQL Server does a better job at handling data for businesses. I can't seem to find any consensus. I've only had experience with MS Access, and a bit with MySQL for Web programming.
To be honest, as of now I don't have a list yet of the specific requirements of the system since I will still have to meet with my advisers. It will however involve the basic functions of a common TPS: adding/deleting/editing data, viewing data, printing of reports, database management. There will be loads of data to be handled as this will be developed for business use. I am also planning to include an audit trail feature, so there's time-tracking involved.
What would you advise?
What I have tried:
After looking up several articles on the internet I still can't decide which would handle the basic needs of a TPS better. Even some of my instructors have conflicting opinions on this; some advise me to use MySQL, some advise me to use SQL Server.
|
|
|
|
|
kmllev wrote: What would you advise?
Look at the price first.
One may have more features than the other, but that is hardly relevant if you're not using those features.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
But if you can live with the limitations, the price for SQL Express[^] is the same as the price for MySQL. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, as much as possible I would like to stick to something that's free. I have MySQL Workbench, and SQL Server has an Express version.
|
|
|
|
|
Ignoring the features like MARS and platform-dependencies; which of the two do you have more experience with?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have more experience with MySQL Workbench because I've done Web programming before, and initially I was already planning to go with it for my project since I'm more familiar with it. However, a schoolmate of mine also developed such TPS, an electoral system to be precise, and he used SQL Server for it, and he recommended it to me, and with all these different opinions it's hard for me to decide.
|
|
|
|
|
To turn the question around a bit, which one do you want to have experience with?
|
|
|
|
|
I´m sorry to mess things up for you.
But I have to recommend you have a look at Postgresql with Entity framework.
It´s just awesome, very productive when writing code.
|
|
|
|
|
I will definitely look into it and put it into consideration. Thank you.
|
|
|
|
|
Nothing that you have described depends on a specific database.
The question of SQL Server or MySQL therefore becomes a personal choice.
And, in this setting, that's all it is - personal choice, normally shaped by personal experience.
For the very common functionality that you describe, you will not see much advantage either way.
In a real life business situation, the scenario changes. You would probably have the resources to do large-scale performance testing for your specific scenario, and could come to a factual conclusion that way. Or, more likely, your employer would dictate which database you were to use.
But you're not in that environment. And it really does not matter. Here, it's on the same level as "Do you like blue cars or red ones?"
Code your solution in such a way that the database logic remains in the database - stored procedures, functions, etc. That way, assuming that you are connecting by way of something like Entity Framework or even ADO.net, your C# code should be talking to your database's interface - calling specific procedures or function names, rather than relying on database-specific functionality.
That way, it really does not matter which database provider you choose. So long as the entities you create within the database present themselves the same way, you can switch (for example) from SQL Server to MySQL to Oracle to Access easily.
Make your application configurable, if you like. That way, a simple change to a config file can allow your application to switch databases. You'll rarely find a real life case that calls for that, but it might get you a few more points when you hand in your assignment! Good luck!
|
|
|
|
|
I've set this up before in the past and it seems like this was easy...
I'm staying in a hotel for a while. I'm trying to connect to SQL 2008 R2 on my Win Server 2008 machine at home.
I followed this article[^] which looks very complete.
I get the standard message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections
I still can't connect. Anyone have any thoughts.
If it's not broken, fix it until it is
|
|
|
|
|
Check the Name of the server in your connection string. Then swap it for the IP address, I've seen this happen before.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I tried it using SSMS. I entered the IP address into SSMS and tried to connect. I've done it before. Not sure what's wrong now.
If it's not broken, fix it until it is
|
|
|
|
|
I'm new to clustered SQL Servers so bare with me.
Anyway, if I run an SP that basically copies a few records from one (large) table to another, it goes in a couple of seconds if I connect to the IP of the active node.
However,if I connect to the cluster IP the same SP takes three hours to run.
Why the big difference? I don't even know where to start looking, any hints are welcome.
|
|
|
|
|
I am doing a student attendance project in php & mysql.
I have a monthly table for recording attendance of students. the first column in the table is the roll number which is common for all the tables and the dates of each month have a column of their own.
suppose if the attendance is of 5 months, I have 5 tables of the same repeated table.
I want to create a table or view that has columns for showing total of each month with the students roll number i.e. student roll number column, first month total column, second month total column, so on.
Can anyone please help me how to do it in mysql?
|
|
|
|
|
Member 11840363 wrote: if the attendance is of 5 months, I have 5 tables of the same repeated table.
Let me get this clear, every month you create a new table for attendances. In which case you should start again and design your data structure to be sustainable, that design is absolutely NOT.
You need just 1 table
StudentID (roll number)
Date
If you have a table for each month then whatever you create you are going to have to redo every month unless the tables are year independent IE tblJan, tblFeb etc. This is still a really bad design but can be worked with under duress.
You can create a view using UNION and a select statement for each month table but you will need to add another select every month.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for taking out some time to reply to my query. I have tried Union and select statements but I am not getting the desired result. It would be of great help if you show me some snippet or syntax of the query for example joining 4 tables using union and select.
For the tables creation part I was thinking about creating procedures to create a table whenever needed, and at the time of creation the name of the table to be created and the table it references should be asked.
It would be great help if you could help with the above both.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
