I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys).
How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
I'm having trouble defining a relationship in SQL Express with a concatenated foreign key.
I have 3 tables. Date, Company and Detail
Date has a PK1
Company has a PK2
Detail has a concatenated foreign Key of PK1, PK2
When I go to set the foreign key relationship in Management Studio 'Tables and Columns', it seems I can only select 1 primary key table on the left for my 2 foreign keys. How can I add 2 primary key tables to point to each FK in my detail table?
I working a tutorial where I will learn a lot of new stuff, LINQ being one of them. As a part of this tutorial, there are four tables and foreign keys. My db skills were limited to simple tables and i've nevr done anything where I related tables before. I'm a bit confused about foreign keys and how to set them up. I think I'm setting up right, but I have a doubt. I have a couple of screen caps to show you what where my doubt comes from at: http://www.noelakins.com/keys.htmlWhat is causing my confusion is that the connecting lines between the Albums and Events table diagrams in my setup seems to point back to the Photos table, and the Photos table seems to point to the Tags table. From the tutorial, the connecting lines seem to be pointing the other direction. Am I setting up the key right, or do I have them backwards? I'm setting out to learn a lot of stuff in this tutorial, so when I mess it up, i would like to know that it isn't because the foreign keys are wrong.EDIT:After doing some googling and some reading and watching, I'm a little less confused about how to set up foreign keys, at least how to do it in VWD. My problem now seems to be understanding the relationships. In the tutorial example, the Tags table has a FK on the PhotoID field, which matches the primary key (PhotoID) in the Photos table. Now, does this mean that the Tags table is the parent table and the Photos table the child? If this is true, then the other two tables must be child tables of the Photos table. At least that how I think this should work. The issue that I'm having with this is trying to understand how a cascade delete would work. Is the foreign key relationship a one way or both way relationship? For example, if I have made a foreign key on the AlbumID field of the Photos table, where the primary key table is Albums, PK is AlbumID and the foreign key table is Photos, then, if I do a cascade delete of a particular photo, does it delete the album too? That wouldn't make sense. Foreign keys and cascade deletes must be one way, right? Thanks
I'm using SQL Server 2005 Management Studio. I've created several Foreign Key Relationships using Database Diagrams. Now I'm trying to edit the constraints on the relationships, but when I right click on the relationship, the only option available is "Delete Relationships from Database". But I am not able to edit the relationship. Does anyone know how to do this?
I have 2 tables Publication and Advertisment_Codes. I want to update the publication_code in both tables where country_id=1 and publication_code='A'. However, it gives me an error because of foreign key relationship. I tried first updating in Advertisment_Codes and then in Publication table but it didn't work. I tried the other way round but also it didn't work. How can I update the publication code? Thanks
GO ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Countries] FOREIGN KEY([Country_id]) REFERENCES [dbo].[Countries] ([Country_id]) GO ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Countries] GO ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Publications] FOREIGN KEY([Country_id], [Publication_code]) REFERENCES [dbo].[Publications] ([Country_id], [Publication_code]) GO ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Publications]
GO ALTER TABLE [dbo].[Publications] WITH CHECK ADD CONSTRAINT [FK_Publications_Countries] FOREIGN KEY([Country_id]) REFERENCES [dbo].[Countries] ([Country_id]) GO ALTER TABLE [dbo].[Publications] CHECK CONSTRAINT [FK_Publications_Countries]
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
Hey guys this is a SQL Server 2005, .net 2.0 question. I got 3 tables: Job, Times, Employee. The times table is related to Job and Employee by foreign keys. The Times table is pretty much a table where the employee's time logs are kept for a specific job. Since there was a M:M relationship between Job - Employee, I decided to create a Times table in between, and the primary key of this table should actually be the PK of Job and the PK Employee, but I couldn't figure out how to make a PK consisting of two fields. Anyhow, my main question is: How to assign an Employee to a specific job in .net? In other words, what's the best way of retrieving the Job_id and Emp_id and sticking them in the Times table? I was doing a form with a drop down for Emp_id and Job_id, but I don't know how to do the inserts... I'm confused and would appreciate any help.THANKS!
How do I move data from one server and append to the same table structure on a second server and preserve the foreign key relationships. In other words TableB.TableA_ID references TableA.ID. The IDENTITY ID numbers themselves will change but they must continue to match up on the destination server the way they did on the source server.
I can do this using procedural programming but that is usually not the way to go.
(btw, this is a cleaned up version of a question I tacked on to another thread)
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
I cannot find a good way to view existing Foreigh Key Relationships in SQL Server Management Studio Express. In the Object Explorer, if you click on a table to expand it, you can see a tree view of EVERYTHING about the table (Columns, Keys, Contraints, Triggers, Indexes, Statistics), but NOTHING about Foreign Key Relationships!!
The only way I have found is that you have to open the table in Design mode, then right click, and choose Relationships, and only then can you see what FK Relationships are defined.
You should be able to see the FK Rletaionships in the tree view of the Object Explorer window!
Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID]) REFERENCES [Category] ([CategoryID]) ON UPDATE CASCADE ON DELETE CASCADE
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?
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 have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions. Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this. with CustomGroup( id) as ( Select id from Groups g Inner join GroupStatus s on s.Id = g.Id Where s.Status = €˜Active€™ )
IF @DisplyStatus=€?UserDetail€? begin <!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here. select u.* from users u inner join userGroups ug on ug.UserId = u.Id inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE end else if @DisplyStatus=€?UserSimple€? begin <!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here. select u.FirstName,u.LastName,u.DOB from users u inner join userGroups ug on ug.UserId = u.Id inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE end else if @DisplyStatus=€?UserWithAddress€? begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
Hi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets() On Error Resume Next 'Dim pt As PivotTable Dim Sh As Worksheet Dim refresh As Boolean Dim tempMode As Boolean Dim startTime As Date
Is there a way to define a key that puts the text 'SELECT * FROM 'into the Query Analyzer window?I must type this about 50 times a day but cannot see a simple way ofdefining a key to write it for me..(tools/customize) seems to execute everything you put in there ratherthan leave it on the screen for me to add table names etc to.thanks for your time...
I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?
Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.
Hi I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>" SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId"> <SelectParameters> <asp:ControlParameter ControlID="Drop1" Name="SourceId" /> <asp:ControlParameter ControlID="Drop1" Name="CatId" /> </SelectParameters> </asp:SqlDataSource> Can anyone help me to define the parameters? thanks
Why is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
Hi, I am trying to connect to my local SQL Server through a webform ( on VS.NET ). My web.config shows something like this - sqlConnectionString="data source=IP address;Trusted_Connection=yes"and the tutorial I am following says something this - SqlConnection myConnection = new SqlConnection( "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI");If I just replace the NetSDK either with my IP address or the servername (my machine name), I get a SQLException that server doesn't exist or access denied. I have specified "windows authentication". How do I correctly define my connection string ? Please guide me.Thanks,sbs.
I noticed in SQL ODBC API reference that SQLGetTypeInfo would return true for AUTO_INCREMENT if a smallint field is defined as autoincrement. Is there a simple way to set a field autoincrement thru the SQL Server`s front end?
table abc ========= no (primary key) name ================ ==== 1 jung 2 love 4 tom
In this example, I want to define 'no' as an identify column so that I can update it as I wish.
Thanks in advance,
----------------------------------------------------------------------------- Subject: From: Date: sorry one more question (reply) Kurt () 6/23/01 2:48:32 AM
Jung, Take a look at your table. It looks like you don't have an identity column defined for "no" (your column name). Set Indentity_Insert doesn't work for tables that don't have one defined. PS A primary key is not the same as an Idendtity column.
------------ jung at 6/22/01 9:49:34 PM
table : abc
no (primary key) name ================ ==== 1 jung 2 love 4 tom
In this record, I wanted to update no 4 to 3 which is primary key. So, I used the query as below. ============================= Set Identity_insert abc on
UPDATE abc SET no = 3 WHERE no = 4
Set Identity_insert abc off go ============================= But I got an error; Server: Msg 8106, Level 16, State 1, Line 1 Table 'abc' does not have the identity property. Cannot perform SET operation.
Why this happens? I really want to update the primary key.
Fazlul Haq writes "i m new to sql server2000.my question is
" I CREATE A TABLE IN WHICH THREE COLUMS EXIST i.e SNO(NUMERIC),NAME(CHAR),FNAME(CHAR).I WANT TO ENTER VALUE IN SNO FIELD STARTING FROM ZERO(0)e.g 011,021,022 etc and when enter value without starting from zero an error messag is appered on the screen.I WANT THAT THIS CAN BE DONE BY CREATING A "FUNCTION" OR "RULE" OR "STORED PROCEDURE". PLZ HELP ME ""
I have a question about user define function in sql. How can I use Exec in UDF? What I mean is that I made a string in a UDF, And I need to execute the string in function. but I think it's illegal. so tell me how can I use it?
P.S : forget about useing stored procedure instead of function.
I am designing a new table with a few columns that may or may not havea value on each row that is inserted.What issues determine whether to allow a NULL value to be inserted forthat column or define a default value to be used?I want to think through the repercussions of this decision before I getinto production.
I have a primary key (column name is emp_id) in employee table. Also,I would like to make a combination of other two columns is unique.(combination of officecode field and claimno field must be unique).how can I implement this uniquess in ms sql 2000? thank you.