My Table That Uses Aspnet Foreign Keys Won't Display Anything - CAUTION - Noob At Work
Apr 12, 2007
I've created a new table in ASPNETDB.mdf called 'customerInfo'. Most of the fields are, for the moment, data that exists in one of the existing ASPNET tables eg Membership_UserId (primary key), Membership_Email, Users_UserName etc.; the list goes on. I have put a new column 'amountspent' - just to test that I can do what I think I can do, however the table doesn't display any data. Am I OK in thinking that I can use the aspnet data in my own tables and reference through foreign keys to them. I've made sure that the procedure that I used to put the table into a gridview on the webpage is OK as I did the same thing to the membership table and that displays all of its data. Can someone either explain the steps I need to make this happen correctly or point me in the direction of a noob type walkthrough.
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
There is itemlookup table, which stores item number and itemdescription. Also there is a child table, pricehistory, of theitemlookup table, which stores different prices and different dateranges. So it is one-to-many relationship. (Price can be stored morethan one with a different date range)And there is another table RequestItem that stores the foreign key ofthe itemlookup table to show the information of the itemlookup table.Then how do I know later which date range of the price was stored inthe RequestItem table? Since I only keep the foreign key of theitemlookup table, it will be impossible to keep track of the row of thepricehistory table if there are more than one data existed in thepricehistory table.Will it be a valid table structure to create a column for the foreignkey of the pricehistory in RequestItem table or any other ways tohandle this issue?
I have two tables as below. I want to update a quote and change the item for which it is for. So I want to do an update statement to change the cat_ref that the quote is for. However, when I do this I get a foreign key conflict with cat_ref in the item table. How do I get around this? Thanks
I have a very elementary question on table design. This screenshot (which is part of a larger diagram) sets the context of the question.
There are two distinct groups of products with different attributes, hence two separate tables: Products_A and Products_B. There's a third table that keeps track of daily production (DailyProduction), which must accept items from either product table. Now, my question is: How do I relate these two tables to "DailyProduction" ? If I place a double-constraint with two foreign keys, then I get an error whenever I attempt to insert a record in DailyProduction. For example, if the new item is of "Products_A" type, then the error message from SSMS is:
No row was updated.
The data in row 1 was not committed. Error Source: .Net SqlClient Data Provider. Error Message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DailyProduction_Products_B". The conflict occurred in database "MyDatabase", table "dbo.Products_B", column 'ProductB_ID'. The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
It's clear that the error message makes sense as SQL is trying to apply BOTH constraints. It's one AND the other, not one OR the other. Now, how do I remedy this ? What would be the most appropriate way to relate the three tables ?
i'm using sql express, management studio express and a visual web developer starter kit. i have 4 tables: items; categories; categorization; old_items the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys. i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self. i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them. can anyone help? i've been trying all the permutations of queries i can think of and not getting it. thanks.
Kinda new to SQL, using SQL Server 2005.I have some foreign keys in a couple of tables. I need to drop thesetables, but can't since I'll get the error:Msg 3726,Level 16, State 1, Line 1Could not drop object 'Client' because it is referenced by a FOREIGNKEY constraint.I'm not sure how to disable or get rid of these foreign keys so that Ican drop my tables. I tried:ALTER TABLE Client NOCHECK CONSTRAINT ALLAlter Table Client Drop Column Foreign Key Boss_ID;I went into the Object Explorer and deleted the FK lines from eachtable, but still the same error.What am I doing wrong?Thanks for your help.
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?
CREATE TABLE StockNames ( -- Added Primary key to [stock_symbol] [stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY, [stock_name] VARCHAR(150) NOT NULL, [stock_exchange] VARCHAR(50) NOT NULL,
Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.
An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.
If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.
I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?
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?
Hello,I have 2 tables:- Customers with columns:customerID(prim_key),customerName(with customer's names)- Deliveries with columns:deliveryID(primKey),sender(ref_key to CustomerID from Customers),receiver(also ref_key to CustomerID from Customers);I need to select all data about deliveries, but instead of havingsender's ID and receiver's ID, I need to have their Names.I tried to do:SELECTdeliveries.deliveryID,Customers.customerName AS sender,Customers.customerName AS receiverFROMcustomers, deliveriesWHERECustomers.customerID=Deliveries.sender ANDCustomers.customerID=Deliveries.receiver;But this only works if sender=receiver, which is obvious ;)I'd like to know if there is any other way for obtaining those datawithin one queryThank you very much for your helpChris
I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.
I have a table called Patient. It's primary key is pat_id.
I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.
I have a third table called Coverage_History. It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.
I want to get the pat_id and all the coverage information that is current. The coverage table contains specific insurance policy information. The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:
Patient (pat_id and lname) P123 Monto P124 Minto P125 Dento P126 Donto
Coverage (pat_id, coverage_plan_id, hosp_status, policy_num) P123 MED1 OP A1499 P123 ACT4 OP H39B P124 MED1 OP C90009 P124 RAC OP 99KKKK P124 RAC OP 99KKKK P124 MED1 OP C90009 P125 ARP OP G190 P126 BCB OP H88
Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to) P123 OP 1 20150102 MED1 NULL P123 OP 2 20150102 ACT4 NULL P124 OP 1 20150203 RAC 20150430 P124 OP 2 20150203 MED1 20150430 P124 OP 1 20150501 MED1 NULL P124 OP 2 20150501 RAC NULL P125 OP 1 20150801 ARP NULL P126 OP 1 20150801 BCB 20160101
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority, from patient p left join ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and (ch.effective_to is NULL or ch.effective_to >= getdate() ) ) on ch.patient_id = p.patient_id
where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
So I want to see:
P123 Monto MED1 OP 20150102 NULL 1 P123 Monto ACT4 OP 20150102 NULL 2 P124 Minto MED1 OP 20150501 NULL 1 P124 Minto RAC OP 20150501 NULL 2 P125 Dento ARP OP 20150801 NULL 1 P126 Donto BCB OP 20150801 20160101 1
I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.
I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.
CREATE TABLE dbo.CodesTable ( PartyGrpId INT NOT NULL , CdTyp VARCHAR ( 8 ) NOT NULL , CompId INT NOT NULL , CdValue VARCHAR ( 8 ) NOT NULL , CdValueDesc VARCHAR ( 255 ) NULL , AltValueDesc VARCHAR ( 100 ) NULL
[Code] ....
I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.
ALTER TABLE dbo.BusinessStatus WITH NOCHECK ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ======================================= -- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ========================================= -- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables?
I'm wondering how/why this query works. Trying to get my head wrappedaround SQL. Basically the Query deletes from the Import table allrecords that are already in FooStrings so that when I do an insert fromthe FooStringsImport table into the FooStrings table, then I won't getprimary key violations.DELETE FROM FooStringsImportWHERE EXISTS(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)It seems to work fine, but I'm wondering about how the EXISTS keywordworks.(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)This part is going to return only records from FooStrings correct? Ordoes it do a cartesian product since I've specified more than one tablein the WHERE statement?I wonder if it only returns records in FooStrings, then I don't see howa record from FooStringsImport would "EXISTS" in the records returnedfrom FooStrings.The reason I wondered about the cartesian product is because, if onlyFooStrings is specified in the FROM part of the SELECT statement, thenI was thinking it is only going to return FooString records. Theserecords would then be returned by the select statement to the WHEREEXISTS, which would look for FooStringImport records, but would findnone because the select statement only returned FooString records.I'm guessing maybe because it has to do a cartesian product to evaluatethe WHERE Pkey's equal, then the "SELECT *" just goes ahead and getsALL the fields, and not just those in FooStrings.FooStrings and FooStringsImport are identically structured tables,where the FooKey is set as the primary key in each table:CREATE TABLE [dbo].[FooStrings] ([FooKey] [bigint] NOT NULL ,[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StartDate] [datetime] NULL ,[EndDate] [datetime] NULL ,[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks in advance. I'm so appreciative of the help I've gotten here,as I've been able to write several very useful queries on my own nowafter everyones help and plus lots of reading on my own.
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)?
l'm trying to do inserts on tables with foreign keys and they keep crashing. Can somebody please help.Whats the best way of populating data that has foreign keys?
I need to know if this is required? I have products, with the help of their business account numbers, are naturally categorized numerically. I want to create a product category table and a product account table.
I am creating a series of foreign keys in my new database, and so far everything is going fine. The company I work for never uses them, and I am working on my own stuff on my own time now.
I come across one table that I do want one field to allow zeroes, but when its set to a value, to exist in the other table.
To be more specific. I have a PO type table, which has an EmployeeID of the person who made the PO, and another field to store who received the PO when the order arrives. I want that 2nd Employee field to be zero until it's received.
I created an employee of zero, to allow the foreign key to be created. But all the other tables that have foreign keys to my Employee table I would prefer to not allow zeroes. So I changed them to use a check of (employeeid > 0).
Is it possible to have a foreign key say that I want the value from the Field in Table A to exist in table B, or to be zero? Or would it just be easier to leave off the foreign key in this one case?
Shiry writes "Hi, I'm a beginner and I'm a bit stuck here..
I'm creating this database for my homework, I'm using Marks & Spencer as an example. It has a table, products, for the clothes
id name cat_id ....... connected to typ_cat colour_id ...... connected to typ_colour size_id ....... connected to typ_size price sup_id ....... connected to typ_sup ...... but here i'm stuck a bit, are you allowed to connect a typ_table to another typ_table?
this is the typ_sup: id name address city_id ......... connected to typ_city
or rather have a separate sup table with the same id, name, address, city_id id will be connected to typ_sup and the products table.. sup_id will then be connected to typ_sup..?
What way is better? and is it allowed to connect a typ_table to another typ_table?
Now I want to have a one to many relationship between UserInfo and HairInfo. I want to specify Hairid as the foreign key in the HairInfo table. Here UserInfo is the parent and HairInfo is the child. I am using SQL server 2000. Is there a way to do it using the Enterprise manager interface. Can someone run me through the steps to do it.
Is there a way to temporaly set off foreign keys dependencies (like a sql command or something...) and then set them on again? I've to migrate 90 tables on my app, but i get the "dependencies error", any ideas?
having been used to creating databases for the past nine years in MS Access, i have been recently getting used to the basics of SQL Server 2005 express edition. i know pretty much all of the simple basics, but i have hit some trouble.
in MS Access, when i have used the "Design View" to develop all of my tables, fields and validation rules, i have used their "Lookup Wizard" to develop ways of assigning a record to a certain value - example;
CLUB: Club_Id (Primary Key) Club
PLAYER: Player_Id Player_Name Club (Foreign Key)
the flaw, i suppose, is the ease with which i can create a relationship between TWO tables without the child tables foreign key having to be an ID (integer) and users can select a "Club" of their choice to assign a "Player" to in the PLAYER table. i.e.
CLUB: Club_Id: 1 Club: Man Utd
PLAYER: Player_Id: 1 Player_Name: Ryan Giggs Club: Man Utd (FKEY)
instead of;
PLAYER: Player_Id: 1 Player_Name: Ryan Giggs Club: 1 (FKEY)
i hope i am making sense. now the awkward bit as far as i'm concerned:
in SQL Server 2005 Management Studio Express, i cannot do this once i do the necessary tables, content and relational diagrams.
so i have this;
PLAYER: Player_Id: 1 Player_Name: Ryan Giggs Club: 1 (FKEY)
instead of what i want, which is this;
PLAYER: Player_Id: 1 Player_Name: Ryan Giggs Club: Man Utd (FKEY)
how can this be achievable using Studio Management Express 2005 for someone like myself who is still something of an SQL novice (of a sort)
Right now i'm building a language centre DB. Is going to hold translations for data in tables in another DB (english DB). The idea is going to be that there is going to be a table in the Language DB for every language and table it is going to translate in the english DB. So lets consider the following in the English DB: PROJ_TBL_HELPTOPICS -> PK_HELP_ID -> TITLE -> DESCR PROJ_TBL_CATEGORIES -> PK_CAT_ID -> TITLE -> DESCR
In the Language DB I want to hold translations for HELPPTOPICS and CATEGORIES, and I want translations for Spanish and Japanese. PROJ_TBL_HELPTOPICS_ES -> PK_TRANS_ID -> FK_HELP_ID -> TRANS_TITLE -> TRANS_DESCR
The rest is going to be the layout as abovePROJ_TBL_HELPTOPICS_JA PROJ_TBL_CATEGORIES_ESPROJ_TBL_CATEGORIES_JA The reasons I separated up the language DB from the english DB are: 1. English DB has, and is going to have a lot more tables, and is going to be heavily queried (plus I think dont think the translations are going to be used anywhere near as often and the english). I figured the less tables, where possible, the better. 2. Putting translations in different a different DB, I could take better advantage of colliations specific to a language for example when using Full-Text searching on Japanese text
Anyways, here's my question!?! I want to link the foreign key column to the table it is translating primary key column (in the English DB). I want to be able to take advantage of Cascade on Delete. So when an item is deleted from EnglishDB.PROJ_HELP_TOPICS it is going to be deleted from LanguageDB.PROJ_HELP_TOPICS_[LANG ISO]. Is this done through Mirroring?
hi. How to update FormA table from customer table. Let say i wish to keep small number of fields from each table so i use foreign keys as reference. However i had a problem when i tried to save the relationships of both tables, i receive the error that FormA_id is not able to insert null into value. Cust_id(PK) is identify column, as well FormA_id(FK) and FormA_id(PK) too. For example, when i insert a record from customer table, it will automatically create id for FormA. Table structure. Customer cust_id(PK),name,age,formA_id(FK) Table structure, FormA formA_id(PK), info, date, How to solve ?
