Foreign Key Indexes
May 8, 2008
Hello,
A quick question concerning foreign keys. I have been creating a database using the database diagram view in sql server management studio, and have an issue when creating foreign key relationships. When I specify a relationship in this view I imagine what is created is simply a constraint between a unique field in one table and the a foreign key in another. My issue is, is it better to index all foreign keys? If this is the case, is there a way to automate this so I don't have to manually create an index for every foreign key?
Thanks,
Mike
View 6 Replies
ADVERTISEMENT
Oct 12, 2006
Hi All,
I know that when you specify a PRIMARY KEY or UNIQUE constraint for a table, SQL Server creates a unique index for the primary key columns.
What about foreign keys? Does SQL Server creates an index when you create a foreign key?
I have looked in Books Online and on the Internet, but couldn't find any information.
Thanks in advance.
View 2 Replies
View Related
Oct 12, 2006
Hi All,
I know that when you specify a PRIMARY KEY or UNIQUE constraint for a table, SQL Server creates a unique index for the primary key columns.
What about foreign keys? Does SQL Server creates an index when you create a foreign key?
I have looked in Books Online and on the Internet, but couldn't find any information.
Thanks in advance.
View 5 Replies
View Related
Sep 21, 2007
If i create a simple table with a foreign key constraint, does itcreate an implicit index on that given ID? I've been told this isdone in some databases, but i need to know for sure if SQL Server doesit. Has anyone heard of this before, on any other databses perhaps?Heres an example of how the foreign key constraint is being added:ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])My initial testing seems to indicate adding an index on the foreignkey column helps, but i need to know for sure. Any insight would begreatly appreciated!Bob
View 6 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Jul 1, 2014
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
View 5 Replies
View Related
Sep 18, 2007
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Dec 27, 2003
Hello,
I want to make a poll and have to use a foreign key in my database. This is not possible in Web Matrix so I have to use SQL Enterprise Manager. But don't know how. I read the help but can't figure it out. Can someone help me??? Thanks in advance.
Regards,
Roel Alblas
View 5 Replies
View Related
Sep 14, 1998
Hi!
Is it possible in SQL 6.5 to create a Foreign Key on a field that
has a datatype of SMALLINT? I have created FK`s with
other datatypes, but it doesn`t seem to work with this particular
datatype. Is there some trick to this?
Thank you for your assistance!
Toni
View 2 Replies
View Related
Mar 30, 2006
When you set a foreign key to reference a primary key on another table, does the foreign key automatically update? Can anyone explain just how to make a relational table from just the nuts and bolts SQL statements.
View 1 Replies
View Related
Dec 20, 2004
Hi there,
I'm trying to make a few tables in SQL (First time user). The tables have been derived from a logical model based around my assignment...
I'm having problem getting two foreign keys in one table in the SQL. I keep getting the duplicate foreign key error...
I'm not sure where i'm going wrong.....
http://img.photobucket.com/albums/v294/Jertsy/Agh.jpg
there’s a pic of my tables.... There are 4 tables, blog, blogger, blog_entry, and comment.. Blog is only table without a FK.
have a look & thanks for any and all help....
Spencer.
View 1 Replies
View Related
Jun 23, 2007
Hi Friends,Is there any way to get the table name which is referenced by theforeign keyfor example: consider two table "Staff" and "Department"Staff with following columnsPK_IDFK_DepartmentIDNameAddressDepartment with following columnsPK_DepartmentIDDeptNameActually what i need is: Initially i would be having the table name as"Staff"from Staff table i need to identify that the column FK_DepartmentID isa foreign keyand the primary key is in the Department tablei need to traverse from Staff table and identify that FK_DepartmentIDis a primary key in Department tablethis has to be accomplished by sql query.... probably this could befetched fromData Dictionary but i couldnt find the relationship between the systemtables.ThanksArunDhaJ
View 1 Replies
View Related
Jun 5, 2007
I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.
Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language
table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)
This cause an error because the foreign key does not include all part of the primary key.
Rufen
View 3 Replies
View Related
Jan 4, 2008
table1
-------
a1 number(10) PK
b1 varchar2(3)
c1 number(10)
Table2
----------
d1 number(10) PK
b1 varchar2(3)
c1 number(10)
Can I make a Foreign key from Table2(b1,c1) to table1(b1,c1) if b1 in table1 might be null.
When I tried that I got this.
ORA-02270: no matching unique or primary key for this column-list
View 6 Replies
View Related
Apr 13, 2008
Hi everyone,
My main table has a column named "customer" which is a foreign key pointed to my secondary table consisted of a column "customer_id".
I wanted to change my secondary table's customer_id's name into another name whereby deleting it and create a new column instead (i dont know another way of changing names by code) but i'm not allowed to do that due to "customer_is"'s being attached to my main table as FK.
Any suggestion how to do it ?
Thanks a lot.
View 9 Replies
View Related
Aug 10, 2007
i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B
column name u can pretain as C1, C2
View 5 Replies
View Related
Aug 8, 2006
I am deleting a column from a table in code. Before I drop the colum I need to find if the column belongs to a foreign key. I have the names of the foreign keys for the table from sysobjects.How can I determine what columns are part of the foreign key?
View 9 Replies
View Related
Oct 5, 2006
Just installed this the Forum starter Kit and am getting this message when I try to add the first threadI'm a bit of a newbie but not only damp behind the earsThe INSERT statement conflicted with the FOREIGN KEY constraint "FK_MembersForums_MemberInfo". The conflict occurred in database "SQL2005_****_*****01", table "dbo.MemberInfo", column 'memberid'.The statement has been terminated. What do I need to do to the SQL Tables in order to get this to work?
View 3 Replies
View Related
Nov 9, 2006
Hi, i have 3 tables.
Orders: {OrderNr(PK, autonumber), date}
Order_Line: {OrderNr(FK), ItemNr(FK), number}
Items: {ItemNr(PK), Name, ...}
How do I insert a new Order with a new order_line? I get this error: The INSERT statement conflicted with the FOREIGN KEY constraint
I can't figure out how to capture the autogenerated OrderNr.
View 1 Replies
View Related
Apr 17, 2007
Can any body tell me how to know to what columns of other table it refers to.
shiva kumar
View 2 Replies
View Related
Apr 27, 2007
I have a one to many relationship between two tables. The master table is keyed using an int ID field set to IsIdentity yes and this is the primary key. The field name is ChurchID. Likewise the detail table has such a field as it's primary key and is named ContactID.
I carry the ChurchID field in the detail table and would like to make it the foreign key. I could not find this explained in the two books I have and the help was anything but that )perhaps it's just me afterall).
Well, any help in this matter would be greatly appreciated as I find myself at a standstill until this can be resolved.
Thanks in advance.
View 2 Replies
View Related
Jul 3, 2007
When I try to insert a record on my DB (SQL 2005 Express) I get a constraint error. This is my table setup which has been simplified to expose the problem I have:
Categories TABLEint CatId PKvarchar CatName :
Items TABLEint ItemId PKvarchar ItemName :
X_Items_Categoriesint CatIdint ItemId
So basically I have a one-to-many relationship between Items and Categories, in other words each item is associated to one or more categories and this association is done via the X_Items_Categories cross table. On this cross table I set two constraints:
The CatId of each entry in the cross table (X_Items_Categories) must exist in the Category table, and
View 4 Replies
View Related
Nov 30, 2007
I am new beginner for SQL .I have requirement of using foreign key in tables I have created earlier.Can anyone tell me the syntax for the same ?
Please help me, Your suggestions are welcome.
View 6 Replies
View Related
Mar 13, 2008
I'm trying to enter data into a SqlServer 2005 database programatically. The databases are part of an open source program I have. I'm encountering the following error when I try to enter data into a particular table:The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CMRC_ProductImages_CMRC_Products". The conflict occurred in database "hscom", table "dbo.CSK_Store_Product", column 'productID'. The statement has been terminated. I understand what's happening theoretically, but I don't know where to look for where the constraint is defined, or how to fix it. I don't see FK_CMRC_ProductImages_CMRC_Products as a file name or a stored procedure. Diane
View 3 Replies
View Related
Apr 7, 2008
tell me how to add foreign keys in sql 2005 express
View 3 Replies
View Related
Apr 26, 2004
Hi
Can anyone tell me how I can remove a foreign key in a SQL database?
Thanks in advance
View 4 Replies
View Related
Sep 24, 2004
Hi;
I have 2 tables Users and DVDTestResults these tables have a relation over UserID
Users.UserID
DVDTestResults.UserName
both char
But when I try to insert in DVDTestResults I am having an error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DVDTestResults_Users'. The conflict occurred in database 'Test', table 'Users', column 'UserID'. The statement has been terminated.
can you help me why?
thank you....
View 7 Replies
View Related
Aug 12, 2005
I'm using stored procedures to update my tables. If I disable the foreign key constraint all works perfectly. Otherwise I get this error:INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_wansub_invoice'. The conflict occurred in database 'test1', table 'invoice'. The statement has been terminated. I don't understand why I'm getting the error, because when I disable the constraint even the foreign key fields are updated properly and there is no apparent conflict with the linked table. I've included my c# code and the stored procedure below.FYI the foreign keys in question are InvoiceDate, AccountNum, CarrierId, and Cycle. These are the primary keys in the "invoice" table, and foreign keys in the "wansub" table. It is vital that all records in the wansub table are matched with a record in the invoice table, so I have to have this constraint in place. C# code to create parameters and call the sp:
public int updateWanTable(WanSubaccount sub, Invoice invoice)
{
SqlCommand cmdUpdateWan;
int returnvalue = 0;
cmdUpdateWan = new SqlCommand("usp_updatewan", conn);
cmdUpdateWan.CommandType = CommandType.StoredProcedure;
SqlParameter invoicedate = cmdUpdateWan.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime);
invoicedate.Value = DateTime.Parse(invoice.InvoiceDate.Trim());
SqlParameter subaccountnumber = cmdUpdateWan.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50);
subaccountnumber.Value = sub.SubaccountNumber.Trim();
SqlParameter addressa = cmdUpdateWan.Parameters.Add("@AddressA", SqlDbType.VarChar, 50 );
addressa.Value = sub.Address.Trim();
SqlParameter citya = cmdUpdateWan.Parameters.Add("@CityA", SqlDbType.VarChar, 50 );
citya.Value = sub.City.Trim();
SqlParameter statea = cmdUpdateWan.Parameters.Add("@StateA", SqlDbType.VarChar, 50 );
statea.Value = sub.State.Trim();
SqlParameter zipa = cmdUpdateWan.Parameters.Add("@ZipA", SqlDbType.NChar, 10 );
zipa.Value = sub.Zip.Trim();
SqlParameter countrya = cmdUpdateWan.Parameters.Add("@CountryA", SqlDbType.VarChar, 50 );
countrya.Value = sub.Country.Trim();
SqlParameter addressb = cmdUpdateWan.Parameters.Add("@AddressB", SqlDbType.VarChar, 50 );
addressb.Value = sub.AddressB.Trim();
SqlParameter cityb = cmdUpdateWan.Parameters.Add("@CityB", SqlDbType.VarChar, 50 );
cityb.Value = sub.CityB.Trim();
SqlParameter stateb = cmdUpdateWan.Parameters.Add("@StateB", SqlDbType.VarChar, 50 );
stateb.Value = sub.StateB.Trim();
SqlParameter zipb = cmdUpdateWan.Parameters.Add("@ZipB", SqlDbType.NChar, 10 );
zipb.Value = sub.ZipB.Trim();
SqlParameter countryb = cmdUpdateWan.Parameters.Add("@CountryB", SqlDbType.VarChar, 50 );
countryb.Value = sub.CountryB.Trim();
SqlParameter speed = cmdUpdateWan.Parameters.Add("@Speed", SqlDbType.VarChar, 50 );
speed.Value = sub.Speed.Trim();
SqlParameter type = cmdUpdateWan.Parameters.Add("@Type", SqlDbType.VarChar, 50 );
type.Value = sub.Type.Trim();
SqlParameter monthlycharge = cmdUpdateWan.Parameters.Add("@MonthlyCharge", SqlDbType.Money );
monthlycharge.Value = sub.MonthlyServiceFee;
SqlParameter discounts = cmdUpdateWan.Parameters.Add("@Discounts", SqlDbType.Money );
discounts.Value = sub.Discounts;
SqlParameter other = cmdUpdateWan.Parameters.Add("@Other", SqlDbType.Money );
other.Value = sub.Other;
SqlParameter ucc = cmdUpdateWan.Parameters.Add("@UCC", SqlDbType.Money );
ucc.Value = sub.Ucc;
SqlParameter fedtax = cmdUpdateWan.Parameters.Add("@FedTax", SqlDbType.Money );
fedtax.Value = sub.FedTax;
SqlParameter statetax = cmdUpdateWan.Parameters.Add("@StateTax", SqlDbType.Money );
statetax.Value = sub.StateTax;
SqlParameter othertax = cmdUpdateWan.Parameters.Add("@OtherTax", SqlDbType.Money );
othertax.Value = sub.OtherTax;
SqlParameter accountnum = cmdUpdateWan.Parameters.Add("@AccountNum", SqlDbType.VarChar, 50 );
accountnum.Value = invoice.AccountNumber.Trim();
SqlParameter carrierid = cmdUpdateWan.Parameters.Add("@CarrierId", SqlDbType.Int );
carrierid.Value = invoice.CarrierId;
SqlParameter isthirdparty = cmdUpdateWan.Parameters.Add("@IsThirdParty", SqlDbType.Bit );
isthirdparty.Value = sub.IsThirdParty;
SqlParameter thirdparty = cmdUpdateWan.Parameters.Add("@ThirdPartyBillingCompany", SqlDbType.VarChar, 50 );
thirdparty.Value = sub.ThirdPartyBillingCompany.Trim();
SqlParameter var = cmdUpdateWan.Parameters.Add("@Comments", SqlDbType.VarChar, 255 );
var.Value = sub.Comments.Trim();
SqlParameter notes = cmdUpdateWan.Parameters.Add("@Notes", SqlDbType.VarChar, 255 );
notes.Value = sub.Notes.Trim();
SqlParameter cycle = cmdUpdateWan.Parameters.Add("@Cycle", SqlDbType.Int );
cycle.Value = invoice.Cycle;
// try
// {
conn.Open();
// }
// catch{
//
// }
// try{
returnvalue = cmdUpdateWan.ExecuteNonQuery();
// }
// catch(SqlException e){
// returnvalue = 2;
// }
// finally{
conn.Close();
fill(this);
// }
return returnvalue;
}The SP:CREATE PROCEDURE usp_updatewan( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @AddressA varchar(50), @CityA varchar(50), @StateA varchar(50), @ZipA nchar(10), @CountryA varchar(50), @AddressB varchar(50), @CityB varchar(50), @StateB varchar(50), @ZipB nchar(10), @CountryB varchar(50), @Speed varchar(50), @Type varchar(50), @MonthlyCharge money, @Discounts money, @Other money, @UCC money, @FedTax money, @StateTax money, @OtherTax money, @AccountNum varchar(50), @CarrierId int, @IsThirdParty bit, @ThirdPartyBillingCompany varchar(50), @Comments varchar(255), @Notes varchar(255), @Cycle int)
AS
if exists ( select * from wansub where InvoiceDate = @InvoiceDate and SubaccountNumber = @SubaccountNumber and AccountNum = @AccountNum and CarrierId = @CarrierId and Cycle = @Cycle ) Begin --if the record exists update wansub set AddressA = @AddressA, CityA = @CityA, StateA = @StateA, ZipA = @ZipA, CountryA = @CountryA, AddressB = @AddressB, CityB = @CityB, StateB = @StateB, ZipB = @ZipB, CountryB = @CountryB, Speed = @Speed, Type = @Type, MonthlyCharge = @MonthlyCharge, Discounts = @Discounts, Other = @Other, UCC = @UCC, FedTax = @FedTax, StateTax = @StateTax, OtherTax = @OtherTax, IsThirdParty = @IsThirdParty, ThirdPartyBillingCompany = @ThirdPartyBillingCompany, Comments = @Comments, Notes = @Notes EndElse Begin
insert into wansub values (@InvoiceDate, @SubaccountNumber, @AddressA, @CityA, @StateA, @ZipA, @CountryA, @AddressB, @CityB, @StateB, @ZipB, @CountryB, @Speed, @Type, @MonthlyCharge, @Discounts, @Other, @UCC, @FedTax, @StateTax, @OtherTax, @AccountNum, @CarrierId, @IsThirdParty, @ThirdPartyBillingCompany, @Comments, @Notes, @Cycle)
EndGOAny help is greatly appreciated! Especially since I have other tables with this very problem as well . . .
View 4 Replies
View Related
Feb 20, 2006
Hi
I have a one to many relationship between two MS SQL tables.
When inserting a record in the master table (primary key is autoinc) how can I get this value for setting in the foreign key details table?
TIA
View 9 Replies
View Related
Mar 20, 2006
i have 3 table which is merchant, merchantcategory and merchantitem.
merchantcategory's foreign key is refer to merchant_id, and merchantitem's foreign key is refer to merchantcategory_id......
the problem is like this....
i would like to select all the available items from merchantitem, but i only have the information of merchant_id, so what method should i use to get these output ??
i am using the functions which generated by codesmith with wilson ORMapper method.....
i really hope that someone will provide me the solution......thanks
View 2 Replies
View Related
May 17, 2001
Hello Folks,
Well I've hit the wall on this one:
I'm wondering if one can designate a conditional foreign key that relates to one of many different tables depending on the "type" column in the foreign key's. My goal is to come up with some SQL code that will allow for this in a Create table statement. (By the way I'm using MS SQL-7 but I'm wondering if this can be done in general.)
I have two simple cases below that show illustrate what I'm trying to do:
Thanks in advance, -JerryZZ
---------------------------------------------------------
Case 1: The foreign key relation is to a primary key
Table: Invoices
-Fields:
---InvoiceID ..... (primary key)
---BilleeID ...... (fkey to Manfacturers.ManfID IF BilleeType=M)
...................(fkey to Distibutors.DistID IF BilleeType=D)
---BilleeType .....(constraint = M or D)
Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---CompanyName.. (not null)
Table: Distributors
-Fields:
---DistID ........ (primary key)
---CompanyName .. (not null)
---------------------------------------------------------
Case 2: The foreign key relation is to a unique "not null" non-primary key
Table: InvoicesEmail
-Fields:
---InvoiceID ..... (primary key)
---EmailAddress .. (fkey to Manfacturers.EmailAddress IF BilleeType=M)
...................(fkey to Distibutors.EmailAddress IF BilleeType=D)
---BilleeType .....(constraint = M or D)
Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---EmailAddress .. (unique, not null)
Table: Distributors
-Fields:
---DistID ........ (primary key)
---EmailAddress .. (unique, not null)
---------------End-O-Message-------------------------------------
View 2 Replies
View Related
