Could somebody please explain to me how to create a foreign key from a table that
has two composite keys? I have a table, UserPrecedence, with two composite keys -
up_owner, owner_userID. I have a second table, Users, that has the primary key
"emailAddress" and a userID table to which all other tables point their FK columns.
I want the userID column in Users to be the FK of the owner_userID column in UserPrecedence
but the Modify Table view complains that "both sides of the relationship must have the
same number of columns" when I try to create the relationship.
I am guessing this is because its a composite primary key. Can somebody explain
to me how this is done correctly (and why)?
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance? 2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
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?
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 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 two tables (categories & listings) which create a many-to-many relationship.
I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).
How does the interim table get populated with the id's?
When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.
I have been reading a bit about db design practices for SQL server and have learned how bad composite keys are!
However, I just wanted to know what the accepted alternatives are (and why they should be used);
Previously I was under the impression that adding autonumber style keys to tables are bad (extra col, doesn€™t allow unique checking of tuples). I want a table which is made up of two (single) primary keys from other tables, but the entries in the new table to be unique (and efficient). (note this is going to be part of a db which is going to grow to become very large).
Hi, i want to make a reference from a table on itself. The table has a composite Primary Key. But I just want to refernce the TEstCaseID. So whats wrong? Can anyone help me?
I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -
LoginName varchar(100) - user name Action varchar(5) - this will store 'INSERT','UPDATE','DELETE' TableName varchar(30) - name of the table updated PrimaryKey int - primary key of the record updated ColumnName varchar(30) - name of the column updated OldValue varchar(1000) - old value converted to varchar NewValue varchar(1000) - new value converted to varchar RecUpdDate datetime - record update date.
This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.
Hi, In my table1, I have a composite primary key ssn+firstname+lastname. In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key? table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?
I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?
create table z ( eno int, deptno char(2), ename varchar(5), constraint pk_eno_deptno primary Key(eno,deptno) ) and I tried to establish a foreign key based on the above defined table. It's giving error,
create table z_dup ( eno int FOREIGN KEY REFERENCES z(eno), deptno char(2) ) go
Error:- ***** There are no primary or candidate keys in the referenced table 'z' that match the referencing column list in the foreign key 'FK__z_dup__eno__18CE1BA6'.Could not create constraint. See previous errors.
Even I tried to create a foreign key like the following,
create table z_dup ( eno int FOREIGN KEY REFERENCES z(eno,deptno), deptno char(2) ) go
Error:- ******** More than one key specified in column level FOREIGN KEY constraint, table 'z_dup'.
Can anyone please help me to create a foreign key relation with the base table.
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 ?
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 :)
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.
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.
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.
I have a database with a set of items that have different ways of describing their locations. One type of item uses states, the other uses some arbitrary region that could cross state boundries. Every item in each state/region is numbered starting at 1, so that each item within a state/region has a unique area number.
So I have a table of states (that doesn't need maintaining) and a table of regions (that does. New regions could be added, existing regions could be modified.)
CREATE TABLE states ( stateId int primary key, stateCode char(2), stateName varchar(16), UNIQUE(stateCode) )
CREATE TABLE regions ( regionId int primary key, regionCode char(6), regionName varchar(32), UNIQUE(regionCode) )
My table of items points to these other tables. I want to use foreign keys for referential integrity. So my table looks something like this:
CREATE TABLE items ( itemId int primary key, itemName varchar(32), itemType ENUM('state', 'region'), areaNum int, stateId int NULL, regionId int NULL, UNIQUE(itemName), UNIQUE(areaNum, stateId, regionId), FOREIGN KEY stateId REFERENCES states (stateId), FOREIGN KEY regionId REFERENCE regions (regionId) )
My problem seems to be that when I insert records into the items table, MS SQL doesn't like a foreign key entry to be null (even though I say it can be null.) I think the problem is that there is no value in the states or regions table with the stateId/regionId = 0. I don't want to entry a dummy entry for a non-state or non-region, but I can't figure out how to do this otherwise.
Is there a way to have MS SQL permit a foreign key value to be NULL? Or is there a better way to organize this data? I would appreciate any guidance. Thanks.
I've posted this in the general database forum as well but think it is more sql server specific. I have three tables as below. My problem is that when I insert anything into has_quote, cat_ref and cust_id are both null. Any ideas on how to get round this? Thanks
