I've done my database studies and know about normalizing, relationships, primary keys and so forth. However, when it comes to actual developing of web sites, I've always skipped setting up relationships, and at times, the primary and (especially) foreign keys.
Now I'm designing my first db in Asp.Net 2, and the thought struck me - Why should I do this?
I guess the reason for setting up relationships is that the website should not get any trouble if rules are violated or poorly written, that is, data is input in an Orders table but not the Oredered_products table or the like. The only reason I can think of for using primary keys (when not defining relationships anyway) is to get the Insert, Delete, and Update statements of GridViews etc to work.
Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.
Please enlighten me, someone! (Or let's have an interesting discussion on this topic - if there is something to discuss, that is!)
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?
I received the error below upon saving when I was trying to create a foreign key in a table using relationships in "table designer" of sql server 2005 management studio. This process has been successful for other tables. What might be the problem?
Thanks
Code Snippet
- Unable to create relationship 'FK_SAMPLE_GRID'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_GRID". The conflict occurred in database "acquire", table "dbo.GRID", column 'GRIDNAME'.
I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
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?
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
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 have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Ok, so I open up a book on beginning sql and it discusses relationships.There is something that appears wrong. I am not understanding how the book is describing the one to one relationship. In it's figure, it shows that the same agency can have two different artist id's. But according to it's definition of one to one list can be related from both tables.Can someone explain this further please?Thanks in advance.
How do you show many to many relationships in ASP.NET with datasources? I can retrieve the information from the database easily but I can't manage to save that information into a variable so that i can then call for only information in the M2M table related to the ID Key I want. For example..1 <asp:Label ID="lblTestLabel" Text='<%# DataBinder.Eval(Container.DataItem, "title_id") %>' Runat="server" /> 2 3 <script language="VB" runat="server"> 4 sub Page_Load(sender as Object, e as EventArgs) 5 Dim lblTitleID As Label = Repeater1.FindControl("lblTestLabel") 6 Response.Write(lblTitleID.text) 7 End Sub 8 </script>
When I try to run this script it dosn't work and I get the error: System.NullReferenceException: Object reference not set to an instance of an object. For line 6 of the code above. Is there a way to just retrieve information from a datasource without storing information into a label first? PS: I use sqldatasource tags to retrieve information like:1 <asp:SqlDataSource ID="DataSet1" runat="server" 2 ConnectionString="Driver={MySQL ODBC 3.51 Driver};server=localhost;database=;user=;pwd=;option=3;" 3 ProviderName="System.Data.Odbc" 4 SelectCommand="Select * from titles order by title_id"> 5 </asp:SqlDataSource>
Can someone help me sort something out. Suppose we have two tables in a database.One is named Person, and one is named Birthday. Is this a many to many relationshipor a 1 to many relationship? Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life. birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.
Is there any easy way of getting all the relationships for the tables in aSQL server db? I've looked through Google and the Books online but I'm barely scratching the surface on the systables. I know I can go to each individual table and get the dependencies or even print out the diagram through Enterprise Manager but I really don't want to have to type out all the primary/foreign keys for 100+ tables. Any help on getting this data either through a 3rd party software or querying the systables would be great.
We have a table with approximately 150 colums and 2/3 of them are bit datatypes. We do not expect much activity on this table. Does it make sense to separate it into a few tables instead of 1 with one-to-one relationships? Will the join(s) significantly slow down our web application? Thank you
My hosting servers have "myLittleAdmin" installed. I am new to ms sql I have some experience with Access but I have never set up Relationships inside the database server. I have always just relied on my application logic to call the relations. What is the benefit to setting up relationships in sql, if any?
Hey, I have SQL Sever Enterpise Manager. I am new at this company and I need to know the relationships between ALL the tables. Is there anywhere I can go to view all the primary keys and the relationships?
Is it possible to have 2 different databases and create relationships inbetween the two? I am thinking about way down the road and the size of the databases and I believe that I need to split my data into multiple databases to keep from running into the size limitations.
Ok, so I open up a book on beginning sql and it discusses relationships. There is something that appears wrong. I am not understanding how the book is describing the one to one relationship. In it's figure, it shows that the same agency can have two different artist id's. But according to it's definition of one to one list can be related from both tables.
I have a really small database in terms of number of objects, but very big in terms of data (possibly millions of records). I always try to design my databases to be in the 3rd normal form (at least I hope they are!), but sometimes (for databases consisting of just a few tables) maintaining M:N relationships looks like an over-bloated approach to me... Let's see an actual example. I have a table like this:
Code SnippetCreate table [Airfares] ( [OriginCity] Char(3) NOT NULL, [DestCity] Char(3) NOT NULL, [FareDate] Datetime Default getdate() NOT NULL, [Price] Decimal(9,2) Default 0 NOT NULL, [PriceChange] Decimal(9,2) Default 0 NOT NULL, [Wrong] Bit Default 0 NOT NULL, Primary Key ([OriginCity],[DestCity],[FareDate]) ) go
Now, at first there also was an Airline nvarchar(50) field, which stored the airline name. But now the client requested to store fares for a few airlines if it's the same price and city pair. This way we can have 3 airlines offering the same price on the same itinerary for example. So I added a dictionary table called Airlines:
Code SnippetCreate table [Airlines] ( [AirlineCode] Char(2) NOT NULL, [Name] Nvarchar(50) NOT NULL, Primary Key ([AirlineCode]) ) go
Now, there are two things I can do. We are obviously dealing with the M:N relationship type here. I can either add another nvarchar column in the first table, like Airlines nvarchar(255) and store something like 'AA,AB,AC' there - those would be airline codes. OR I could do it the proper way and create a cross-reference table which will connect both Airfares and Airlines tables (this will store as many records as there are airlines for that particular itinerary, offerring the same price):
Code SnippetCreate table [AirfareAirlines] ( [AirlineCode] Char(2) NOT NULL, [OriginCity] Char(3) NOT NULL, [DestCity] Char(3) NOT NULL, [FareDate] Datetime Default getdate() NOT NULL, Primary Key ([AirlineCode],[OriginCity],[DestCity],[FareDate]) ) go
But this is starting to look overly complicated, isn't it? The pros of this are I'm going to get real relationships, referential integrity, etc., but cons are more difficult code to query all this. What would you do? Do we really need referential integrity everywhere? Are there times when we're better not using relationships? I have seen many programmers skip referential integrity before, which always made me think they are missing something important in a Relational Database Management System.
Ok - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.
Hi I am using the Web Matrix Project to write a .NET application. I have set up a database using MSDE which I accessed through the Web Matrix Project to start with. I have now found the Web Data Administrator which I use to backup my database and help to transfer it to the Server where I hope to run this application (if I ever get it finished!).
I need to set up some relationships between database tables but cannot see a way to do this. SQL Server Books on Line referes to Enterprise Manager which I believe ships with the full MS SQL Server package (and costs a package). I am doing this on a shoe-string. Any ideas on how to do it?
Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer.. I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.
EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.
I'm upgrading my sites forum to a newer version. I've tried, unsuccessfully so far, to transfer member details, threads and posts to the new format. I say unsuccessfully but I have made it work once by deleting the Index's and Relationships on tables and re-entering them after transfer but his ended up screwing with the regestration of new members. Before I become more frustrated than I already am, could I run something by you guys with the experience you have.
If I delete the Rels and Indx's again, then move all my tables over and then run this code through my browser, will the Indexs and Relationships find their way back to the right place?
Part 1
Code:
strSQL = "CREATE INDEX [Ban_ID] ON [" & strDBO & "].[" & strDbTable & "BanList]([Ban_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE UNIQUE INDEX [Cat_ID] ON [" & strDBO & "].[" & strDbTable & "Category]([Cat_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Group_ID] ON [" & strDBO & "].[" & strDbTable & "Group]([Group_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Poll_ID] ON [" & strDBO & "].[" & strDbTable & "Poll]([Poll_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Author_ID] ON [" & strDBO & "].[" & strDbTable & "Author]([Author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Group_ID] ON [" & strDBO & "].[" & strDbTable & "Author]([Group_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Group" & strDbTable & "Author] ON [" & strDBO & "].[" & strDbTable & "Author]([Group_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE UNIQUE INDEX [User_code] ON [" & strDBO & "].[" & strDbTable & "Author]([User_code]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE UNIQUE INDEX [Username] ON [" & strDBO & "].[" & strDbTable & "Author]([Username]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Cat_ID] ON [" & strDBO & "].[" & strDbTable & "Forum]([Cat_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Sub_ID] ON [" & strDBO & "].[" & strDbTable & "Forum]([Sub_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Last_post_author_ID] ON [" & strDBO & "].[" & strDbTable & "Forum]([Last_post_author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Categories" & strDbTable & "Forum] ON [" & strDBO & "].[" & strDbTable & "Forum]([Cat_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Choice_ID] ON [" & strDBO & "].[" & strDbTable & "PollChoice]([Choice_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Poll_ID] ON [" & strDBO & "].[" & strDbTable & "PollChoice]([Poll_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Polls" & strDbTable & "PollChoice] ON [" & strDBO & "].[" & strDbTable & "PollChoice]([Poll_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Address_ID] ON [" & strDBO & "].[" & strDbTable & "BuddyList]([Address_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Author_ID] ON [" & strDBO & "].[" & strDbTable & "BuddyList]([Buddy_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Buddy_ID] ON [" & strDBO & "].[" & strDbTable & "BuddyList]([Author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Author" & strDbTable & "BuddyList] ON [" & strDBO & "].[" & strDbTable & "BuddyList]([Buddy_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Author_ID] ON [" & strDBO & "].[" & strDbTable & "EmailNotify]([Author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Forum_ID] ON [" & strDBO & "].[" & strDbTable & "EmailNotify]([Forum_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Author" & strDbTable & "TopicWatch] ON [" & strDBO & "].[" & strDbTable & "EmailNotify]([Author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Toipc_ID] ON [" & strDBO & "].[" & strDbTable & "EmailNotify]([Topic_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Watch_ID] ON [" & strDBO & "].[" & strDbTable & "EmailNotify]([Watch_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Auhor_ID] ON [" & strDBO & "].[" & strDbTable & "PMMessage]([Author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [From_ID] ON [" & strDBO & "].[" & strDbTable & "PMMessage]([From_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Message_ID] ON [" & strDBO & "].[" & strDbTable & "PMMessage]([PM_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Author" & strDbTable & "PMMessage] ON [" & strDBO & "].[" & strDbTable & "PMMessage]([From_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Forum_ID] ON [" & strDBO & "].[" & strDbTable & "Permissions]([Forum_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Forum" & strDbTable & "Permissions] ON [" & strDBO & "].[" & strDbTable & "Permissions]([Forum_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Group_ID] ON [" & strDBO & "].[" & strDbTable & "Permissions]([Group_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Forum_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Forum_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Poll_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Poll_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Start_Thread_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Start_Thread_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Last_Thread_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Last_Thread_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Moved_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Moved_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Forum" & strDbTable & "Topic] ON [" & strDBO & "].[" & strDbTable & "Topic]([Forum_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Topic_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Topic_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Message_date] ON [" & strDBO & "].[" & strDbTable & "Thread]([Message_date]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Message_ID] ON [" & strDBO & "].[" & strDbTable & "Thread]([Thread_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Author" & strDbTable & "Thread] ON [" & strDBO & "].[" & strDbTable & "Thread]([Author_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Topic" & strDbTable & "Thread] ON [" & strDBO & "].[" & strDbTable & "Thread]([Topic_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Topic_ID] ON [" & strDBO & "].[" & strDbTable & "Thread]([Topic_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Guest_ID] ON [" & strDBO & "].[" & strDbTable & "GuestName]([Guest_ID]) ON [PRIMARY] "
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [" & strDbTable & "Thread" & strDbTable & "GuestName] ON [" & strDBO & "].[" & strDbTable & "GuestName]([Thread_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE INDEX [Thread_ID] ON [" & strDBO & "].[" & strDbTable & "GuestName]([Thread_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
strSQL = "CREATE UNIQUE INDEX [Session_ID] ON [" & strDBO & "].[" & strDbTable & "Session]([Session_ID]) ON [PRIMARY]"
'Write to the database adoCon.Execute(strSQL)
'If an error has occured write an error to the page If Err.Number <> 0 Then Response.Write("<br />Error Creating one or more Indexs <br />" & Err.description & "<br /><br />")
'Reset error object Err.Number = 0
'Set the error boolean to True blnErrorOccured = True End If
Is there a way to create a supertype/subtype table in sql server 2000 or is supertype/subtype an abstract idea rather than an option on creating a table?
i would like to set up a supertype table:
person personid firstname lastname
and two subtype tables:
employee id personId ss#
customer id personId someOtherInfo
how do i set this up so that I cant have the same personid in employee and customer. How do i make sure that the personid is in either one or the other.
I am starting a new project and somebody told me at work that if I create relationships (foreign keys) between my tables, it makes the database slower, the best way is to "link" the tables using sql instructions like inner/outer join. Is that true?
