Tag Info

Hot answers tagged

47

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


20

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


15

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

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

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

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

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


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

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

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


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

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


8

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.


8

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


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

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


7

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.


7

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


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

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

The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say: If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that ...


7

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space: CREATE TABLE dbo.OtherTable ( ID INT PRIMARY KEY IDENTITY(1,1), Blah VARCHAR(100), FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL ) ; ALTER TABLE ...


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

FWIW ISBN is terrible as a primary key. For one, what happens if you get a book you want to put up for pre-order, but the ISBN hasn't been assigned yet? What happens when the ISBN changes (yes, this happens!)? What happens when they change the ISBN format yet again? I would say make that a candidate key but use a surrogate for the PK. Adding to that the ...


6

Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like foreign key (Name, BoughtFrom, TimeBought) references the_other_table_name (Name, BoughtFrom, TimeBought) If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}. You can do that with ...



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