Tag Info

Hot answers tagged

46

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


30

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


14

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


11

Every result from a SQL operation is functionally a new table, whether or not it is stored on disk or in memory The function of a join is to "Join" two tables together into a synthetic third table that (usually) only exists in memory during the time it is output to the application. The reason to use a join is to reduce Data Anomalies, by insuring that data ...


11

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


11

Plain INSERT INSERT INTO bar (description, foo_id) SELECT val.description, f.id FROM ( VALUES ('testing', 'blue') ,('another row', 'red' ) ,('new row1', 'purple') -- purple does not exist in foo, yet ,('new row2', 'purple') ) val (description, type) LEFT JOIN foo f USING (type); The use of a LEFT [OUTER] JOIN ...


10

I'm going to go out on a limb here fully expecting this to get down-voted since this is a DBA-focused group. I agree that using strict foreign keys is the best decision in most scenarios. However, there are some cases where foreign keys cause more problems than they solve. When you are dealing with very highly concurrent environment such as a high ...


10

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


9

Create a unique index or unique constraint on UserName then you can reference it in a FK constraint fine. Your statement that Sql Server doesn't allow me to create a relationship on a non primary key column is incorrect. SQL Server only cares that the column(s) participating in the FK relationship have a unique index defined.


9

I'm only familiar with SQL Server: Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back. If you're relying on the ID values and don't want to cascade the related tables, consider ...


9

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


9

It needs to validate that the row you are trying to delete is not a parent of an existing row. You don't have an index on ParentTestId. So it must do the scan. CREATE NONCLUSTERED INDEX ix ON [dbo].[Test](ParentTestId) Then you see a seek. BTW: The 20% estimated cost of the scan is likely to be an underestimate in this case. The FK validation is ...


8

Proper solution The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this: CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); ...


7

First of all, if this is homework, please tag it as such. Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand ...


7

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution: SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLS.COLUMN_NAME, CONS.R_CONSTRAINT_NAME, CONS_R.TABLE_NAME R_TABLE_NAME, ...


7

In addition to JOINs another benefit of indexing Foreign Key columns is that it can speed up enforcement of the Foreign Key constraint for some DML operations. If you delete a row from an Orders table then the RDBMS would need to ensure that this would not leave an orphaned row in OrderDetails. Obviously this is easier if it can be verified with the use of ...


7

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


7

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 childs of the parent row are killed (deleted), too. If any of these children has ...


6

Solution 1: Script all of your foreign keys out and create the tables on the new server without foreign keys. Load the data, then rerun the scripts to create the foreign keys. Solution 2: Backup the database, the restore it to the new server. Solution 3: Run a query similar to this and figure out the dependencies for yourself. Pick the one that sounds ...


6

A View is a logical table that is based on one or more physical tables. If there are foreign key relationships in the underlying tables, then they will be manifested in the view. Views are entirely dependent on the tables they are derived from, so trying to add foreign keys to them is not possible.


6

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea: can't have filtered foriegn keys can't FK to constants can't have multiple parents Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I ...


6

I think you're looking for SELECT username, cities.city FROM Users JOIN Cities ON (Cities.Id = Users.City) which would give you | username | city | | John | Singapore | | Toby | London | | Eric | Paris | but don't take my word for it: try it out on your database and see what you get!


6

Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to Choose (and document) what it means for each nullable field Make sure it means one thing only With those rules, consider the following use case: You have a ...


6

This will be non-specific to oracle, but in general foreign key indexes are most useful because they speed up JOINs. If you have a foreign key field, it is extremely likely you will use it as a JOIN key as well - that's the whole point of an FK, to make a direct association between one field in tablea to another field in tableb. Having this field indexed ...


6

You can do this (assuming I'm understanding you correctly) via having foreign keys in your child tables referring to the parent table. CREATE TABLE Parent ( ParentID INTEGER PRIMARY KEY) CREATE TABLE ChildOne ( ChildOneID INTEGER PRIMARY KEY, ParentID INTEGER, FOREIGN KEY (ParentID) REFERENCES Parent(ParentID) ) CREATE ...


6

It is good practice to use foreign key in the database. It helps- to keep data integrity by removing the possibility of unwanted data to increase performance. In systems which auto index fields, foreign key references can give a performance boost to write less code by the programmer. like, using ON DELETE CASCADE


6

Assuming a company can have more than one shipping address, the key would be a column added on Shipping Address table (aptly named 'customer_id' and the foreign key definition on the Shipping Address table. If you wanna avoid same address more than once, also add a unique key on shipping address table that can include (customer_id, contact_name) On an ...


6

You can't implement this as a single constraint; you'll need to create two: ALTER TABLE dbo.B ADD CONSTRAINT FK_HomeTeam FOREIGN KEY (homeTeam) REFERENCES dbo.A(teamName); ALTER TABLE dbo.B ADD CONSTRAINT FK_AwayTeam FOREIGN KEY (awayTeam) REFERENCES dbo.A(teamName); As I alluded to in my comment, it would be much more efficient to store TeamID in ...



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