SSMS Setting Relationships (Foreign Keys)
Jul 6, 2007
Hi,
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'.
View 1 Replies
ADVERTISEMENT
Apr 11, 2006
Hello again,
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,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
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,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
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?
Thank you for your assistance.
View 1 Replies
View Related
Apr 12, 2008
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
View 2 Replies
View Related
Aug 4, 2015
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?
View 7 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
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?
View 4 Replies
View Related
Nov 22, 2007
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
View 6 Replies
View Related
Jun 2, 2006
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!)
Pettrer
View 2 Replies
View Related
May 10, 2007
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?
View 1 Replies
View Related
Feb 5, 2007
Hi,
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?
Thanks for the help.
View 1 Replies
View Related
Mar 3, 2008
Hi,
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
CREATE TABLE [dbo].[Advertisment_Codes](
[Advertising_Code] [nvarchar](20) NOT NULL,
[Country_id] [int] NOT NULL CONSTRAINT [DF_Advertisment_Codes_Country_id] DEFAULT ((0)),
[Publication_code] [nvarchar](20) NULL,
[Advert_type] [nchar](1) NULL,
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Upd] DEFAULT ((0)),
[Default_Code] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Default_Code] DEFAULT ((0)),
[Active] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Active] DEFAULT ((0)),
CONSTRAINT [PK_Advertisment_Codes_1] PRIMARY KEY CLUSTERED
(
[Advertising_Code] ASC,
[Country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
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]
CREATE TABLE [dbo].[Publications](
[Country_id] [int] NOT NULL,
[Publication_code] [nvarchar](20) NOT NULL,
[Publication_name] [nvarchar](50) NOT NULL,
[Media_type] [int] NOT NULL CONSTRAINT [DF_Puclications_Media_type] DEFAULT ((0)),
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Publications_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Publications_User_Id_Upd] DEFAULT ((0)),
CONSTRAINT [PK_Puclications] PRIMARY KEY CLUSTERED
(
[Country_id] ASC,
[Publication_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
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.
View 2 Replies
View Related
Aug 1, 2006
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!
View 6 Replies
View Related
Feb 21, 2005
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)
View 7 Replies
View Related
Nov 5, 2007
Guys,
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
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
IS there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
View 3 Replies
View Related
May 15, 2008
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!
View 3 Replies
View Related
Jul 18, 2006
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
View 3 Replies
View Related
Mar 12, 2014
I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.
The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.
Currently my tables can be summarized like this.
Pre-Advice Table
Barcode
Lots of other info…
Scanners Table
Barcode
Date
Time
Weight
Which device input this information
Suppliers Weight Table
Barcode
Date
Time
Weight
Here is the process(es):
1.Pre-advice table populated.
2.When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.
3.Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.
At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.
Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.
Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.
I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.
how I will need to set up my tables/relationships in order to achieve my requirements?
View 3 Replies
View Related
Apr 17, 2007
Can any body tell me how to know to what columns of other table it refers to.
shiva kumar
View 2 Replies
View Related
Aug 24, 1999
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 ??
thanks in advance
Paul
View 1 Replies
View Related
Mar 29, 2001
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)?
Thanks,
Doug Smith
View 1 Replies
View Related
Oct 25, 2001
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?
View 1 Replies
View Related
Oct 4, 2004
How would I drop a foreign Key?
Thanks
Lystra
View 1 Replies
View Related
Mar 10, 2004
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.
Example :
tbProductCategories
TypeCode | Description
1000 | Cups
2000 | Plates
tbProductAccounts
Account | Description
1001 | Mug
1002 | Glass
2001 | Plate
2002 | Saucer
With the above tables (which are made up :) ), would you include
a foreign key in tbProductAccounts indicating the type code?
What would the stored proc look like without it?
Create Procedure usp_GetProductAccounts
@iTypeCode int
AS
SELECT tbProductAccounts.Account,
tbProductAccounts.Description
FROM tbProductAccounts
WHERE tbProductAccounts.Account - iTypeCode > 0 AND < 999
Would this work? Or should the foriegn key always be included?
Mike B
View 4 Replies
View Related
Jun 8, 2008
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?
Tks
View 4 Replies
View Related
Dec 15, 2006
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?
Thanks in advance!!!!
Shiry"
View 1 Replies
View Related
Oct 11, 2006
I have created two tables:
UserInfo:
UserID (PK)
Hairid
eyeColorid
faceShapeid
and
HairInfo:
HairInfoID (PK)
Hairid
HairDesc
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.
Thanks...
View 1 Replies
View Related
Feb 28, 2008
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?
View 4 Replies
View Related
Mar 24, 2008
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)
help would be much appreciated
View 3 Replies
View Related
Jun 21, 2007
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?
View 6 Replies
View Related
Jan 7, 2008
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 ?
View 1 Replies
View Related
Feb 3, 2008
I'm working with a SQL 2000 DB, which has a table structure as follows, implemented with foreign key constraints: ________________________________| ||--> TABLE1 --> TABLE2 --> TABLE3 --|
(ie, it forms a 'loop' so to speak)
I am trying to create a clean copy of this database, basically by deleting all the data in most of the tables, and these 3 tables are included. Of course, when I run a DELETE on any of these tables, it complains because of the FK. How can I script a delete on the 3 tables without manually deleting row after row, until there aren't anymore relations?
Cheers for any help :)
View 11 Replies
View Related
Feb 1, 2004
Hi everyone,
I've been using VS.NET to design and implement my SQL databases. This has been simple and effective.
I've deployed the database onto the production database server and it's been performing without a hitch for some time now.
My problem is that I now need to remove a foreign key relationship on the production database as I have had a relationship between two tables but now not wanting the relationship since the data that had the relationship is now not wanting to be compulsory. I've had a look in the system.foreignkey (I think) table but I can't make head or tail of it.
Long story short, how can I find and remove the relationship from Query Analyser / Enterprise Manager ? I know how to do this in VS.NET diagram (just delete the relationship) but when you open the production database in VS.NET the diagram is no longer there.
Any help will be appreciated!!
Thanks!
Andrew
View 1 Replies
View Related
Sep 4, 2005
Hey all - been knocking my head on this on for awhile - perhaps someone could shed a little light.
I have 2 tables that I want to link:
Table 1:
UserID - Primary key
First Name
Last name
AuditionID - FK
Table 2:
AuditionID - PK
Description
Sing
instrument
So - I have a web form that collects the information for both tables and then insets the collected data. But, the AuditionID value in Table1 does not update. Now, I assume I am incorrect in thinking the AuditionID in Table1 will be automagically updated when Table2 creates it's key...?
If that is indeed the case, how do you update 2 tables and maintain a foreign key relationship?
I hope I am being clear enough - any help would be greatly appreciated.
Thanks all
View 2 Replies
View Related
Aug 25, 1998
Hi!
Does anybody know why after applying both triggers and foreign keys to a table (standard relational database procedure!!!), when I try to perform, say a simple delete, I am not allowed to do so and get this error message:
Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint `FK_contract_1__14`. The conflict occurred in database `tmp`, table `contract`, column `employee_id`
Command has been aborted.
HELP!!!!
Cheers, Marc
View 3 Replies
View Related
