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


13

There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns. It's like having the same check 2 or more times.


12

There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value. The SQL Server database engine does not stop you from doing so. Good constraint naming constraint naming conventions (e.g. FK_ReferencingTable_ReferencedTable) ...


6

Well, after continue searching I found this article Unlike a normal query, it won't pick up a new index due to statistics being updated, a new index being created, or even a server being rebooted. The only way I'm aware of to have a FK bind to a different index is to drop and recreate the FK, letting it automatically select the index with no options to ...


5

Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.


5

A foreign key can also constrain and reference a group of columns. As usual, it then needs to be written in table constraint form. Here is a contrived syntax example: CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) ); Of course, the number and type of the constrained columns need to ...


5

Use a composite foreign key: (tree_id, parent_id) REFERENCES (tree_id, id) You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)


5

Given the next example: CREATE TABLE A ( [ID] VARCHAR(10) NOT NULL, CONSTRAINT [PK_A] PRIMARY KEY ([ID]) ); CREATE TABLE B ( [ID] INT PRIMARY KEY, [A_ID] VARCHAR(10) NOT NULL, CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID]) ); CREATE TABLE C ( [ID] INT PRIMARY KEY, [A_ID] VARCHAR(10) NOT NULL, CONSTRAINT [...


4

the situation would greatly improve if I added supporting indexes for all the FK (currently, only 20 of the 60 tables have it) You should always index fields used as keys, foreign or otherwise. This gives the database far more options when analysing how to execute the query and adding them may actually increase the overall performance of your application. ...


4

You probably receive the error because you have orphaned records in the [rpt].[ReportLessonCompetency] table. An orphaned record is a record in a child table without a corresponding parent record in the parent table. In your case the [rpt].[ReportLessonCompetency] will contain values for [Grade] that don't exist in the [rpt].TraineeGrade(Id) table. There ...


4

Primary key & Foreign key Can I designate that one column of a child to be both a foreign key and also a primary key? Yes absolutely: create table photo ( id integer primary key, ... other columns ... ); create table thumbnail ( id integer primary key references photo, ... other columns ... ); TOAST bytea columns are stored outside ...


4

First, stay off enums for things like this. Enum values can never be removed, so use them only if you are sure that that will never be necessary, which doesn't seem the case here. Anyway, I would say that your design is too complicated, and still lacks the crucial feature of referential integrity. Use a junction table for each pair of objects that can be ...


3

Not sure I understand all of your questions, but one fairly common model for super/sub-type is to add a classifier attribute to your supertype: CREATE TABLE super ( xxx_id ... not null primary key , classifier int not null , unique (classifier, xxx_id) , check (classifier in (1,2,...)) ) CREATE TABLE sub1 ( xxx_id ... not null primary key , classifier ...


3

Reason : When you drop an index, InnoDB checks if the index is used for checking a foreign key constraint. It is still OK to drop the index if there is another index that can be used to enforce the same constraint. InnoDB prevents you from dropping the last index that can enforce a particular referential constraint.[Reference: https://dev.mysql.com/doc/...


3

Error 150: Plan A: Rearrange the order of CREATE TABLEs Plan B: Disable FKs, create the tables, enable FKs. Plan C: Create the tables without FKs, then add ALTERs to add the FKs.


3

I think you need: for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop If a table has more than one constraints, your code will try to recreate each one of them many times. Or just use a single for loop, I don't think you need the nesting. --Recreate not_deferrable_constraints for add_constraint in (select constraint_name, ...


3

It's possible to change the data type without data loss or breaking FK dependencies. It depends on whether you have some downtime where you can execute the required change though. Changing from datetime to datetime2(7) will require every row in the table / indexes to be updated, since these two types are stored differently on disk. This can take a little ...


3

You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target table. eg -- TABLE t_parent CREATE TABLE t_parent ( m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450) ); -- TABLE t_child CREATE TABLE t_child ...


3

I think you may be misunderstanding what a CONFLICT is. A CONFLICT is a violation of uniqueness, basically the row that is being added should not be added because another row with the same values already exists. In your example of insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) values (3, 'a3', 'b3') on conflict do nothing;, the ON CONFLICT ...


3

If I understand correctly you want to insert the given row only if the FK constraint is satisfied - and do nothing otherwise, in particular do not raise an exception. This is no "UPSERT", i.e. not a use case for INSERT .. ON CONFLICT DO NOTHING, which only works for violations of unique indexes or exclusion constraints. The manual: The optional ON ...


3

I think you're making this complex. First that blog is bad advise. Welcome to the internet. Just ignore it. Use a int PRIMARY KEY GENERATED BY IDENTITY AS DEFAULT (ie, IDENTITY COLUMN until you have a reason not too Now you have two things.. comments tags Both of these can be hierarchical. That's the only thing they have in common. Tables don't model ...


3

Sure, there is a big difference. In first case you are referencing by combination of columns. It means ParentTable should contain a row for each combination of OrderId and CompanyId which you want to add to your ChildTable. In the second case it would be enough to have separate rows with corresponding OrderId and corresponding CompanyId.


2

SELECT id FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id) AND /* OR */ EXISTS (SELECT 1 FROM table3 WHERE table1.id = table3.id) When you need ids which are present both in table1 and table3, use AND. When you need ids which are present at least in one of these ...


2

There are two possibilities here: 1) is that it is a case of 2 fields in the same table being FOREIGN KEYs pointing to the same field in the parent table. 2) is that this is an example of an Associative Entity. These are also called joining, linking or many-to-many tables. 1. Double FOREIGN KEY. This is quite simple to resolve. You have two tables - ...


2

Sounds like a blue-green thing. When you begin to cutover from blue to green, you need to temporarily create extra copies of things. What we want to do is temporarily create an extra foreign key CHECK WITH NOCHECK and ON UPDATE CASCADE ON DELETE SET NULL; what this does is it is a working foreign key but the existing rows aren't checked when the key is ...


2

A simple mnemonic would be ON DELETE of parent CASCADE [by deleting] here That tells you which deletes (deletes of the parent) get cascaded, where the ON DELETE CASCADE statement goes (on the child), and what gets deleted (the child).


2

Stealing from myself (please see the caveats and comments on that article): CREATE PROCEDURE dbo.ImplementNamingStandard @SELECT_Only BIT = 1, @ForeignKeys BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX), @cr CHAR(2); SELECT @sql = N'', @cr = CHAR(13) + CHAR(10); DECLARE @TableLimit TINYINT, @ColumnLimit ...


2

You cannot have that directly, but you can do the following: Have two foreign key columns driverid and passengerid in wallet that reference the respective tables. Add a check constraint that force one of the foreign key columns NULL and the other NOT NULL based on usertype. CHECK (usertype = 'driver' AND driverid IS NOT NULL AND passengerid IS NULL ...


2

After reading MS DOCS here. To modify a foreign key To modify a FOREIGN KEY constraint by using Transact-SQL, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition. For more information, see Delete Foreign Key Relationships and Create Foreign Key Relationships. In you case I believe add a new ...


2

I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database): SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], C.TABLE_SCHEMA [PKTABLE_OWNER], C.TABLE_NAME [PKTABLE_NAME], KCU.COLUMN_NAME [PKCOLUMN_NAME], C2.TABLE_CATALOG [FKTABLE_QUALIFIER], C2.TABLE_SCHEMA [FKTABLE_OWNER], ...


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