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 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.
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.
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?
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?
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 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.
I'm using MS SQL 2000 with enterprise manager I have a customers table called high_customer, it has a primary key called IDI have an invoices table called high_invoices, it has no primary keys but it does have a column for customer ID.I want to set it so if a customer is deleted from the customers table, any invoices with that customerID are deleted from the invoices table.Do I need to specify a constraint? If so how do I do it in enterprise manager.Thanks
Hi, I have the following problem: I have a table called Jobs with the fields: JobNumber, Name, Customer, ... And a table called Customers with the fields: ID, Name, Address, ... Obviously Jobs is linked to Customers with the Customer<->ID fields. I want to set it up so that if a Customer is deleted, then any Jobs that had that customer listed now have the Customer field set to NULL. Can I do this with a constraint, or will I need to use a trigger? Cheers,Little'un
Hello, I am not a DBA and I need to do the following. I have 2 tables A and C and both have a common fileds say emp.Emp is pkey in C . There can be a record in C but not in A.If a record is inserted in A then it checks for the same employee info in C .If not found it do not allow to insert a record in A. How to go for it? Thanks!
Hiya peops im creating a constraint using enterprise manager for 1 of my tables n was wondering how u constructively create a constraint expression, there is a specific style,right??
i have added my tables and defined foreig keys and primary keys for my table, but when i try to put the data it gives me some error reagards to parent key..and it wont let me drop the table... can anybody tell me how to drop the foregin key..i have tried (
ALTER TABLE module DROP CONSTRAINT ATTENDANCEID_FK;) this formula and it wont work,
heres my tables and relationships:
CREATE TABLE ATTENDANCE (ATTENDANCEID char(4) NOT NULL, StudID char(4) NOT NULL, ModuleID char(4) NOT NULL, Week1 VARCHAR(10), Week2 VARCHAR(10), Week3 VARCHAR(10), Week4 VARCHAR(10), Week5 VARCHAR(10), Week6 VARCHAR(10), PRIMARY KEY (ATTENDANCEID));
ALTER TABLE ATTENDANCE ADD FOREIGN KEY (studID) REFERENCES student(studID); ALTER TABLE ATTENDANCE FOREIGN KEY (moduleID) REFERENCES module(moduleID));
insert into ATTENDANCE values ('F1','A1','E1','Yes','Yes','Yes','Yes','Yes','Yes'); insert into ATTENDANCE values ('F2','A2','E2','Yes','No','Yes','Yes','No','Yes'); insert into ATTENDANCE values ('F3','A3','E2','Yes','Yes','Yes','No','Yes','Yes'); insert into ATTENDANCE values ('F4','A4','E2','Yes','Yes','Yes','Yes','No','Yes'); insert into ATTENDANCE values ('F5','A5','E1''Yes','Yes','No','Yes','Yes','Yes'); insert into ATTENDANCE values ('F6','A6','E2','Yes','Yes','Yes','Yes','Yes','No'); insert into ATTENDANCE values ('F7','A7','E1','Yes','Yes','Yes','Yes','No','No'); insert into ATTENDANCE values ('F8','A8','E2','Yes','Yes','Yes','No','Yes','Yes'); insert into ATTENDANCE values ('F9','A9','E3','Yes','Yes','No','Yes','Yes','Yes');
CREATE TABLE MODULE (ModuleID char(4) NOT NULL, ATTENDANCEID char(4) NOT NULL, ModuleName varchar(45), ModuleCode varchar(6), PRIMARY KEY (ModuleID));
ALTER TABLE MODULE ADD FOREIGN KEY (attendanceID) REFERENCES attendance(attendanceID);
insert into MODULE values ('E1','F1','Workshop Research and Project Implementation','Im3023'); insert into MODULE values ('E2','F2','Advanced Database Development','SD3042'); insert into MODULE values ('E3','F3','Advacned Information Systems Develpment','SD3043');
I am fairly new at SQL and also new to this forum. So I don't know if this topic belongs here or not.
But anyways, I found this tutorial online. However, I am having trouble completing it. Can someone please help me?
Given the following table definitions:
CREATE TABLE USERS ( USERID CHAR(5) PRIMARY KEY, NAME VARCHAR(30), RATING CHAR(1) CHECK (RATING IN (A, B,C,D) )
CREATE TABLE ITEMS ( ITEM_NO NUMBER PRIMARY KEY, DESCRIPTION VARCHAR(50), OFFERED_BY CHAR(5) REFERENCES USERS(USERID) ON UPDATE CASCADE ON DELETE CASCADE, START_DATE DATETIME, END_DATE DATETIME, RESERVE_PRICE NUMBER )
CREATE TABLE BIDS ( USERID CHAR(5) REFERENCES USERS ON UPDATE CASCADE ON DELETE SET NULL, ITEMNO NUMBER REFERENCES ITEMS(ITEM_NO) ON UPDATE CASCADE ON DELETE CASCADE, BID NUMBER, BID_DATE DATETIME, PRIMARY KEY(USERID,ITEMNO,BID_DATE) )
Directions: Indicate whether the statement is accepted or rejected; and if accepted indicate its effects. Evaluate each statement INDEPENDENTLY.
1) INSERT INTO ITEMS VALUES(1009, Red Bicycle,U01,1999-01-08, 1999-02-10, 30) 2) UPDATE ITEMS SET ITEMNO=1008 where ITEMNO=1001 3) DELETE FROM ITEMS WHERE ITEMNO=1005 4) INSERT INTO ITEMS VALUES(1010, Green Bicycle,U07,1999-01-08, 1999-02-10, 30) 5) UPDATE BIDS SET ITEMNO=1011 WHERE ITEMNO = 1001 6) DELETE FROM USERS WHERE USERID=U01 7) UPDATE ITEMS SET ITEMNO=1020 WHERE ITEMNO=1001 8) DELETE FROM BIDS WHERE BID > 1001
ORDER_ITEM (OrderSK, ItemNumber, Qty, ServiceSK, UnitPrice) SERVICE (ServiceSK, Description, UnitPrice)
How could I modify the database structure to enforce the data constraint that ORDER_ITEM.UnitPrice must be equal to SERVICE.UnitPrice for the corresponding service.
I have to change someones username in my database in a couple of tables. When I try to make any changes, I get an error that there is a constraint. Is there a way to make the changes without removing all the constraints then replacing them? Thanks
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
How does one know what a particular trigger or constraint is supposed to be doing on a table. I am just two days old on this position, and no form of documentation, at the same time I am expected to trouble-shoot some error messages. Thanks for your help.
When I first set up my database, I set it up with many unique constraints, but not much in the way if indexes. After reading more about the benefits of indexes, I'm thinking of deleting many of the unique constraints, and replacing them with unique indexes where appropriate.
It seems that indexes would be better than unique constraints in many cases, because you have the double benefit of enforcing uniqueness, as well as improving sorting capability. Does this make sense?
Hi folks, Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.
I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.
Just curious...opinions? Be glad to post the code here if that's appropriate.
Can anyone recommend a good article/book on the subject? I am trying to find out more about compound primary keys. How many is “too many”? Does the number of primary key columns affect your performance? If so, how does it affect it? Should some of the constraints be handled from stored procedures?
Executed as user: companyadmin user. Violation of PRIMARY KEY constraint 'PK_tblRMAProduct'. Cannot insert duplicate key in object 'tblRMAProduct'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Hi, I am developing a database for my company in SQL server 2000 and I have some few problems. Firstly.
I have a customers table and orders table in my DB:
Customers Orders --------- ------ CustID (primary key) ----------------< CustID . ^ ProductID . | Quantity . | . . | . etc. | etc. | relationship (one to many)
What I want to do is: 1) to be able to delete a Customer and automaticaly SQL server delete all the orders that this customer done from the Orders table. 2) If for some reason the CustomerID changes, SQL should be able to automaticaly update the necessary fields with the new values in the Orders table. Finally, 3) I want to be able to insert a new customer that has an order and update both the Customers table and Orders table automaticaly. e.g
CustID Name Address ProductID Quantity etc. ------ ---- ------- --------- -------- ---- 10-003 John London 33-25 2 ...
Such a kind of insert should add automaticaly the following entries in the two tables:
Customers Orders --------- ------ CustID (10-003) CustID (10-003) Name (John) ProductID (33-25) Address(London) Quantity (2) . . . . etc. etc.
A friend of mine told me that this can be done using Foreign Check constraints in SQL server. But I do not know what to do.
I am Trying to add a check constraint that if the paymenttotal is 0 the column is allowed to have null and if its greater then 0 it is not allow to have null. Here is what I have so far but i get some syntax errors, See if you can see what im doing wrong and how to get this to be valid. Thanks heres what I got so far
Code:
ALTER TABLE Invoices WITH CHECK PaymentDate SMALLDATETIME NULL, CHECK (PaymentTotal = 0) PaymentDate SMALLDATETIME NOT NULL, CHECK (PaymentTotal > 0)
I also had this before i changed it to that and I got syntax erros as well but i dunno which is closer.
