UNIQUE CONSTRAINT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UNIQUE CONSTRAINT

Author

Topic

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-07 : 10:07:03

Hi again. I have made a lot of tables.
Lets take two of them

1) create cash
(code_ep int not null,
name varchar(30),
primary key (code_ep));

2)create prot
(code_p int not null,
code_ep int not null,
name varchar(30),
primary key (code_p, code_ep), (My exercise says wants these keys)
foreign key code_ep references ....);

In the second table I want these values
(1,1,name)My exercise says so.
(2,1,name)My exercise says so.
...
(2,2,name)
(3,2,name)My exercise says so....

But of course I can't do it because I have primary Key constraint.

Some help plz.
And I have the same problem with the other tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 10:49:44

why? your pk is composite looking for only unique combination of (code_p, code_ep) so i dont think you wont have any problems in inserting
(1,1,name)
(2,1,name)
...
unless value of code_ep doesnt exist in cash table.

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-07 : 10:54:28

I dont know, i have in cash number 1 and 2 but oracle says unique constraint violated.
I have these values to put
1,1
2,1
3,1
4,1
5,1
6,1
1,2
2,2
3,2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 10:57:33

then you must have a unique constraint on code_ep which you've not posted. if its there, you cant insert. drop it if you want above values to be inserted.

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-07 : 12:29:15

quote:
Originally posted by visakh16

then you must have a unique constraint on code_ep which you've not posted. if its there, you cant insert. drop it if you want above values to be inserted.

OK I'll try. thanks. Something else now. Hope you're not sick of me

I ' m trying to create another table with three keys. I've double checked everything but the stupid oracle says ORA-02270: no matching unique or primary key for this column-list. I'm sure they match. Can write the tables to see them if u want. Any ideas ?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 12:48:36

were you trying to create foreign key constraint when you got this error?

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-07 : 12:51:56

CREATE TABLE TAM
(COD_T VARCHAR(30) NOT NULL,
NAME VARCHAR(200),
PRIMARY KEY (cod_T))
////
CREATE TABLE EP
(COD_EP INT NOT NULL,
NAME VARCHAR(200),
PRIMARY KEY (cod_EP))
////
CREATE TABLE PROT_TA
(COD_P INT NOT NULL,
COD_EP INT NOT NULL,
NAME VARCHAR(200),
FOREIGN KEY (COD_EP) REFERENCES EP(COD_EP),
PRIMARY KEY(COD_P,COD_EP))
////
AND THE MISTAKE IS IN THIS TABLE

CREATE TABLE MET
(COD_M INT NOT NULL,
COD_P INT NOT NULL,
COD_EP INT NOT NULL,
COD_T VARCHAR(30) NOT NULL,
NAME VARCHAR(30) NOT NULL,
P_S NUMBER(3),
FOREIGN KEY (COD_P) REFERENCES PROT_TA(COD_P),
FOREIGN KEY (COD_EP) REFERENCES EP(COD_EP),
FOREIGN KEY (COD_T) REFERENCES TAM(COD_T),
PRIMARY KEY (COD_M,COD_P,COD_EP))

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-07 : 13:44:19

[code]

Remove the Brown part:
CREATE TABLE TAM
(COD_T VARCHAR(30) NOT NULL,
NAME VARCHAR(200),
PRIMARY KEY (cod_T))
////
CREATE TABLE EP
(COD_EP INT NOT NULL,
NAME VARCHAR(200),
PRIMARY KEY (cod_EP))
////
CREATE TABLE PROT_TA
(COD_P INT NOT NULL,
COD_EP INT NOT NULL,
NAME VARCHAR(200),
FOREIGN KEY (COD_EP) REFERENCES EP(COD_EP),
PRIMARY KEY(COD_P,COD_EP))
////
AND THE MISTAKE IS IN THIS TABLE

CREATE TABLE MET
(COD_M INT NOT NULL,
COD_P INT NOT NULL,
COD_EP INT NOT NULL,
COD_T VARCHAR(30) NOT NULL,
NAME VARCHAR(30) NOT NULL,
P_S NUMBER(3),
~~[maroon]FOREIGN KEY (COD_P) REFERENCES PROT_TA(COD_P),[/maroon~~
FOREIGN KEY (COD_EP) REFERENCES EP(COD_EP),
FOREIGN KEY (COD_T) REFERENCES TAM(COD_T),
PRIMARY KEY (COD_M,COD_P,COD_EP))[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 13:47:32

the problem occurs because COP_P is not sole primary key and so may contain duplicate values.

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-07 : 13:47:45

quote:

Originally posted by sodeep



Remove the Brown part:
CREATE TABLE TAM
(COD_T VARCHAR(30) NOT NULL,
NAME VARCHAR(200), 
PRIMARY KEY (cod_T))
////
CREATE TABLE EP
(COD_EP INT NOT NULL,
NAME VARCHAR(200),
PRIMARY KEY (cod_EP))
////
CREATE TABLE PROT_TA
(COD_P INT NOT NULL,
COD_EP INT NOT NULL,
NAME VARCHAR(200),
FOREIGN KEY (COD_EP) REFERENCES EP(COD_EP),
PRIMARY KEY(COD_P,COD_EP))
////
AND THE MISTAKE IS IN THIS TABLE

CREATE TABLE MET
(COD_M INT NOT NULL,
COD_P INT NOT NULL,
COD_EP INT NOT NULL,
COD_T VARCHAR(30) NOT NULL,
NAME VARCHAR(30) NOT NULL,
P_S NUMBER(3),
[maroon]FOREIGN KEY (COD_P) REFERENCES PROT_TA(COD_P),[/maroon
FOREIGN KEY (COD_EP) REFERENCES EP(COD_EP),
FOREIGN KEY (COD_T) REFERENCES TAM(COD_T),
PRIMARY KEY (COD_M,COD_P,COD_EP))

Thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-07 : 13:58:49

Good

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-08 : 02:40:16

have patience. i ' ll ask a lot more

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-08 : 03:09:11

I'm not so sure you want to remove the FK reference for data integrity reasons. Consider redefining the reference as:

FOREIGN KEY (COD_P,COD_EP) REFERENCES PROT_TA(COD_P,COD_EP)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 03:10:34

quote:
Originally posted by misty

have patience. i ' ll ask a lot more

no problem

feel free to ask always when you've doubt

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-08 : 08:32:06

quote:
Originally posted by visakh16

quote:
Originally posted by misty

have patience. i ' ll ask a lot more

no problem
feel free to ask always when you've doubt

Ok then question

I have a table named R with five columns. The first one is codec and it is a primary key. The second one is named code_e (it's a foreign primary key) and has prices 1,2,3. The last one has the amounts. For each code (1,2,3) of code_e i must find the total amount and then i must put the total amounts in ascending order (with their code_e of course-eg (1,300000 etc).

these are some of my prices only for the two columns (second and last) i'm interested in.
1,20000
1,80000
1,200000
2,18000
2,32000
2,50000
3,100000
3,120000
3,250000

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-08 : 08:51:05

Select Code_e,Sum(Amount)as TotalAmount
from [R-TABLE]
Group by Code_e
Order by TotalAmount

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-08 : 08:53:53

quote:

Originally posted by sodeep

Select Code_e,Sum(Amount)as TotalAmount
from [R-TABLE]
Group by Code_e
Order by TotalAmount

Why are these so easy for you and they look like a mountain to me?

Thank you very very very much. I ' ll run it although I have vista and I have to many problems with oracle

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-08 : 08:55:53

Excuse me what is the use of "as"? (as totalamount) (as select) etc?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-08 : 09:02:45

quote:
Originally posted by misty

Excuse me what is the use of "as"? (as totalamount) (as select) etc?

It is an alias for simplicity.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-08 : 09:05:29

Learn from here for Beginner's for SQL:
http://www.w3schools.com/sql/default.asp

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-09 : 05:47:19

I have two tables.

Table "work" hasQ
code_work, description, code_m ...

Table "de" has:
cod_de,code_work,cod_d,date,amount

I must find the description of the work which is the most expensive.

I know i must do something like inner join, but i cant understand though i ' m searching on net all day .

I'm pretty sure i am completely stupid

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources