Constraint/Restriction Question
Feb 7, 2008
Hello all,
First of all I would like to show my satisfaction at this great forum.
I'm quite a beginner at SQL and would like to ask a question.
Let's say I have a table of Students and a table of Subjects with their respective details. Now I want to create another table that references both the previous tables so that I would know what subjects the Student is attending. My problem is how to put a restriction in the new table on the number of subjects that each student can have (e.g. each student can only apply for 8 subjects, not more).
I've heard about triggers and other possible solutions, but it's too advanced for me at the moment, so I'd like to possible find a solution through the new table.
Roughly, I guess the new table (let's call it StudentAttends) will have the student id from the Students table and the subject id from the Subjects table. Now how should I add the above restriction?
Thanks for your help!
View 1 Replies
ADVERTISEMENT
May 22, 2008
Hello
I have the following table, and i need to know, what kind of Constraint-restriction and how to apply using management studio for the next case
Table ITEMSProperties
===============================================================================
ItemID (PK-FK) ValueType (PK-FK) PropertyValue (NVARCHAR)
-----------------------------------------------------------------
1 1 (1=Color) Red
1 2 (2=Date) 01/01/2001
2 1 Blue
2 2 021/badStr/08badstr
Well, so ..
If I don€™t want bad Dates-Strings stored in this table, for the ValueType = 2, .. What kind of constraint-restriction or maybe a trigger, I need to use and please tell me how to aplly using management studio ?
View 3 Replies
View Related
May 5, 2004
I want to access mssql2000 server only by 'sa' & database login ids. I want to restrict windows administrator account to access the database. Please help !!!
Our enviorment :- SQL2000 with SP3 on Windows2000 advanced server with latest service pack..
View 2 Replies
View Related
Sep 26, 2007
In SQL 2005 is there a way where you can restrict certain columns on a table from being view from a user(s) or group, without creating a view?
View 2 Replies
View Related
Apr 13, 2008
Hi everyone,
My first table has a column named "customer" that is directed to "customers" table "id" column as foreign key.
I wanted to change "id" name so i wanted to delete it and create another column of another name but i cannot delete that column because it is attached as fk to another table.
How can i remove th fk constraint ?
Thanks.
View 3 Replies
View Related
Feb 16, 2006
I'm inserting rows from one table to another and trying to figure out how to not allow nulls from one field. ie - the feeder table can have nulls, but when I insert into the other table, I don't want the records that have NULLS in one field to insert. I hope that's clear.
View 3 Replies
View Related
Jul 23, 2005
Hi, Ive been told this, but I hope it is NOT true. I have an sqlserver2000 installation running on a server that has four processors.It is on a active network but is not the domain controller soessentially it is fully dedicated to servicing the needs of sqlserver, (a bit of browsing, a bit of ms Office, but almost whollydedicated to sqlserver. Now, the big question, why, when the serverproperties have been set to utilize all four processors, can any onejob never get more than 25% of cpu time? I can launch multipleinstance of QA and run the same job on each one and that will utilisemore and more cpu time, but if you launch multile QA windows fromwithin one insance of QA, you can NEVER get more than 25% CPUutilisation. Now i have to run a job (FTS is a good example,re-indexing lots of db's another, or even a huge query with multipleufd's on computed cols which I hoped would grab lots of CPU time thatthey need, but no. So do I have to live with this or can I tell eitherwindows or sql server to grab more cpu when it want to ie use my spareCPU capacity more efficiently or am i working on a misguided premiseand 25% per job is your lot?DMAC
View 4 Replies
View Related
Jul 20, 2005
I have 2 installations:1. W2k with MS SQL 2000 sp2.2. W2003 with MS SQL sp3.There are two databases in all instalations: "maindb" and "userdb". Thetable with data is in maindb.In the 1'st installation I restict user access to all data in table in thisway:- create user's login and user in databases maindb and userdb.- create view in userdb with "where" clause as dbo (dbo is owner of thisview). This clause restict access to data. (create view userdb.dbo.table asselect * from maindb.dbo.table where ...)- add user to group "Public" in maindb.- add role "data reader" to user in userdb.- effect: user can access data only by view, can not access any data inmaindb.I do the same in secound installation:- effect: user can not access data by view - message like this: "userhave not permission to select on maindb.dbo.table"Is this bug in sp3 or in sp2 ?Is there another way to horizontal restrict access data in tables?In Sybase ASE this method (restict by view) works ok. And there is newproperty of ASE 12.5.1 - administrator can define context of login - the"where" clause will be added automatically to any select.Please help me. Thank You for any advice.
View 2 Replies
View Related
Jul 10, 2007
About a month ago I restored a data base that had been originally created in MSDE into a full version of SQL Server 2000. I thought that the size restriction would automatically be removed by putting it into the full version of SQL. I was wrong and about a week or so later the data base reached the 2GB size and needed attention. I received some assistance through Microsoft and they walked my client through the process of removing the size restriction. Since then everything has been OK.
Now I am doing something similar. My client has a data base that was originally created in MSDE. We upgraded them to SQL Express and the data base has now grown to 4GB which is the max that SQL Express allows.
We will be installing SQL for Workgroups this week.
My question is this. Is there a setting I must change inside the data base or in SQL for Workgroups that will allow the data base to grow beyond 4GB? We need to let it expand to whatever they need and I cant seem to find any documentation on whether or not I have to change a setting for this data base.
Thanks for your help. Jean
View 3 Replies
View Related
Sep 28, 2015
I have two employee tables called EmpA and EmpB.Each table has the same attributes of Employee ID and Email address.I do an inner join on email address like this:
select * from EmpA
inner join EmpB on EmpA.email = EmpB.email
where EmpB like '%@mydomain.com'
I now want to modify the above where I want to output rows such that
 EmpA.employeeid <> EmpB.employeeid
View 9 Replies
View Related
May 17, 2006
If i create a database in SqlEX and then move it to a sever running Sql 2005 standard, am Is my database still limited to the 4GB size?
View 1 Replies
View Related
Nov 26, 2013
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
View 5 Replies
View Related
Apr 27, 2014
I would like to know if there is any option to Restrict DML statements in SSMS for a user where the same user should be able to perform these actions through application on particular database.
View 1 Replies
View Related
May 13, 2008
Hi, all.
I am trying to create table with following SQL script:
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.
View 1 Replies
View Related
May 13, 2008
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.
The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?
View 3 Replies
View Related
Jun 22, 2015
My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???
View 2 Replies
View Related
May 27, 2008
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies
View Related
May 29, 2001
Hi,
I am trying to figure out how to do this.
For each row, only one out of two columns(id1,id2) should be populated. So if the id1 column is already populated and the application tries to fill in something for id2 then we just simply don't want to allow that and vice versa.
I am thinking triggers is the way to go. What do you think?
thanks
Rozina
View 1 Replies
View Related
Sep 21, 2000
using alter table syntax how can i insert the field TramingChoiceCd
Extend the constraint on NetwkChannel table UQ__TetwkChannel__50FB042B to include TramingChoiceCd
View 1 Replies
View Related
Nov 30, 2001
Which is the preferred method Rule, Check Constraint or Trigger?
I want to set a column to todays date when the column is = "T"
else when "F" set it to a future date. Each time there is a insertion into
the table.
View 1 Replies
View Related
Jun 7, 2004
I have a varchar field in a table.I want to restrict the entries in that field as "yes" or "no" nothing else.No record will be allowed for this field without yes or no.My question is is it possible without using any trigger for the table?I want to do it with the help of a constraint.
View 1 Replies
View Related
Oct 13, 2004
When I see desing table option in enterprise manager of a table I don't see any constraints, but when I extract ddl I can see all 6 of them. They are all unique constraints not the check constraints. Is this normal. I am new to SQL Server and would appreciate some explanation.
Thanks
View 2 Replies
View Related
Nov 15, 2007
Hi, i want to put a contraint on a table which much check agains two values in the same column for the same member.
For example, i don't want a male to get information based on breast cancer, and i don't want a female to get information based on prostate cancer.
I have included some sample data. Just copy and paste.
Code Block
DECLARE @MemberLookupValues TABLE (OptionID INT, ValueID INT, Description VARCHAR(20))
INSERT @MemberLookupValues VALUES (3, 10, 'Male')
INSERT @MemberLookupValues VALUES (3, 11, 'Female')
INSERT @MemberLookupValues VALUES (7, 69, 'Prostate Cancer')
INSERT @MemberLookupValues VALUES (7, 70, 'Breast Cancer')
DECLARE @MemberValues TABLE (MemberID INT, OptionID INT, ValueID INT)
INSERT @MemberValues VALUES (1, 3, 10)
INSERT @MemberValues VALUES (1, 7, 69)
INSERT @MemberValues VALUES (1, 7, 70)
INSERT @MemberValues VALUES (2, 3, 11)
INSERT @MemberValues VALUES (2, 7, 69)
SELECT * FROM @MemberLookupValues
SELECT * FROM @MemberValues
I've highlighted the values that must be stopped.
So the basic check would be, IF OptionID = 3 AND ValueID = 10 then it must not allow you to insert the values OptionID = 7 AND ValueID = 70
I hope that makes sense.
Any help will be greatly appreciated, if you need any more informaiton then just ask,
Kind Regards
Carel Greaves
View 4 Replies
View Related
Mar 28, 2007
Hi I was wodering how to add an OR statment right in the Check Constraint expression.
This is what I am starting with in the database
([zip] like '[0-9][0-9][0-9][0-9][0-9]')
and what I want well not exact but this would answer my question
([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')
Thanks for any help
View 5 Replies
View Related
Oct 18, 2007
Is it possible to set an index of no duplicates on a column other than the primary key of a table? If yes, how is this done?
View 2 Replies
View Related
Jan 3, 2005
how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.
View 10 Replies
View Related
May 13, 2006
Hi,I want to add unique contraint to EXISTING column in EXISTING table.I have ms sql 2005.How to do that?
View 7 Replies
View Related
Oct 31, 2000
hi, I want to implement a constraint on a talbe for two fields
phone numbers should b (###)###-####
and ss# should be ###-##-####
How can I create such constraint. I tried, but got an error message and could not save the table with the new changes.
Thanks
Ahmed
View 2 Replies
View Related
Aug 11, 1999
student --- enrollment is 1- to - many . ssn is pk in student. ssn and courseid is pk in enrollment. later I added the foreign key constraint FK_SSN in enrollment table reference student table. it is ok.
enrollment --- lessonhistory is 1-to -many, ssn, courseid and lessonid is pk in lessonhistory . I tried to set FK_SSN foreign key constraint in lessonhistory table reference enrollment table, it always show error message " no primary key in referewnce talbe enrollment "
I don't know how to fix it. could you help me out, thanks!!
View 1 Replies
View Related
Dec 7, 2003
Can somebody tell me about on delete constraint and where should it be used - table having foriegn key?
coz want i want is- the moment i delete the data from the table whose primary key is been referenced as foreign key , The Data in all the tables where its primary key is beeen used as forein key should be deleted.
:confused:
View 2 Replies
View Related
Nov 19, 2001
Does anyone have any Idea on how I could enforce a unique constrait across multiple tables?
View 1 Replies
View Related
Mar 28, 2007
My company does medical billing for several clients. Each client may have a contract with several insurance companies (Payors) for rates for different procedures. I have a table named FeeSchedule with four (relevant) columns: Client, CPTCode (procedure code), Payor, and Fee.
Some Payors don't really negotiate, and have the same FeeSchedule for all clients. I know the right way to track that is have a separate set of records for each client, even for payors like medicare where every client will have the same set of records. However, the data-entry burden for populating that table would be very large, and I would like to use be able to have use a NULL client like a wildcard.
The problem is I then can't use client as part of the key, and I need to be able to restrict it so that if I have a NULL client for a specific CPT and Payor and I can't also have a another Client for that CPT and Payor.
Any ideas how to accomplish this or the equivalent functionality?
View 1 Replies
View Related
Oct 27, 2004
Hi everybody
we have the following tables
1)
Country
Countryid CountyNAme
INIndia
MYMalaysia
UKUnitedKingdom
here Countryid is the primary key.
2) MainDept
DeptID Deptname Countryid
CMCashManagementIN
CBConsumerBankingMY
CSCustomer SupportIN
IBInternetBankIN
here deptid is the primary key
3) UserMaster
Uid Uname Deptid Countryid
001 Chris CMIN
002 Raja CSIN
003 Ram CBMY
here Uid is the primary key.
The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible
Can anybody suggest a solution for this in both sql server and in oracle
Thanks u verymuch
View 1 Replies
View Related
