Tag Info

New answers tagged

0

Create a new table that is partitioned as you want and copy the data from the old table into it rather than trying to apply the new partitioning scheme to an existing table. Once the data is copied, remove the relations to the old table, drop it, and rename the new to take the place of the old and recreate all of the relations. Perform this within a ...


3

I think this is the original idea. First thing to notice is that the PK on the LineItem table has three attributes {CustomerID, CustomerOrderNo, OdrerItemNo}, as opposed to just two in your example. Second thing to note is the confusion resulting from the use of the generic id name for an attribute. The CustomerOrderNo should ideally be (1,2,3..) for ...


-2

If you don't know the answer, go with the Surrogate. Here's why - if assumptions are made about business rules, and those assumptions are false or the rules change, your data is garbage. Here is an example: Person, Role, PersonRole current business rule states that a Person has one Role. You make a table that links Person and Role where PersonRole ...


2

The "item" shouldn't reference the "customer" directly, because this is implied by the item's "order". So, you won't need the "customer" columns on the "items" table at all. The item's relation to the customer is ensured with the existing foreign key. If orders.id is an identity column, consider removing items.customer alltogether.


0

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be ...


2

A foreign key is a type of constraint. The purpose of constraints is to make sure that data in tables follows certain rules. You use foreign key constraints to ensure that child data has parent data. You can use it to either prevent orphaning child records or to facilitate cascade deletes or updates.


2

Keep it. A foreign key exists primarily to ensure that the value given in the child table really does exist in the parent (foreign) table. Cascading deletes or updates are, generally, a distant second. That's the general case; your specific business rules may be different.


2

I have an old post Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios. In that post, I do not use a foreign key constraint in my example. Soft deletes are way faster. You just have to integrate JOINs for tombstone tables or AND deleted=0 in WHERE clauses. In light of these things, you probably could live without the ...


0

Please check that the FOREIGN KEY is in the table design of your table B. EXAMPLE CREATE TABLE projects ( StaffID INT NOT NULL, ProjectName VARCHAR(20) NOT NULL, Allocation INT NOT NULL, PRIMARY KEY (StaffID, ProjectName) , FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE ); Facts Table A is the table Staff Table B ...


5

Yes. It's in the MSDN documentation pages: Foreign Key relationships A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list. That page does not ...


0

I've used a recursive CTE that works from each node back towards the root, child to parent. If at any stage the path loops onto itself it will set a flag to stop the recursion. Each node other than the root node is considered as a start (anchor) point for the recursion. WITH rcte AS ( -- Child Nodes SELECT a.ID BaseID, a.ParentID, ...


3

I think you're looking for a recursive common table expression. WITH rcte (Id, ParentId, [Path]) AS ( --- Anchor: SELECT Id, ParentId, CAST(CAST(Id AS varchar(10))+' > '+ CAST(ParentID AS varchar(10)) AS varchar(max)) FROM MyTable UNION ALL --- Recursion: SELECT t.Id, rcte.ParentId, CAST(CAST(t.Id ...


2

is this what business process of yours? that must be document table : create table document ( `id` int unsigned auto_increment, `title` varchar(128) not null, primary key(id) ); block table create table block ( `id` int unsigned auto_increment, `id_document` int // foreign key to document primary key(id) ); ...


1

For the table A (with B_id) and table B (with id) Count of Primary Keys By Foreign Key SELECT FK,COUNT(1) PKCount FROM (SELECT IFNULL(B.id,0) FK FROM A LEFT JOIN B ON A.B_id = B.id) K GROUP BY FK; If FK is 0, then PKCOUNT is a count of Primary Keys that do not have a Foreign Key To be 100% homogenous, FK should never show up as zero(0) (In a Perfect ...


4

This is not a great design. You have a couple of options to improve it: Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints. Put the parent key in the child table. If every child record refers to one master record, ...


1

I was going to write a long post telling you that there are basically 3 ways to implement polymorphic associations, but someone (@Bill Karwin) has already done it better in a more compact way: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32 Your original solution is the first one presented on the slides, the one you propose is the 3rd ...


2

Example 4 has the fewest scans and reads: Example 1 SQL Server parse and compile time: CPU time = 4 ms, elapsed time = 4 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. example1 Id FirstName 1 2 Aaron 1 3 John 1 8 Aaron 1 9 John 1 14 Aaron 1 15 John 1 20 Aaron 1 ...



Top 50 recent answers are included