I got a wierd problem which I haven't been able to explain.
I am working on MS SQL 2000. I don't know for what reason, the
relationship between Parent/Child table is getting deleted. When I
open up the ER diagram in MSSQL Enterprise Manager, I see the
relationship line come up for just a split of a second, and disappears
afterwards. This is the second time I am seeing this thing.
I realized it when I accidently deleted the rows in the Parent Table.
They should not have been deleted as I had associated records in the
Child Table (because of Key Constraints).
Has anyone ever come across this situation? Do you have any
suggestions?
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?
I have an asset schema problem which I'm not too sure how to resolve....
I have an asset table which stores values common to all asset types. I have created seperate asset type tables(VehicleAssetDetail, PropertyAssetDetail, InvestmentAssetDetail) to store their specific details.
This in turn creates 1:1 relationships between "Asset" table and the respective detail tables.
If I were to store all the details in the "Asset" table it would cause a lot of redundancy bearing in my mind there are about four other asset type details I have excluded from the schema.
Are the 1:1 relationships bad? If they are, how do I resolve them without throwing all the columns from the asset details tables into the Asset table?
Attached is a 15Kb gif image of the schema. I'd really appreciate any help with this... :) Thanks.
While in the design window of a table, I click the properties button and click the Relationships tab. Here everything is grayed out, and I can't drag and drop to create relationships in my Diagram either. Its like things are locked up right now. Can any of you give me some direction? thanks
Hi, I'm slightly familiar with using access to do databasing, but I am now working with sql server and visual studio 2008 programming in vb. This is probably very simple, but I am trying to call a table from a dataset into a datagrid, but the information in the table needs to be relative. This is why I need to create a relationship, however I am unsure how to. I'm not sure if this is done within the sql server or within vs2008 in vb? The tables I am trying to relate are Financial and Demographics with the PK and FK being Patient ID. Can someone please point me to an example or give me a simple one that will explain how I can do this?
I'm using SQL Express with the Management Studio. I create a database diagram, and add a relationship. The relationship is shown in the diagram, but if I right-click it, I only get the option to delete it, I can't see it's properties. When I go thru the nodes for all the objects in the Object Explorer where I think I should be able to find this relationship, I can't find it??? I look in Triggers/Constraints both for the database as a whole and for the individual participating tables but I can't see the relationship anywhere. What am I doing wrong? Where should I be looking?
I have created the following tables in SQL 2005. English(engcode,engword) Esperanto(espcode,espword) french(frcode,frword) eng-esp(engcode,espcode) esp-fr(espcode[/u],frcode[/u]) The underline code are primary keys. and i need to create a relationship between them. The problem is that the icon "Relationship" is disable.. I have searched a lot how enable this function but in vain.. Any1 have the solution???
Simple question here.. just wondering what the established best-performance approach is to setting up indexes to reflect one-to-many relationships between tables. To take the classic Authors and Books example - many books to 1 author - I want to search my Books table by Author, then by Title. So my query would be:
SELECT a.LastName, a.Initial, b.Title FROM Books b INNER JOIN Authors a ON b.AuthorID = a.AuthorID ORDER BY a.LastName, a.Initial, b.Title
As you can see I want to sort by author names, then by book titles. The way I would currently index the Books table is as follows:
Primary Key: BookID (Incremented Identity) Secondary Key: AuthorID, Title
So is that best-practice for efficient JOIN queries? Or should I have 2 *separate* indexes, one just for AuthorID (the foreign key), and the other just for book Title? I've often wondered this because, for each possible JOIN query, my indexes include AuthorID as the first field. For example, a query by Author then by books' PublishDate, would require an index containing AuthorID and PublishDate. I've often wondered if I can get away with a single index for AuthorID and the other indexes only including the other fields I want to sort/find by.
Hope I've explained it well enough.. and hope I have the theory right. Thanks for any advice!
Can anyone tell me what is best practices to define relationships betweentables and to define their primary key?For example...One table is Orders and other is OrderDetails.For ease of accessing datas from table OrderDetails I have fields copiedfrom Orders like year_of_order, order_number, client_id.Now, is it better to link those tables on this three fields, because theyare already there or define new field in OrderDetails with name OrderID andlink it on ID field from table Orders?In this case I will always access table Orders to check year of order,order number or client id for records in OrderDetails.Which one of these examples have better performace on server?I'm also doubting is it good to define field Id in each table and use itfor relationships to other tables or link tables on fields likeNumber_of_order.Is it enough to define field "Id" set it as PK and Identity Seed 1 forunique mark of record and preserving consistency of data.Is it good practice to link tables on primary key from one table to othercolumn in other table?Thnks.
