New answers tagged

1

The question is all over the place but I think this is what you are looking for select a.name, u.name, b.note, b.date from table_B as B join table_A as u on u.ID = B.ID join table_A as a on a.ID = B.admin_id where B.ID = @userID you can create a line with select a.name + " took action on " + u.name ...


0

There are a few issues that come to mind. A normalized approach (jobs in one table, addresses in another, related only by a foreign key) is desirable from a purely database design perspective. It makes both storage and reporting more efficient. With this normalized schema, however, you'll want to make sure that an address isn't entered in duplicate (for ...


0

To retrieve data from both table associated with foreign key i.e(common column) you have to join both the tables. if you matching data from both table then use INNER JOIN. > SELECT A.List_Of_columns,B.List_Of_columns FROM Table1 AS A INNER > JOIN Table2 as B ON A.ID=B.ID (Here Id is Common in both table). If you want matching data from both ...


0

For getting the result from the two table associated with the foreign key, you need to join the tables. Find below query as like you want the result: SELECT name, date, roll FROM A INNER JOIN B ON A.id = B.id;


1

You need to join these tables to get the result that you want. SELECT b.*, a.name FROM tableB AS b INNER JOIN tableA as A ON (b.id=a.id); This query will return everything from Table B and name from Table A where the ID from Table B is the same as the ID from Table A.


3

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one: SELECT a.id, a.name, a.num, b.date, b.roll FROM a INNER JOIN b ON a.id=b.id; INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want ...


2

As mentioned in the comment by jkavalik constraints are there to enforce data integrity. While most modern optimizers can use the information in constraints to help make access decisions that is not their purpose. Here is a question you need to ask yourself - if the integrity of the data is not important - then how important is the data? If it is worth ...


3

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but ...


0

TL;DR -- explain how db's work Long version: You can't show that removing a FK can not cause corruption, because that is not actually true -- theoretically it could. What you can show, is that it doesn't cause corruption and explain why the chance of it causing corruption is more theoretical than actual. I would start with why the chance of it actually ...


8

As pointed out by Mr Brownstone (this would just be a comment but it turned out too long) the behaviour you are expecting can be achieved if you define the key as ON DELETE CASCADE. I would be very very careful with ON DELETE CASCADE, in fact I almost never use it and generally recommend against it. This counts for triggers that take action based upon rows ...


8

You need to specify that you want the delete action to cascade like so: CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`) ON DELETE CASCADE If you do not specify either the ON DELETE or ON UPDATE then it defaults to RESTRICT, from the documentation: For an ON DELETE or ON UPDATE that is not specified, the default ...


2

With the 1st design, you can enforce that a Part is related (through SubItem and Type) to the same Item. With the second, you can't (through DDL alone, you need triggers as you already mentioned to enforce that or make sure all CRUD operations are done through stored procedures that take care of the restriction). Otherwise you may have a Part that is related ...


0

Tried to create a database with the exact tables, PKs and FKs as yours and I receive no error. Tried both creating the tables with the constraints and without it, creating the relationships via PHPMyAdmin. This "Error" you're getting really doesn't have a message or a error number? To answer you, yes, you can set two columns as Primary Keys that are also ...


1

You are asking three questions: Can a foreign key be a primary key in the same table? The answer is yes. Here is an example. Suppose that you have a table Projects, with columns ProjectId, Name, Description, Manager, Budget, and also a table SpecialProjects, that contains additional information about certain special projects, with other columns, ...


2

Let's start with definitions then go into differences. Primary Key There can be only one Primary Key per table. It is one of the candidate keys. A candidate key is a key comprised of a column or group of columns that uniquely identify the row. In SQL Server none of the values can be NULL but I can't say for certain that is true in other DBMSs. Foreign ...


1

I suspect this is a duplicate but I cannot find it Typically another table and even some definitions state another table Repeating values is the norm for a FK FK point to the PK of another (or the same table) is the common configuration. In MSSQL it can be a column(s) with a unique constraint (and not PK). The row must be uniquely identified. ...


0

This should clarify things for you:- https://msdn.microsoft.com/en-gb/library/ms179610.aspx



Top 50 recent answers are included