When I use Management Studio (SQL Server 2005 SP 1) to set up transactional replication with updateable subscriptions, the snapshot initialization doesn't push the foreign key constraints. If I add a foreing key at the publisher, the change doesn't make it to the subscriber, even though the Publication Properties' Subscription Options panel shows Replicate Schema Changes set True - what am I missing?
Thanks, Liston
Hi All
I am a newbie to replication. I just want to know Is there any way to publish tables (articles) without primary keys in transactional replication.
Early Thanks,
Salman Shehbaz.
I am currently new to Merge Replication and have not been able to find one really simple underlying answer to the fundamentals of Merge Replciation....
Foreign Keys.... An example - I have two DBs in separate locations both adding new records. Assume that adding these new records create foreign key links to other tables. Of course when you come to synchronise - a foreign key in a table perhaps now relates to a different record.. How do you deal with this?
Eg: two DBs, the DBs both started out the same with 10 records in the CARS table for example. The two dbs also had 20 records in the MODEL table which states the model name and a foreign key relationship to the cars table. As the two DBs get updated separately and the result is now one DB has 2 new records in the car table for example and the other has 3 new records in its cars table... Of course these have corresponding entries in their MODEL table referencing primary keys. So how do I, using merge replication keep all these new records and update their foreign key references to suit as they synchronise?
It seems fundamental to merge replication and I cannot find advice anywhere on how to do this. Do I have to change both tables to use GUIDs as primary keys rather that incrementing numbers perhaps??
advice would be greatly appreciated
i'm struggling with merge replication and foreign key constraints.
i have four tables:
- owners(id, name, firstname, address, phone)
- cars(id, licenceplate, ownerid) (thus with ownerid FK to the owners table)
- busses(id, licenceplate, ownerid) (thus with ownerid FK to the owners table)
- ships(id, licenceplate, ownerid) (thus with ownerid FK to the owners table)
now, i want to merge only the OWNERS table (article) between my 5 client pc's and 1 server.
I don't manage to reach this.
Using the merge replication upon all 4 tables works, but replicating only the owners table seems to be impossible.
Is it really impossible to replicate only the owners table, or do I forget something?
these are my create table scrips:
create table Owners (
id int identity,
name varchar(20),
firstname varchar(20),
address varchar(20),
phone varchar(20),
CONSTRAINT PK_owners
PRIMARY KEY(id)
);
create table cars (
id int identity,
licenceplate varchar(20),
ownerid int,
CONSTRAINT PK_cars
PRIMARY KEY(id),
CONSTRAINT FK_cars_ownerid
FOREIGN KEY(ownerid) REFERENCES owners(id) ON DELETE CASCADE
);
create table busses (
id int identity,
licenceplate varchar(20),
ownerid int,
CONSTRAINT PK_busses
PRIMARY KEY(id),
CONSTRAINT FK_busses_ownerid
FOREIGN KEY(ownerid) REFERENCES owners(id) ON DELETE CASCADE
);
create table ships (
id int identity,
licenceplate varchar(20),
ownerid int,
CONSTRAINT PK_ships
PRIMARY KEY(id),
CONSTRAINT FK_ships_ownerid
FOREIGN KEY(ownerid) REFERENCES owners(id) ON DELETE CASCADE
);
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.
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?
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 RelatedHello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
Can any body tell me how to know to what columns of other table it refers to.
shiva kumar
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
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
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 RelatedHow would I drop a foreign Key?
Thanks
Lystra
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
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
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"
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...
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 Relatedhaving 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
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 :)
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
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
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
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
Code:
CREATE TABLE Item (
cat_ref INT IDENTITY(1,1) PRIMARY KEY,
descrip VARCHAR(50),
date_added SMALLDATETIME,
cat_type VARCHAR(20),
contract VARCHAR(10),
cost_price SMALLMONEY,
supplier_name VARCHAR(20),
supplier_phone VARCHAR(20))
CREATE TABLE has_quote (
quote_id INT IDENTITY (1,1) PRIMARY KEY,
installation_charge SMALLMONEY,
date_of_quote SMALLDATETIME,
commercial_markup SMALLMONEY,
service_desk_contact VARCHAR(20),
category INTEGER,
date_last_pricecheck SMALLDATETIME,
cat_ref INT FOREIGN KEY
REFERENCES Item(cat_ref),
cust_id INT FOREIGN KEY
REFERENCES Customer(cust_id))
CREATE TABLE Customer (
first_name VARCHAR(10),
surname VARCHAR(10),
customer_phone VARCHAR(20),
contract VARCHAR(10),
location VARCHAR(20),
email VARCHAR(50),
cust_id INT IDENTITY (1,1) PRIMARY KEY)
What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?
The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.
My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?
Let me know! Thanks!!
I am constructing a db in sql server 2000 that will score cross-country running meets. I have an individual results table that needs to only contain participants that are entered as participants but are specific to a certain race as well. Can I have this table be linked back to TWO other tables via the PK-FK relationship and what issues might I have doing that?
Thanks!
I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.
What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?
Thanks!
Hi all
What is the general consensus regarding indexing of foreign keys in tables?
I have a lot of queries that perform inner joins from the 'primary key' table to a 'foreign key' table. I was wondering if there are any special considerations to take into account.
For example, I think in some RDBMS, foreign keys are sort of automatically assigned indexes? Is there anything that SQL Server automagically does in the background when a foreign key constraint is created that may affect whether something should be indexed?
Thanks
Matt
I am brand new to SQL Server 2000 and have encountered an issue. I created a DB with 5 or so tables all of which contain different client information. All tables are related via the Client_ID primary key I set in my main table, and foreign key in all the subsequent tables. Im using a Microsoft Access Project form as the front end.
Im having trouble figuring out how to populate the subsequent tables with the Client_ID after a enter the data in the Main table where the primary key resides.
Do I create a trigger or a stored procedure...am i even in the ballpark?
thanks in advance.
I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.
View 1 Replies View Related