Hot answers tagged

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 ...


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 ...


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 ...


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

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.


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 ...


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 ...


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 ...


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 ...


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, ...


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. ...



Only top voted, non community-wiki answers of a minimum length are eligible