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.
| Author |
Topic |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
 Posted - 2014-10-12 : 01:31:46
|
| Hi,I would need to rewrite SQL code to determine that id is unique in the Customer table.My two tables are:CREATE TABLE CUSTOMER( [CUSTNO] varchar(5) NOT NULL, [ID] CHAR(9) NOT NULL, [NAME] VARCHAR(128) NOT NULL, [ADDRESS] VARCHAR(128) NOT NULL, [DATEOFBIRTH] DATE NOT NULL, CONSTRAINT CUSTNOPK PRIMARY KEY([CUSTNO]), CONSTRAINT CUSTNOVALUES CHECK(CUSTNO LIKE '[A-Z][0-9][0-9][0-9][0-9]'), CONSTRAINT DATEOFBIRTHVALUES CHECK ( getdate() - [DATEOFBIRTH]=21), );&CREATE TABLE RENTAL([RENTALNO] INT IDENTITY (1000,1)NOT NULL,[VEHNO] VARCHAR(8) NOT NULL,[CUSTNO]varchar(5) NOT NULL, [DATERENTED] DATE NOT NULL,[DATERETURNED] DATE NULL,[CONDITION] CHAR(20) NULL DEFAULT 'EXCELLENT',CHECK ((DATERETURNED >= DATERENTED) AND(DATERETURNED != DATERENTED)),CHECK([CONDITION] LIKE 'EXCELLENT' OR[CONDITION] LIKE 'ABOVE AVERAGE' OR [CONDITION] LIKE'AVERAGE' OR[CONDITION] LIKE'NEEDS REPAIR'), CONSTRAINT RENTALNO_PK PRIMARY KEY(RENTALNO),CONSTRAINT VEHNOFK FOREIGN KEY(VEHNO)REFERENCES Car(VEHNO)ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT CUSTNOFK FOREIGN KEY(CUSTNO)REFERENCES CUSTOMER(CUSTNO)ON DELETE NO ACTION ON UPDATE CASCADE ) ;Kindly help. |
|
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2014-10-12 : 03:23:45
|
| Hi,Kindly could anyone check if this is the correct wayIS this the correct way:SELECT DISTINCT [ID],[CUSTNO],[NAME],[ADDRESS],[DATEOFBIRTH]FROM CUSTOMER; |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-12 : 11:05:52
|
| that will return unique rows of those columns. Is that what you want? NOte that, if for some reason one ID is associated with two CUSTNOS, you'll get two rows with the same ID column |
|
|
|
|
|
|
|
|
Unique