I have a table (Portfolio) with the following columns:
1)PortfolioID
2)StockID
3)Date
Interestingly enough, I also have a second table (stock) with the following columns:
1) StockID
2) Date
The relationship I am trying to enforce is one in which the Portfolio table is made up of many stocks, and the Stock table rows can be components of many Portfolios. Many-To-Many in that respect (OMG! Am I enforcing an orgy-type relationship? ;) )
My Portfolio PK is all three columns. My Stock PK is both columns. Obviously both tables contain other data, but that isn't necessary to figure stuff out here, I don't think.
My portfolio table defines portfolios that consist of multiple stocks. However, the Stock table will never have a duplicate stockID on any given date. The reasoning here is that a stock's data (the "other stuff" in the stock table) won't change from portfolio to portfolio, since the smallest unit is a stock, and that stock's performance data for a given date will be the same regardless of how many portfolios it may be a part of.
On the other hand, a portfolio MAY consist of multiple instances of the SAME or different stocks.
Many-To-Many
Attempting to add a FK constraint where the primary key table is STOCK, and the FK table is PORTFOLIO was my way of attempting to enforce at the DB level that there is only ONE of a given stock (defined as a distinct StockID and Date pair) for each day, but it can be associated with one-to-many PORTFOLIOs. The FK is StockID and Date.
It all seemed so simple... :( but of course, since I create my portfolio before the STOCK rows associated with it (stock rows are only created as needed, based on the portfolio rows that define the stock list that makes up the portfolio), the FK constraint fails (no primary key row exists when the FK row is created).
It bothers me to remove the constraint, even though I know I must...
How would one go about enforcing this type of relationship? What I really want is the constraint to reverse the PK and FK tables...but the STOCK table's columns are a subset of the PORTFOLIO table's primary key...so I can't do it that way (error due to PORTFOLIO's columns not being THE PK or having a unique constraint (which they cannot have, since the same StockID/Date can exist in more than one PORTFOLIO.
Any thoughts from anyone able to stay awake and interested by this time?
I am currently truncating a table, and then importing the data that I need (Using DTS). The way that I have it setup is I first Truncate my table, then I connect to the Server at our corporate HQ's and import the data that I need.
The problem that I haven't had yet, but may in the future is that, what if the server at HQ is down, or the database that I am getting data from is not available for whatever reason?
I think what I need to do is add a step that first makes sure that the database is available, then go ahead and truncate the table, and finally import the data, but I am not sure how, or what I need to do. Please help.
So I've set up 3 tables for some recursive data verification for anobject link embedding procedure.Basically importing filenames.The three fields in question are identical.Document_link.key2_valuedocument_link_staging.key2_valuedocument_link_storage.key2_valueAnd these three fields are populated from a substring of the filenameswhich are generated in another table.Filenametbl.picknohere is the rub.If I have 100 identical records in the document link tables withkey2_values that are in Filenametbl, and three hundred records inFilenametbl, then this query:select * from Filenametbl where pickno not in (select key2_value fromdocument_link_staging) and pickno not in (select key2_value fromdocument_link_storage)should return 200 records.It returns 0 records.So while this query:select * from Filenametbl where pickno not in (select key2_value fromdocument_link_staging)returns 200 records in this scenario,this query returns 0:select * from Filenametbl where pickno not in (select key2_value fromdocument_link_storage)I am trying to figure out why that is, as the casting for thekey2_values is exactly the same (varchar(255))Can anybody tell me how to remedy this sort of thing, as its beenbugging me for about 2 months.I've been able to work around it, but what it is... is just terriblyineffiecient.
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.
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?
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.
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
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.
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.
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
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?
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?
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.
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.
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
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.
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
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.
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;
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
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
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)
--------------------------------------------------------- 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)
For some reason I have to drop a foreign key in the table and recreate them again. for example, I droped titleauthor table's foreign key in the PUBS database, then I want to recreate them again, but I got error massage. I used below script:
alter table titleauthor drop CONSTRAINT FK__titleauth__au_id__164452B1 ==== alter table titleauthor add constraint FK__titleauth__au_id__164452B1 foreign key (au_id) references authors(au_id)
**************error message************* Server: Msg 547, Level 16, State 1, Line 1 ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK__titleauth__au_id__164452B1'. The conflict occurred in database 'pubs', table 'authors', column 'au_id'. ****************************************** Anybody can please tell me where is wrong?
student --- enrollment is 1- to - many . ssn is pk in student. ssn and courseid is pk in enrollment. later I added the foreign key constraint FK_SSN in enrollment table reference student table. it is ok. enrollment --- lessonhistory is 1-to -many, ssn, courseid and lessonid is pk in lessonhistory . I tried to set FK_SSN foreign key constraint in lessonhistory table reference enrollment table, it always show error message " no primary key in referewnce talbe enrollment " I don't know how to fix it. could you help me out, thanks!!
Could someone enlighten me as to the advantage of using the foreign key tab when in table design mode in the Enterprise Manager. Does it have any advantages ?? Is it necessary ??
I haven't tried, but does anyone know if its possible to a have a foreign key for two tables when the tables reside in different databases (on the same server)?
For some reason I have to drop a foreign key in the table and recreate them again. for example, I droped titleauthor table's foreign key in the PUBS database, then I want to recreate them again, but I got error massage. I used below script:
alter table titleauthor drop CONSTRAINT FK__titleauth__au_id__164452B1 ==== alter table titleauthor add constraint FK__titleauth__au_id__164452B1 foreign key (au_id) references authors(au_id)
**************error message************* Server: Msg 547, Level 16, State 1, Line 1 ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK__titleauth__au_id__164452B1'. The conflict occurred in database 'pubs', table 'authors', column 'au_id'. ****************************************** Anybody can please tell me where is wrong?
