149

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans! Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has ...


69

Your syntax is almost good, needs some parenthesis around the subqueries and it will work: INSERT INTO bar (description, foo_id) VALUES ( 'testing', (SELECT id from foo WHERE type='blue') ), ( 'another row', (SELECT id from foo WHERE type='red' ) ); Tested at SQL-Fiddle Another way, with shorter syntax if you have a lot of values to insert: ...


69

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here. In the SQL 2003 standard there are 5 different referential actions: CASCADE RESTRICT NO ACTION SET NULL SET DEFAULT To answer the question: CASCADE ON DELETE CASCADE means that if the parent record is ...


61

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'My_Table'; And then you can remove the named constraint in the following way: ...


49

Yes it's a terrible idea. Instead of going: SELECT Deal.Name, DealCategory.Name FROM Deal INNER JOIN DealCategories ON Deal.DealID = DealCategories.DealID INNER JOIN DealCategory ON DealCategories.DealCategoryID = DealCategory.DealCategoryID WHERE Deal.DealID = 1234 You now have to go: SELECT Deal.ID, Deal.Name, DealCategories FROM Deal ...


43

Plain INSERT INSERT INTO bar (description, foo_id) SELECT val.description, f.id FROM ( VALUES (text 'testing', text 'blue') -- explicit type declaration; see below , ('another row', 'red' ) , ('new row1' , 'purple') -- purple does not exist in foo, yet , ('new row2' , 'purple') ) val (description, type) LEFT JOIN foo f ...


41

Put the foreign keys on the database. Even if you validate the data in the application before you save it the FK's are a good piece QA backup. For a first approximation, applications always have data issues. Leaving controls like this out of the system just invites failure modes where data gets corrupted silently. There's nothing like working in data ...


39

Check the CREATE TABLE page of the manual: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. ...


36

Here's a simple query to match up foreign keys to their referenced tables/columns: SELECT o1.name AS FK_table, c1.name AS FK_column, fk.name AS FK_name, o2.name AS PK_table, c2.name AS PK_column, pk.name AS PK_name, fk.delete_referential_action_desc AS Delete_Action, fk.update_referential_action_desc AS Update_Action FROM sys....


33

For example, if I have two tables - Parent and Child - where Child records are owned by Parent records, which table needs the ON DELETE CASCADE? ON DELETE CASCADE is an optional clause in a foreign key declaration. So it goes with the foreign key declaration. (Meaning, in the "child" table.) ...it could mean delete the Parent record when the Child ...


31

You can use the function pg_get_constraintdef(constraint_oid) in a query like the following: SELECT conrelid::regclass AS table_from , conname , pg_get_constraintdef(oid) FROM pg_constraint WHERE contype IN ('f', 'p ') AND connamespace = 'public'::regnamespace -- your schema here ORDER BY conrelid::regclass::text, contype DESC; Result: ...


31

Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against ...


30

This calls for a recursive CTE: WITH FindRoot AS ( SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path FROM dbo.MyTable UNION ALL SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)) FROM dbo.MyTable P JOIN FindRoot C ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id ) SELECT * ...


29

Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key. A good example is a table of employees: EmployeeId Int Primary Key EmployeeName String ManagerId Int Foreign key going back to the EmployeeId So in this case there is a ...


28

Have you tried the --disable-triggers option to pg_restore? Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload. Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.


25

innodb foreign key. It's just short hand naming convention. You could call it asdfqwerty and the name would still work.


21

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that ...


19

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB. @Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you ...


19

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


18

Foreign keys can be made conditional...sort of. You don't show the layout of each table, so here is a typical design showing your relationships: create table TransactionalStores( ID int not null auto_increment, StoreType char not null, ..., -- other data constraint CK_TransStoreType check( StoreType in( 'B', 'K', 'O' )), ...


18

What is relatively easy - you just have to add another step. The FOREIGN KEY column has to exist in order to make it an FK. I did the following (from here and the documentation): CREATE TABLE x(t INT PRIMARY KEY); CREATE TABLE y(s INT); ALTER TABLE y ADD COLUMN z INT; ALTER TABLE y ADD CONSTRAINT y_x_fkey FOREIGN KEY (z) REFERENCES x (t) ...


18

Managing an individual piece of information Assuming that, in your business domain, a User can have zero-one-or-many Friends; a Friend must first be registered as a User; and you will search for, and/or add, and/or remove, and/or modify, single values of a Friend List; then each specific datum gathered in the Friendlist_IDs multivalued column represents a ...


16

There are a few problems with your tables. I'll try to address the foreign keys first, since you question asked about them :) But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same. Of course, the definition of Table3 in your second ...


16

I think you can, using a "diamond" relationship diagram: CREATE TABLE Artist ( artistID INT NOT NULL , name VARCHAR(100) NOT NULL , PRIMARY KEY (artistID) ) ; CREATE TABLE Album ( artistID INT NOT NULL , albumID INT NOT NULL , title VARCHAR(100) NOT NULL , PRIMARY KEY (artistID, albumID) , FOREIGN KEY (artistID) REFERENCES Artist (artistID) ) ; CREATE ...


16

Hierarchical queries, as those recursive queries are known, are not supported for MySQL. They are however supported in Oracle, Microsoft SQL Server, DB2, and PostgreSQL, amongst others. If you need a workaround, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries You ...


16

SQL Server allows you to do a lot of silly things. You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself. One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for ...


15

Referential Integrity should be handled on the lowest possible level, which would be the underlying database. Relational Database Management Systems are optimized to handle this. It doesn't make sense to reinvent the proverbial wheel. It is acceptable to define domain logic in the application code to prevent the DML statement to even cause an RI exception, ...


15

To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index. This can be a good optimization, though the details depend on your hardware, how likely the affected pages ...


15

Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of ...


15

Here's a better script. It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints. It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like ...


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