I am having trouble dropping constraints(Primary and Foreign Keys). I would like to do so so I can truncate the tables and repopulate them. Any time I use the DROP CONSTRAINT #### on one table, I get an error message saying this is referenced in another table. Any help in how to drop the keys so I can truncate the tables in a database would be appreciated. I must be overlooking something simple. Thanks for the help.
Regards,
Mark
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?
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 RelatedBe warned, index padding is not included in this, and I'm not sure the fillfactor setting is correct.
set nocount on
create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name))
create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key clustered(constraint_schema, constraint_name, ordinal_position))
create table #FK(constraint_schema sysname not null, constraint_name sysname not null,
unique_constraint_schema sysname not null, unique_constraint_name sysname not null,
sql varchar(4000) not null, constraint PK_#FK primary key clustered(constraint_schema, constraint_name))
insert into #PK
select constraint_schema, constraint_name, 'ALTER TABLE ' + quotename(table_schema) + '.' + quotename(TABLE_NAME) +
' ADD CONSTRAINT ' + quotename(CONSTRAINT_NAME) +
' PRIMARY KEY ' + CASE WHEN si.indid<>1 THEN 'NON' ELSE '' END +
'CLUSTERED (>cols<) WITH FILLFACTOR=' + cast(si.OrigFillFactor as varchar) + ' ON ' + quotename(fg.groupname)
AS SQL
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN sysindexes si on TC.CONSTRAINT_NAME=si.name
inner join sysfilegroups fg on si.groupid=fg.groupid
WHERE CONSTRAINT_TYPE IN('PRIMARY KEY','UNIQUE')
insert into #fk
select c.constraint_schema, c.constraint_name, c.unique_constraint_schema, c.unique_constraint_name,
'ALTER TABLE ' + quotename(F.table_schema) + '.' + quotename(F.table_name) +
' ADD CONSTRAINT ' + quotename(F.constraint_name) +
' FOREIGN KEY(>cols<) REFERENCES ' + quotename(r.table_schema) + '.' + quotename(r.table_name) +
'(>rcols<)'
AS sql
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS F
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON F.constraint_schema=C.constraint_schema AND f.constraint_name=c.constraint_name AND F.constraint_type='FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS R ON R.constraint_schema=C.unique_constraint_schema AND r.constraint_name=c.unique_constraint_name AND r.constraint_type in ('PRIMARY KEY','UNIQUE')
ORDER BY F.table_name, r.table_name
insert into #cols
select constraint_schema, constraint_name, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
declare @ctr int, @max int, @delim varchar(1)
select @ctr=1, @max=max(ordinal_position), @delim='' from #cols
set nocount on
while @ctr<=@max
BEGIN
update P SET SQL=Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<')
FROM #PK P INNER JOIN #cols C ON P.constraint_schema=C.constraint_schema AND P.constraint_name=C.constraint_name
WHERE C.ORDINAL_POSITION=@ctr
UPDATE F SET SQL=Replace(Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<'), '>rcols<', @delim + quotename(r.column_name) + '>rcols<')
FROM #FK F INNER JOIN #cols C ON F.constraint_schema=C.constraint_schema AND F.constraint_name=C.constraint_name AND C.ordinal_position=@ctr
INNER JOIN #cols R ON F.unique_constraint_schema=R.constraint_schema AND F.unique_constraint_name=R.constraint_name AND C.ordinal_position=R.ordinal_position
select @ctr=@ctr+1, @delim=','
END
set nocount on
update #PK SET SQL=Replace(SQL, '>cols<', '')
update #FK SET SQL=Replace(Replace(SQL, '>cols<', ''), '>rcols<', '')
select sql from #PK
select sql from #FK
drop table #pk
drop table #fk
drop table #cols
I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.
Your help would make my life a lot easier
thanks
Hi all!
I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:
== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)
== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)
== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)
The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?
Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?
Any ideas are appreciated.
Thank you,
Ben
Ok, so I've broken down and decided to write myself an invoicing
program. I'd like to use GUID fields as the Primary Keys in my
table. Now, I basicly bill for two seperate items:
Deliverables and Services.
So, my Layout's gonna look something like
Create Table Invoice(
ID UniqueIdentifier Primary Key,
-- Other Data
);
Create Deliverable(
ID uniqueidentifier Primary Key,
ParentInvoice uniqueidentifier,
-- Other data);
--...
Im sure there are probems with that as it's written feel free to edify me as to what they are.
my questions are such:
1) Does a uniqueidentifier field automagically get a value?
2) If not how do I generate one in TSQL?
3) If so, what do I use to store my Foreign Keys.
4) How do I declare my Foreign key constraints?
I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?
CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,
[code].....
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
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
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
This forum looked like the only one where I may get an answer to this question.
I built a database with a set of tables in Sql Server 2005 sp2.
I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.
Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.
How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!
In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.
Is there a system SPROC that I can execute to show and delete this item?
Simple heres the syntax Alter Table XX Drop PRIMARY KEY.
This is not working whats up?
Thanks,
Phil
I'm changing the collation sequence of a field which is a primary, clustered key field via:
ALTER TABLE [dbo].[clusterAlgorithm] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ClusterAlgorithmClassName]
) ON [PRIMARY]
GO
Is there a way to drop the primary key designation before doing an alter table/alter column statement and then recreating the key, or must I drop and recreate the table?
Thanks,
Al
I am using RDA to download a tables
I use TrackingOffWithIndexes
the issue is that when I download a small set of data the primary key index is there
when I download a larger set. the index gets dropped (no error) just slowness when querying the table
the max database size is set to 1024mb and the temp database size is set to 1024mb
the actual db size (with the missing index) is 262 mb... lot's of room left!
when I try to create the index manually after the download in sql server management studio I get this error:
Not enough storage is available to complete this operation
I am using 2GB sd cards and nothing else is on the card.
any ideas?
Regards,
eric [at] westgen com
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
The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.
I need to write SQL to drop the current primary key and add a new one Say "RecordId"
as the new primary key and which should be a autonumber too.
any idea.
thanks in advance
Hi all,
I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.
So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.
(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.
Thanks.
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 ?