Using GUID Fields As Primary Keys W/ Foreign Key Constraints

Nov 17, 2005

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?

View 12 Replies


ADVERTISEMENT

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

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

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

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

Foreign And Primary Keys

Oct 19, 2005

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

Dropping Primary And Foreign Keys

Jun 22, 2001

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

View 2 Replies View Related

Scripting Primary & Foreign Keys Only

Oct 28, 2004

Be 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

View 5 Replies View Related

List Primary And Foreign Keys

Jan 10, 2006

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

 

View 1 Replies View Related

Derived Fields/foreign Keys

Feb 16, 2006

firstly, how do u write a derived field in sql table, e.g you are following a logical design and you are calculating a score for a match table.

secondly, if a foreign key is being used as a primary key in another table, do u have to set it to NOT NULL ?

View 1 Replies View Related

Primary/Foreign/Identity Keys &&amp; Encryption

Nov 2, 2006

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

View 1 Replies View Related

Transact SQL :: Identify Primary And Foreign Keys In Two Table

Apr 23, 2015

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].....

View 19 Replies View Related

Reporting Services :: Copy Table From One To Another Server With Primary And Foreign Key Constraints

Jun 9, 2015

How to copy a table from one server to another server with primary and foreign key constraints.

View 5 Replies View Related

Transact SQL :: Set Fields To Auto Increment Primary Keys In Bulk

Aug 4, 2015

I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?

View 11 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

Jan 28, 2008

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

View 6 Replies View Related

Auto Incremented Integer Primary Keys Vs Varchar Primary Keys

Aug 13, 2007

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

View 7 Replies View Related

UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 9, 2007

I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View 3 Replies View Related

Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 17, 2008

Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View 7 Replies View Related

Foreign Keys - On Which Kind Of Keys Do The Base On?

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

Generate Script For Primary Keys And Foreing Keys

May 16, 2008



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?

View 2 Replies View Related

Urgent !!!!! Nee Explanation On Primary Keys And FK Keys

Jul 15, 2002

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 ?

View 1 Replies View Related

How The Heck Do I Use A GUID As A Foreign Key In MSSQL 2005?

Jun 23, 2007

Okay, this is driving me nuts.  It *should* be simple but it is not.  I've spent the last 4 hours scouring the web for what should be a simple thing to find out.
I have the following tables, but everytime I attempt to create a foreign key constraint for the StudentLocations table I get an 'invalid type' error.  I have a GUID as the PK for the Students and the Locations table.  However, since I cannot create two GUIDs (as far as I can tell) for my StudentLocations table, I have instead defined the StudentLocations.StudentID, and StudentLocations.LocationID as binary(16).  Unfortunately, I can't match this binary(16) to the GUID Identity in a foreign key because I keep getting this stupid error of 'invalid conversion type!'. 
 So, how do I do what I am trying to do using GUIDs as my PK for the Students and Locations tables, and the above defined StudentID, LocationID in a StudentLocations table?
Thanks in advance for answering this question.  I should spend 5 minutes searching and then come here in the future...it's way less frustrating!:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Students]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Students](
[id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Students_id] DEFAULT (newid()),
[first_name] [nvarchar](50) NOT NULL,
[middle_name] [nvarchar](50) NULL,
[last_name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Locations]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Locations](
[id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Locations_id] DEFAULT (newid()),
[address_1] [nvarchar](50) NOT NULL,
[address_2] [nvarchar](50) NULL,
[city] [nvarchar](50) NOT NULL,
[state] [nvarchar](50) NOT NULL,
[zip_base] [char](5) NOT NULL,
[zip_four] [nvarchar](4) NULL,
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StudentLocations]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StudentLocations](
[id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_StudentLocations_id] DEFAULT (newid()),
[student_id] [binary](16) NOT NULL,
[location_id] [binary](16) NOT NULL,
CONSTRAINT [PK_StudentLocations] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

View 2 Replies View Related

Constraints And Keys

Feb 5, 2008

Hello:

I have the followig table that has constraints:


CREATE TABLE [dbo].[MD_HIERARCHY](

[MedDRA_Version_No_C] [varchar](10) NULL,

[PT_ID_N] [bigint] NULL,

[HLT_ID_N] [bigint] NULL,

[HLGT_ID_N] [char](18) NULL,

[SOC_ID_N] [bigint] NULL,

[PT_C] [varchar](100) NOT NULL,

[HLT_C] [varchar](100) NOT NULL,

[HLGT_C] [varchar](10) NOT NULL,

[SOC_C] [varchar](100) NOT NULL,

[SOC_Abbr_C] [varchar](5) NOT NULL,

[Null_Field_C] [char](1) NULL,

[PT_SOC_ID_N] [bigint] NULL,

[Primary_SOC_Flag_C] [char](1) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_14] FOREIGN KEY([MedDRA_Version_No_C], [PT_ID_N])

REFERENCES [dbo].[PT] ([MedDRA_Version_No_C], [PT_ID_N])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_14]

GO

ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_15] FOREIGN KEY([MedDRA_Version_No_C], [HLT_ID_N])

REFERENCES [dbo].[HLT] ([MedDRA_Version_No_C], [HLT_ID_N])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_15]

GO

ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_16] FOREIGN KEY([MedDRA_Version_No_C], [HLGT_ID_N])

REFERENCES [dbo].[HLGT] ([MedDRA_Version_No_C], [HLGT_ID_N])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_16]

GO

ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_17] FOREIGN KEY([MedDRA_Version_No_C], [SOC_ID_N])

REFERENCES [dbo].[SOC] ([MedDRA_Version_No_C], [SOC_ID_N])

GO

ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_17]

GO

ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_18] FOREIGN KEY([MedDRA_Version_No_C], [PT_SOC_ID_N])

REFERENCES [dbo].[SOC] ([MedDRA_Version_No_C], [SOC_ID_N])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_18]


But when I click on the constarinst folder, it's empty. However, these constrainst show up under Keys.

Can somebody tell me why? Does this tell me that keys and constraints are one and the same?

venki

View 3 Replies View Related

GUID As Primary Key

Mar 13, 2008

I have a table profiles and a table resumes.
The id's are currently int indentity fields.
I was thinking of using a GUID as ID.

Why would i do this ?

In my webapplication I have i.e. this page
DeleteProfile.aspx?profileId=1

I don't want to give the user the ability to change this to f.e.
DeleteProfile.aspx?profileId=3

A user will almost possibly never guess a certain GUID, right ?

Is this a good idea, or not ?

View 6 Replies View Related

Foreign Key Constraints?

Jul 23, 2005

I have two tables, T1 and T2.T1 has a foreign Key that refers to a key in T2.The way that I see it, its only possible to insert something into T1 ifthere already exists a tuple in T2 with the same value in key.That means that I always have to insert something into T2 before insertinginto T1Is that correct?

View 1 Replies View Related

XOR And Foreign Key Constraints

Dec 8, 2005

I have a situation where attending a meeting could be either staff orcoalition members. But don't know how to enforce a foreign keyconstraint. any ideas ?Table MeetingMeetingID int NOT NULL,AttendeeID int NOT NULLPrimary Key (MeetingID, AttendeeID)Table StaffStaffID int IDENTITY not null PRIMARY KEYTable CoalitionMemberMemberID int Identity not null PRIMARY KEYSince AttendeeID can either a value from Staff.StaffID or fromCoalitionMember.MemberID, I cannot place both constraints asADD CONSTRAINT [FK_Meeting_Staff] FOREIGN KEY(AttendeeID)REFERENCES [Staff] ([StaffID])ADD CONSTRAINT [FK_Meeting_CoalitionMember] FOREIGN KEY(AttendeeID)REFERENCES [CoalitionMember] ([MemberID])

View 1 Replies View Related

Foreign Key Constraints

Oct 17, 2006

When following a script to create some tables and constraints, it is recommended that you name the FK constraint? Otherwise it appears with numbers after it. Is this the way 2005 creates them or has this always been the naming convention?

kat

View 5 Replies View Related

Using GUID / Uniqueidentifier As Primary Key

Jul 6, 2006

<BACKGROUND INFO> We are in the database design stage of creating a db and we plan to use a GUID as the primary key for our tables (the app will be a smart client and will need to support offline functionality so when the app comes back online and the queued up inserts come thru we want to ensure that the primary keys are unique). We plan to have the app generate the guid and to send the GUID over as a parameter i.e.

<code>GUID myPrimaryKey = GUID.NewID(); </code>

and will not be using the DEFAULT NEWID clause on the CREATE TABLE statements.
</BACKGROUND INFO>

<QUESTION > Is it better to make the data type for the primary key columns to be uniqueidentifier or should the datatype be varchar(36).</QUESTION>

I only ask this because the tables have already been created with the primary key columns having a datatype of varchar(36) and changing them would be a little bit of work. Not a big deal but don't want to do it if we don't have to. If its really advantageous we will go ahead and do it but if not we would like to leave it as is. Thanks for your thoughts!

Bo

View 3 Replies View Related

GUID Vs. Autoincrement Primary Key

May 30, 2007

Hy! I am a beginner in SQLServer and I have to design a database with a lot of tables (30-50 tables). I have to choose a primary key for every table between an autoincrement and a GUID. If I choose autoincrement after an insert statement (from the client) I will have to go back to the server to get the value of the primary key which is a waste of time. On the other hand if I choose a GUID I can create the guid from the client and send him with the other values of the new row to the server and I have not to go back to the server. I am aware of the disk space of a guid field so which is the best for me: GUID or AUTOINCREMENT? Thank you!

View 1 Replies View Related

Disabling Foreign Key Constraints

Dec 22, 2000

I want to disable foreign key constraints en mass. Is there a way to do this?

I know that I can go into each table and navigate to the Relationship tab of Properties and uncheck the "Enforce relationship for INSERTs and UPDATEs" box, but I'd much prefer to automate this process with a query since there are 160+ tables and probably 200+ relationships to disable.

I figure that sysconstraints my be the ticket, but I will keep experimenting until I get the right solution. In the meantime, any insight to steer me in the right direction is appreciated.

View 1 Replies View Related

Foreign Key Constraints And Indexes

Oct 12, 2006

Hi All,

I know that when you specify a PRIMARY KEY or UNIQUE constraint for a table, SQL Server creates a unique index for the primary key columns.

What about foreign keys? Does SQL Server creates an index when you create a foreign key?
I have looked in Books Online and on the Internet, but couldn't find any information.

Thanks in advance.

View 2 Replies View Related

Question On FOREIGN KEY CONSTRAINTS

Feb 4, 2005

I've recently implemented a whole bunch of foreign key constraints in a database. As a result I have issues every time I want to truncate a table to perform data loads.

Is there an easy way to tell the system to ignore these constraints for the term of a data load or truncation of data?

thank you

View 1 Replies View Related

Foreign Key Constraints And Indexes

Oct 12, 2006

Hi All,

I know that when you specify a PRIMARY KEY or UNIQUE constraint for a table, SQL Server creates a unique index for the primary key columns.

What about foreign keys? Does SQL Server creates an index when you create a foreign key?
I have looked in Books Online and on the Internet, but couldn't find any information.

Thanks in advance.

View 5 Replies View Related

Enforce Foreign Key Constraints

Oct 22, 2007



I have recently restored an SQL Server 2000 database on SQL Server 2005
A number of constraints on the database had previously been disabled

A monthly process that is carried out, imports data into 'Table A'.
This process now crashed, being unable to truncate table as it referenced by 'Table B'

Although when i check the properties of the constraint the 'enforce foreign key constraints' is set to no.

Is there a known issue with restoring databases on 2005,

Any help greatly appreciated.

Micheal

View 2 Replies View Related

UPDATE Problem With Foreign Key Constraints

Feb 9, 2004

I have a table that I want to run an UPDATE statement to change some data in the table, but I get this error:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK__Yield__Financial_Product__ProductCode'. The conflict occurred in database 'lsmdb', table 'Financial_Product', column 'ProductCode'.
The statement has been terminated.


How do I update the ProductCode column in both tables to reflect my updated data?

Thanks in advance.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved